Home > Forum Home > Analyzing Corporate Financial Data > Functions on Filtered Data Share

Functions on Filtered Data

Excel Help for Functions On Filtered Data in Analyzing Corporate Financial Data


Forum TopicPost Reply Login

Functions On Filtered Data

Rate this:
(4.7/5 from 6 votes)
OopsThe SUBTOTAL function provides some basic calculations on filtered data such as averages, standard deviation, maximum and minimum values. More advanced calculations such as statistical functions including the CORREL correlation function as well as any of the other inbuilt functions within Excel cannot be performed on filtered data ranges without applying complicated array formulas.

Another alternative and effective solution is to apply a user defined function to the filtered data ranges in order to return an array of values representing only the visible data. This way all functions in Excel can be applied to filtered data including common statistical functions such as correlations and forecasting.

The following VBA code creates a user defined function that returns only the visible data with the input range.

VBA Code
Function VISIBLE(InputRange As Range) As Variant
Dim arrOut(), ic As Variant
Dim vcount As Long
vcount = 0
For Each ic In InputRange
  If ic.Rows.Hidden = False And ic.Columns.Hidden = False Then
    ReDim Preserve arrOut(vcount)
    If IsNumeric(ic.Value) Then
      arrOut(vcount) = ic.Value
    Else
      arrOut(vcount) = 0
    End If
    vcount = vcount + 1
  End If
Next
VISIBLE = arrOut
End Function

This can then be used within standard Excel functions on the filtered data such as correlation in

=CORREL(VISIBLE(A2:A100),VISIBLE(B2:B100))
 Excel Business Forums Administrator
 Posted by on
 
Replies - Displaying 1 to 1 of 1Order Replies By: Most recent | Chronological | Highest Rated
Confused
Rate this:
(3/5 from 1 vote)
This is a great solution to work with filtered data. If you are looking for different options, AI tools like SkySuite AI can also efficiently handle complex calculations of transformed data. It does a lot of work for projects beyond what standard Excel functions can do and can be a great addition to advanced data analysis.
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Functions on Filtered Data in the