| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to ignore hidden columns in an "Average" formula?
If an "average" formula includes multiple columns, and those columns change
each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to ignore hidden columns in an "Average" formula?
Try this if you can use 101 in your Excel version
=SUBTOTAL(101,A1:C1) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DotK" wrote in message ... If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to ignore hidden columns in an "Average" formula?
Use SUBTOTAL(101,YourRange)
-- Please remember to indicate when the post is answered so others can benefit from it later. "DotK" wrote: If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to ignore hidden columns in an "Average" formula?
Ron,
I thought "=subtotal(101,range)" only was affected "rows" were hidden - not the columns? "Ron de Bruin" wrote: Try this if you can use 101 in your Excel version =SUBTOTAL(101,A1:C1) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DotK" wrote in message ... If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to ignore hidden columns in an "Average" formula?
Hi Brad
No, also columns Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brad" wrote in message ... Ron, I thought "=subtotal(101,range)" only was affected "rows" were hidden - not the columns? "Ron de Bruin" wrote: Try this if you can use 101 in your Excel version =SUBTOTAL(101,A1:C1) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DotK" wrote in message ... If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to ignore hidden columns in an "Average" formula?
In xl2003, hidden columns are not ignored.
Ron, did that change in xl2007 or xl2010? (or maybe celebrating the New Year too early???) Ron de Bruin wrote: Hi Brad No, also columns Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brad" wrote in message ... Ron, I thought "=subtotal(101,range)" only was affected "rows" were hidden - not the columns? "Ron de Bruin" wrote: Try this if you can use 101 in your Excel version =SUBTOTAL(101,A1:C1) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DotK" wrote in message ... If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? . -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to ignore hidden columns in an "Average" formula?
From the 2007 Excel help
The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal. How are you getting the 101 to not count the hidden columns? "Ron de Bruin" wrote: Hi Brad No, also columns Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brad" wrote in message ... Ron, I thought "=subtotal(101,range)" only was affected "rows" were hidden - not the columns? "Ron de Bruin" wrote: Try this if you can use 101 in your Excel version =SUBTOTAL(101,A1:C1) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DotK" wrote in message ... If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to ignore hidden columns in an "Average" formula?
Hi Dave and Brad
(or maybe celebrating the New Year too early???) Yes that it is I would swear that I test it in 2007 yesterday and it worked but I must admit that we drink beer at work before I test it <vbg Everybody a Happy New-year -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... In xl2003, hidden columns are not ignored. Ron, did that change in xl2007 or xl2010? (or maybe celebrating the New Year too early???) Ron de Bruin wrote: Hi Brad No, also columns Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brad" wrote in message ... Ron, I thought "=subtotal(101,range)" only was affected "rows" were hidden - not the columns? "Ron de Bruin" wrote: Try this if you can use 101 in your Excel version =SUBTOTAL(101,A1:C1) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DotK" wrote in message ... If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? . -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to ignore hidden columns in an "Average" formula?
Happy New Year to you, too, Ron!
Ron de Bruin wrote: Hi Dave and Brad (or maybe celebrating the New Year too early???) Yes that it is I would swear that I test it in 2007 yesterday and it worked but I must admit that we drink beer at work before I test it <vbg Everybody a Happy New-year -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... In xl2003, hidden columns are not ignored. Ron, did that change in xl2007 or xl2010? (or maybe celebrating the New Year too early???) Ron de Bruin wrote: Hi Brad No, also columns Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brad" wrote in message ... Ron, I thought "=subtotal(101,range)" only was affected "rows" were hidden - not the columns? "Ron de Bruin" wrote: Try this if you can use 101 in your Excel version =SUBTOTAL(101,A1:C1) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DotK" wrote in message ... If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? . -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comments partially hidden due to "frozen" columns | Excel Discussion (Misc queries) | |||
| Get "AVERAGE" function to ignore empty cells | Excel Discussion (Misc queries) | |||
| How do I get Excel to ignore "a", "an", "the" when sorting? | Excel Discussion (Misc queries) | |||
| How do I ignore "#N/A" data in a column while calculating average | Excel Worksheet Functions | |||
| Formula to figure an average on a range of cells with an "if" form | Excel Worksheet Functions | |||
Linear Mode
