samwell

New Member
Joined
Jul 20, 2012
Messages
3
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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

jeffmb

Board Regular
Joined
Jul 18, 2008
Messages
184
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

New Member
Joined
Jul 20, 2012
Messages
3
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

New Member
Joined
Jul 20, 2012
Messages
3
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

Board Regular
Joined
Jul 18, 2008
Messages
184
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top