Subtotal Averages

[Kevin B]

I'm have a problem explaining differences in avegages in a workbook created
in Excel 2003

If I use sub totals from the data menu I get the following 4 results using
the SUBTOTAL(1, xx:xx) and SUBTOTAL(101,xx:xx):

99.38
98.17
97.26
98.06


But if I use the the standard average formula, or I sum the range and divide
by a count of cells having values I get the following results:
Average formula 1: AVERAGE(xx:xx)
Average formula 2: SUM(xx:xx)/COUNT(xx:xx)

99.38
98.16
97.23
98.05

While the differences are fractional I've been asked to determine why
there's a difference. Any and all information will be greatly appreciated.

Thanks...

 

[Dave O]

Are there any hidden rows in the SUBTOTAL(1... function's range? I
found this in the Help:
"But, hiding a row in a subtotal of a vertical range does affect the
subtotal."

 

[REST]

This sounds like a rounding error to me. Try using the rounding function
ROUND(number,num_digits) at the detail level then then try to calculate both
ways.

REST

 

[Kevin B]

Thanks Dave, but changing the Subtotal option for 1 to 101 will get the blank
rows, which actually what I was hoping for.

Thanks again.

 

[Kevin B]

You're right about it sounding like it's a rounding error, but the user said
that it was straight input, however, that remains to be seen. I'll peruse
the spreadsheet in depth tomorrow and see if there's any formulas floating
about.

Thanks ...

 

[Dave O]

Thanks Dave, but changing the Subtotal option for 1 to 101 will get the blank
rows, which actually what I was hoping for.

Blank rows are different from hidden rows. Any hidden rows in your
range?