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,

 

[Rick Rothstein]

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

Give this UDF (user defined function) a try...

Function VisibleAverage(Rng As Range) As Double
  Dim N As Long, Sum As Double, Cell As Range
  For Each Cell In Rng
    If Not Cell.Columns.Hidden Then
      N = N + 1
      Sum = Sum + Cell.Value
    End If
  Next
  VisibleAverage = Sum / N
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use VisibleAverage just like it was a built-in Excel function. For example,

=VisibleAverage(F16:BE16)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[PetGiraffe]

Related Question, how can I utilize subtotal, but with criteria?

For example, I have hidden rows, and the column that has these 'unhidden' results need to be averaged, but only if it is from a corresponding criteria met (in my case, I want to average the "response time" of certain users, and response times are randomly sampled, and I hide the rows that did not get a sample response, So I want to average each specific users response times.)

I tried

=SUBTOTAL(101,(INDEX(F4:V530,MATCH("User, Sample",F4:V530,0),17,22)))

Which didn't work. Also,

=SUBTOTAL(1,(VLOOKUP("Foster, Tawnya",F4:V530,17,FALSE)))

Which it wouldn't even take.

Anyone know a way to merge subtotal averaging with criteria?

 

[kaweng88]

Give this UDF (user defined function) a try...

Function VisibleAverage(Rng As Range) As Double
  Dim N As Long, Sum As Double, Cell As Range
  For Each Cell In Rng
    If Not Cell.Columns.Hidden Then
      N = N + 1
      Sum = Sum + Cell.Value
    End If
  Next
  VisibleAverage = Sum / N
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use VisibleAverage just like it was a built-in Excel function. For example,

=VisibleAverage(F16:BE16)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thanks for this! is there a way for the average formula to auto refresh every time a column / cell is hidden? when this happens i had to re-calculate it. Appreciate it!