# Thread: Get average of percentages

1. ## 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."

2. 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%.

3. 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(I15-I16)/I15)

4. If you use:
=IF(I16=0,"",SUM(I15-I16)/I15)

The average will ignore the text strings

Steve

5. OK, but then it will result in the grant total average with #VALUE! if that cell is blank.

6. Kzkz,

Or maybe something like this -

7. 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

8. Kzkz,

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.

9. 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

10. 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 similarly-sized 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%.

11. 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....

Steve

12. 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

13. ## 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,
Maud

14. 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.

15. 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.

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•