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

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)

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

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, 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?

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 percentagesPercentAverage = 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

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.