Average not summing up to 100%

Hello All,

New to the forum and wondering if someone can explain a problem for me please.

I have 13 cells that i need to average. When i took on the worksheet, it had a formula using SUM to get the AVERAGE. I changed it to show the proper formula AVERAGE but it came out with a different result.

=SUM($E8,$G8,$I8,$K8,$M8,$O8,$Q8,$S8,$U8,$W8,$Y8,$AA8,$AC8)/13 - (result = 60.7%)

=AVERAGE($E8,$G8,$I8,$K8,$M8,$O8,$Q8,$S8,$U8,$W8,$Y8,$AA8,$AC8) - (result = 65.8%)

Am i using AVERAGE wrong? Do they function differently? Please explain the difference.

Thank you!

AVERAGE ignores text and blanks, so your divisor is wrong in the first case if those cells include any.

Following through on shg's comment, change 13 in the SUM formula to

=COUNT($E8,$G8,$I8,$K8,$M8,$O8,$Q8,$S8,$U8,$W8,$Y8,$AA8,$AC8)

Then again, why are you double-checking the AVERAGE result that way?

Is there an underlying question that is really the problem that you want help with?

Wild guess.... Perhaps it is a surprise that some of your "numbers" are not included in the AVERAGE. If so, the likely reason is that they are considered text, not numeric, for any of several reasons. The format of the cell does not matter. And visual inspection can be misleading. Use formulas of the form =ISTEXT(E8) to determine the type (not format) of a cell value.

If you restore Excel's default horizontal alignment (which is General), numbers align right and text aligns left.

It's a mystery to me why people change it.