Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal Averages
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... -- Kevin Backmann |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal Averages
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." |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal Averages
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" wrote: 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... -- Kevin Backmann |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal Averages
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 Backmann "Dave O" wrote: 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." |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal Averages
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 ... -- Kevin Backmann "REST" wrote: 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" wrote: 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... -- Kevin Backmann |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal Averages
Kevin B wrote:
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested subtotal display error | Excel Discussion (Misc queries) | |||
I figured out how to do total averages in Pivot Charts! | Charts and Charting in Excel | |||
SUBTOTAL - TJ | Excel Worksheet Functions | |||
subtotal a range of cells on a different worksheet | Excel Worksheet Functions | |||
Subtotal Bug in Excel 2003 | Excel Discussion (Misc queries) |