Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUBTOTAL function to average column without blanks
I can use the SUBTOTAL command with the 101 function to average only visable
cells in a filtered list. However how can I exclude empty cells from being included in the average? I can do this with an "array formula", e.g. {=AVERAGE(IF(P3:P60<0, P3:P60,""))} but not with a filtered list using visable cells only. Any help appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUBTOTAL function to average column without blanks
I take it you mean a list where rows have been manually hidden, instead
of filtered. In any case, the SUBTOTAL function ignores empty cells. Or do you want an average that excludes zeros. If so, try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER... =AVERAGE(IF(SUBTOTAL(102,OFFSET(P3:P60,ROW(P3:P60)-ROW(P3),0,1)),IF(P3:P6 0 0,P3:P60))) Hope this helps! In article , Campbell wrote: I can use the SUBTOTAL command with the 101 function to average only visable cells in a filtered list. However how can I exclude empty cells from being included in the average? I can do this with an "array formula", e.g. {=AVERAGE(IF(P3:P60<0, P3:P60,""))} but not with a filtered list using visable cells only. Any help appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUBTOTAL function to average column without blanks
Cheers Domenic,
Thank you so much - you solved my problem, although I'll have to work on it a bit to fully understand why! FYI, I didn't have manually hide rows, they were only hidden by the filter. The filter selected items from one column, and the corresponding amounts that I wished to average were in another column, which included some blank cells. Keep up the excellent work! "Domenic" wrote: I take it you mean a list where rows have been manually hidden, instead of filtered. In any case, the SUBTOTAL function ignores empty cells. Or do you want an average that excludes zeros. If so, try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER... =AVERAGE(IF(SUBTOTAL(102,OFFSET(P3:P60,ROW(P3:P60)-ROW(P3),0,1)),IF(P3:P6 0 0,P3:P60))) Hope this helps! In article , Campbell wrote: I can use the SUBTOTAL command with the 101 function to average only visable cells in a filtered list. However how can I exclude empty cells from being included in the average? I can do this with an "array formula", e.g. {=AVERAGE(IF(P3:P60<0, P3:P60,""))} but not with a filtered list using visable cells only. Any help appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Fill Function to next Column | Excel Worksheet Functions | |||
Average Function (include Blank Cells and Zeros) | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
"Average" function query | Excel Worksheet Functions |