Average Unhidden Cells Only?

[leewoo5]

Hi all,

I am currently working on a workbook that will serve as a low maintenance indicator for cross-functional KPI's at work.

For this I have created a simple macro to filter by month so the charts only show performances from the selected month. This works by hiding cells in the spread sheet for all other months. Along side the charts I have created a gauge that works from taking the actual result against the target result.

My question is: Is it possible, VBA or otherwise, to take the average of only unhidden cells so when filtering by month it will only populate the average for the selected month?

I am happy to share this workbook if needed.

Many thanks,

 

[T. Valko]

The SUBTOTAL functions are designed to work with filtered data.

For an average of a filtered range...

=SUBTOTAL(1,A5:A100)

That will return the average of only the visible cells within the range A5:A100.

 

[Rick Rothstein]

The SUBTOTAL functions are designed to work with filtered data.

For an average of a filtered range...

=SUBTOTAL(1,A5:A100)

That will return the average of only the visible cells within the range A5:A100.

1 or 101 ?

 

[T. Valko]

1 or 101 ?

1 for filtered data only.

101 for both filtered data and cells that are hidden by any other means.

so when filtering by month it will only populate the average for the selected month?

That sounds like 1 to me.

 

[FDibbins]

Rick, you beat me to it, not often you can catch Tony on something like that

 

[T. Valko]

not often you can catch Tony on something like that

Like what?

 

[T. Valko]

P.S.

Also, it depends on what version of Excel they're using.

The 100 series arguments aren't available in every version of Excel.

 

[leewoo5]

P.S.

Also, it depends on what version of Excel they're using.

The 100 series arguments aren't available in every version of Excel.

Thank you for getting back to me. The rows are manually hidden with the macro, and not filtered. I am looking to create an average of the unhidden cells; in this instance the range is F16:BE16

Many thanks,

 

[Rick Rothstein]

1 for filtered data only.

101 for both filtered data and cells that are hidden by any other means.

so when filtering by month it will only populate the average for the selected month?

That sounds like 1 to me.

Obviously, I don't use SUBTOTAL much. I am using XL2010 with the built-in help files (not the online help files) and that version does not have the quote you posted; however, it did have this...

For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list.

The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.

That second paragraph, if I am reading it right, seems to say either 1 or 101 would work, so I guess that means both, you are right and I am not wrong.

 

[T. Valko]

Thank you for getting back to me. The rows are manually hidden with the macro, and not filtered. I am looking to create an average of the unhidden cells; in this instance the range is F16:BE16

OK, that makes this more difficult!

The SUBTOTAL function only works on columns, not rows.

I think you're going to need a UDF for this.

Unfortunately, I'm not much of a programmer so I won't be able to help with that.