Results 1 to 15 of 45
Thread: Get average of percentages

20120921, 13:16 #1
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 681
 Thanks
 1
 Thanked 0 Times in 0 Posts
Get average of percentages
I've got a spreadsheet with about 100 cells that each have a percentage. I'm trying to have one cell that shows the total or average of each of those cells. What it's doing is adding them all up and giving a total of like 193% when adding two of the cells that have 95% and 98%. What am I doing wrong?
P.S. I had been using pluses, but if I use a , inbetween the cell formulas, it will max out and say"more arguments have been specified than are allowed in this current file format."Last edited by kzkz; 20120921 at 14:54. Reason: Update

20120921, 14:53 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 6,807
 Thanks
 228
 Thanked 913 Times in 837 Posts
Kzkz,
If you have 95% in A1 and 98% in A2 then put =Average(A1:A2) in A3 to get the average. I would assume you are using a =SUM(A1:A2) or =A1+A2 to get the answer of 193%.

20120921, 15:09 #3
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 681
 Thanks
 1
 Thanked 0 Times in 0 Posts
The problem too is that if I have two cells that have actual data, it works, but if I tell it to complete the average of the other cells that have zero then it lowers the result.
Each of those cells that it's reading are coming from cells that have the following as they are producing a result of the average number. =IF(I16=0,0,SUM(I15I16)/I15)

20120921, 15:30 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
If you use:
=IF(I16=0,"",SUM(I15I16)/I15)
The average will ignore the text strings
Steve

20120921, 15:48 #5
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 681
 Thanks
 1
 Thanked 0 Times in 0 Posts
OK, but then it will result in the grant total average with #VALUE! if that cell is blank.

20120921, 15:49 #6
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 6,807
 Thanks
 228
 Thanked 913 Times in 837 Posts
Kzkz,
Or maybe something like this 

20120921, 16:14 #7
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 681
 Thanks
 1
 Thanked 0 Times in 0 Posts
I can't use ranges since each of the cells are not right next to each other. Would that formula still work? What would that look like? Couldn't figure that one out. Thanks

20120921, 18:10 #8
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 6,807
 Thanks
 228
 Thanked 913 Times in 837 Posts
Kzkz,
Ok, how about this approach?
Select all the cells you want to average (hold Ctrl and click on each).
In the name box give it a name {MyPcts}.
Use this formula: =SUM(MyPcts)/COUNT(MyPcts).
Note: although my example has them all in a single column this method will work with any cells you select on this or even other sheets in the same workbook.

20120922, 06:24 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I don't understand, if a cell is blank it shouldn't trigger an error when calculating the average. Could you elaborate on your setup, perhaps attach an example file...
Steve

20120922, 07:23 #10
 Join Date
 Feb 2008
 Location
 A cultural area in SW England
 Posts
 2,919
 Thanks
 19
 Thanked 114 Times in 108 Posts
Whereas it is quite possible to calculate a mean of a number of percentages, are you quite sure that what you are doing is actually a meaningful (no pun intended!) operation? That is, are these percentages based on similarlysized fractions?
To give a pathological example, suppose you were taking the mean of two percentages, 50% and 80%: answer 65%.
But what if the two fractions were 1/2 (50%) and 200/250 (80%)?
Really the mean should be (1+200)/(2+250) or 201/252 or 79.8%.BATcher
Dear Diary, today the Hundred Years War started ...

20120922, 10:41 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The difference in the 2 calculations are the whether one wants the average percentage (which was the request) or a weighted average (which is the second. But your example presumes the "fraction" are both unreduced: that is to say Does 1/2 mean 1 out of 2, 3 out of 6, or even 125/250 that has been reduced...
The average assumes the "fractions" are 50/100 and 80/100 which averages 65/100 no matter which way it is done.
A mean (unweighted) average always assumes that each value is of equal likelihood. It is only when it is not that one would do a weighted average.But both are meaningful, they just mean different things....
SteveLast edited by sdckapr; 20120922 at 10:45.

20120922, 11:14 #12
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 681
 Thanks
 1
 Thanked 0 Times in 0 Posts
Steve, attached is a cut down version of the sample file. Maybe that will help.
The totals I'm trying to get right are in columns M, N and O at the top.
I think that changing each of the cells to being separated by a comma rather than plus would work but it only allows so many items and stops. Maybe a combination of the named field with the sumifs command?
KZ

20120922, 12:08 #13
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,518
 Thanks
 60
 Thanked 310 Times in 285 Posts
VB solution
In your average formula (M1 and N1), you summed all your values (to one number) then took an average of that sum. Of course, the average of a number is the number. Here is a code in VB that will do what you want to do. I have left it simple, and therefore not as efficient as could be, but just a functional. All text in green are comments to help you understand the flow. I have applied it to the attached revised spreadsheet with an update button. The code is located in a subroutine, PercentCalc(), in Module 1
Dim num As Integer, PercentTotal, PercentAverage
Public Sub PercentCalc()
' *******************
' * Code by Maudibe *
' * 9/22/12 *
' *******************
'
' % PROFITS
num = 0 'number of values excluding zeros as the divisor (denominator)
PercentTotal = 0 'running sum of percentages
PercentAverage = 0 ' Sum of percentages divided by the number of acceptable values
For I = 6 To 11 Step 5 'cylce between the 2 columns F and K where the Labor Hours are located
For J = 9 To 438 Step 8 'cylce between the many rows where the Labor Hours are located
PercentTotal = PercentTotal + Cells(J, I).Value 'Update running sum of percentage totals
If Cells(J, I).Value <> 0 Then 'Checks if it is a legitimate entry for the divisor
num = num + 1 'Divisor is incremented
End If
Next J
Next I
PercentAverage = PercentTotal / num 'Average is calculated
Cells(1, 13).Value = PercentAverage 'Assign M1 Average Percent
'
' % PARTS
num = 0 'number of values excluding zeros as the divisor (denominator)
PercentTotal = 0 'running sum of percentages
PercentAverage = 0 ' Sum of percentages divided by the number of acceptable values
For I = 6 To 11 Step 5 'cylce between the 2 columns F and K where the COGS are located
For J = 8 To 437 Step 8 'Cyclce between the many rows where the COGS are located
PercentTotal = PercentTotal + Cells(J, I).Value 'Update running sum of percentage totals
If Cells(J, I).Value <> 0 Then 'Checks if it is a legitimate entry for the divisor
num = num + 1 "Divisor is incremented
End If
Next J
Next I
PercentAverage = PercentTotal / num 'Average is calculated
Cells(1, 14).Value = PercentAverage 'Assign N1 Average Percent
End Sub
HTH,
MaudLast edited by Maudibe; 20120922 at 16:48. Reason: added coded

20120922, 12:18 #14
 Join Date
 Jul 2012
 Posts
 108
 Thanks
 0
 Thanked 4 Times in 4 Posts
What Steve is saying is vital to understand how averages work. Most calculations that used the average function assumed that you are entering a fraction of 1.
What Excel and other applications will do when it formats your results as a percentage is it puts the % in, it is going the back end multiplication for you. If you are getting a result of 193%, there may be a whole number in your row or column of numbers somewhere. This is a common error (I do it all the time). You need to highlight the column of numbers and reformat them with number and decimal places. That way you can see if one of the values exceeds a fraction.
The other portion to understand is that, depending on what you are doing, a blank cell may be included in the calculation of averages just as a cell with a 0 becomes part of the average calculation unless you turn this off. There is a difference between a blank cell and a cell with a null value.
As a side point, there are studies by a number of individuals that criticized te ability of Microsoft Excel to calculate results correctly. Whenever I do a complicated spreadsheet, I usually check it with pocket calculator because I don't trust the stuff.

20120922, 12:39 #15
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 681
 Thanks
 1
 Thanked 0 Times in 0 Posts
I understand what you are saying. Did you take a look at my attached file? How do you turn off the calculations of a result of 0 in Excel?
The cells are calculating correctly with full numbers.