Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average without Hidden Cells
I need to average values in a row, where some columns may be hidden. I do not
want the hidden values to be used in the Average calculation. The hidden column may not always be the same. Can this be done without itemizing the cells in the Average formula? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average without Hidden Cells
If you have XL 2003 or later:
=SUBTOTAL(101,A2:E2) -- Best Regards, Luke M "Goody" wrote in message ... I need to average values in a row, where some columns may be hidden. I do not want the hidden values to be used in the Average calculation. The hidden column may not always be the same. Can this be done without itemizing the cells in the Average formula? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average without Hidden Cells
Hi,
Annoyingly the SUBTOTAL function does this on columns only and not rows so we have to improvise. say your date are in A1 - J1 put this in A2 and drag right to J2 =CELL("width",A1)0 You can now hide this row if you want Now if you have E2007 use the formula below to average visible rows only =AVERAGEIF(A2:J2,TRUE,A1:J1) If you don't have e2007 then use =SUMIF(A2:J2,TRUE,A1:J1)/COUNTIF(A2:J2,TRUE) One note is that hiding/unhiding rows/cols doesn't cause Excel to calculate so if you hide/unhide then tap F9 to get the result to update -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Goody" wrote: I need to average values in a row, where some columns may be hidden. I do not want the hidden values to be used in the Average calculation. The hidden column may not always be the same. Can this be done without itemizing the cells in the Average formula? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average without Hidden Cells
Luke,
That only works on columns. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Luke M" wrote: If you have XL 2003 or later: =SUBTOTAL(101,A2:E2) -- Best Regards, Luke M "Goody" wrote in message ... I need to average values in a row, where some columns may be hidden. I do not want the hidden values to be used in the Average calculation. The hidden column may not always be the same. Can this be done without itemizing the cells in the Average formula? . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average without Hidden Cells
Thanks for the correction. Since the basic functionality worked across
columns, and made the bad assumption that it would handle the hiding of columns. -- Best Regards, Luke M "Mike H" wrote in message ... Luke, That only works on columns. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Luke M" wrote: If you have XL 2003 or later: =SUBTOTAL(101,A2:E2) -- Best Regards, Luke M "Goody" wrote in message ... I need to average values in a row, where some columns may be hidden. I do not want the hidden values to be used in the Average calculation. The hidden column may not always be the same. Can this be done without itemizing the cells in the Average formula? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Visible Cells in Sheet with Merged and Hidden Cells | Excel Discussion (Misc queries) | |||
How to ignore hidden columns in an "Average" formula? | Excel Discussion (Misc queries) | |||
drag data vertically over hidden cells.. ignore hidden cells | Excel Discussion (Misc queries) | |||
average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
How do I paste hidden cells and keep them hidden? | Excel Discussion (Misc queries) |