Automatically Updating Formulas

[samwell]

I am trying to insert a string of formulas on a separate sheet from my data, ie standard deviation, median, mode, etc. I am trying to get the sheet of formulas (sheet 2) to update automatically given the data that is selected on sheet 1. Sheet 1 is filtered by categories allowing the user to select which category to view, which then updates the data. However, I cannot get the formulas to update when I change the visible data. If I do not select an exact range, eg if I just select row E, it includes all data, even data not visible. Any suggestions?

Thanks!

 

[jeffmb]

I just answered a similar question yesterday and this morning.

The Subtotal function will allow you to select any of a number of mathematical functions to be applied against visible cells. These functions include Sum, Average, Count, Max, Product, and Standard Deviation. The results change as you change the filter criteria.

 

[samwell]

Sorry for taking a month to reply, I didn't see your response. But, thank you so much! This worked beautifully. Do you have any idea how I could do this for Median?

 

[samwell]

Sorry for taking a month to reply, I didn't see your response. But, thank you so much! This worked beautifully. Do you have any idea how I could do this for Median?

 

[jeffmb]

The following assumes your data is in column B and there are no more than 100 rows:

=MEDIAN(IF(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),B2:B100))

To enter this as an array press CONTROL+SHIFT+ENTER.