+ Reply to Thread
Results 1 to 7 of 7

Median If with Subtotals

  1. #1
    Registered User
    Join Date
    08-09-2018
    Location
    San Francisco, CA
    MS-Off Ver
    2016
    Posts
    3

    Talking Median If with Subtotals

    Hi All,

    Long time reader, first time poster. I've dug around several boards and am having difficulty finding a solution to my issue. I'm trying to calculate the Median of a data set based on one specific criteria, but I also want that calc to update if I filter my data. I'm familiar with the median(if) array formula:

    =MEDIAN(IF($C$3:$C$32=$G3,$E$3:$E$32))

    However that doesn't help out with the filtering component. I'm attaching a sample data set where I've got the above formula in column H and a static formula to calc the median of the selected Classification/Season in column I; what I need is a formula that I can apply to the entire range to calculate dynamically. To give a bit more detail, I would want column I to produce the same results if I filter column D to Spring, but update if I change that filter to Summer/Fall/Holiday.

    Thank you in advance!
    Attached Files Attached Files
    Last edited by SDInvPl; 08-09-2018 at 01:26 PM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Median If with Subtotals

    Rather than filtering the data and calculating on the visible rows, perhaps add a drop down list to select the season and add another condition to your MEDIAN IF, for example:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached. Change the value in the yellow cell to update the table.

    BSB
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-09-2018
    Location
    San Francisco, CA
    MS-Off Ver
    2016
    Posts
    3

    Re: Median If with Subtotals

    Thank you for the suggestion BadlySpelledBuoy! Unfortunately my actual data set contains 30+ seasons and additional potential filter columns; the number of iterations takes a drop-down list out of play I think. Really hoping there's a dynamic way to make it happen!

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Median If with Subtotals

    No probs, just an idea.

    Good luck.

    BSB

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Median If with Subtotals

    Hi SDInvPl. Welcome to the forum.

    Your formula in H3 needs to be array entered to get the correct number. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    The AGGREGATE function has ways to return MEDIAN without array entry.

    To filter the results include a nested SUBTOTAL/OFFSET. This returns 264 at my end when filtered for Spring.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  6. #6
    Registered User
    Join Date
    08-09-2018
    Location
    San Francisco, CA
    MS-Off Ver
    2016
    Posts
    3

    Re: Median If with Subtotals

    Thanks so much Dave, this is amazing! Would you happen to be able to help me understand the syntax? I'm trying to perform a similar calc for min/max.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Median If with Subtotals

    You're welcome. Thanks for the feedback.

    On the explanation I'll do my best.

    You will want to be acquainted with using the F9 function key on the upper keyboard as well as the Evaluate formula feature in the Formulas ribbon ... aka Fx.

    OFFSET is the heart of this formula. As SUBTOTAL will only accept ranges OFFSET is capable of sub dividing ranges that SUBTOTAL will accept as ranges.

    Here $E$3 sets the starting cell reference. All other arguments are relative to that cell. It's an "anchor" point. The next argument is rows. This / these (there can be an array of them) define the offset row wise from $E$3. 0 offset is on the same row as $E$3, 1 is offset one row which is E4.

    ROW($E$3:$E$32)-MIN(ROW($E$3:$E$32)) returns an array of offsets. If you select just that portion of the formula in the formula bar and hit the F9 function key you will see the array of row offsets

    {0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29}

    Hit escape. Select just

    OFFSET($E$3,ROW($E$3:$E$32)-MIN(ROW($E$3:$E$32)),)

    and hit F9. You will see the individual cell values in an array like this

    {330;264;259;293;354;344;357;295;391;263;270;397;393;364;273;211;344;319;396;211;249;351;228;284;211;278;248;352;252;264}

    That's all of the values in column E which are passed on to SUBTOTAL. The first argument in SUBTOTAL is the function_num. There are two sets of 11 of them. The 100 series instructs to ignore hidden cells. The less than 100 series (1-11) instructs to ignore invisible cells. They sound alike, but they are not. Invisible cells are invisible due to filtering. Hidden rows are hidden by the user. All the function_num ignore invisible cells. The 100 series also ignores hidden cells. Confused yet?

    I set the argument 103 just to be sure. 3 might work for you as well. This is the COUNTA function. It functions just like the spreadsheet function COUNTA does. SUBTOTAL(103 returns a count of each cell that OFFSET defined above.

    Select just

    SUBTOTAL(103,OFFSET($E$3,ROW($E$3:$E$32)-MIN(ROW($E$3:$E$32)),))

    F9 it and see

    {1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}

    Remember that and hit escape. That whole array will be multiplied by the next step.

    Select ($C$3:$C$32=$G3) and hit F9. You will see an array of TRUE/FALSE. When math operations are applied to these they are coerced into their underlying values 1/0. By now you should know how to verify that and see the results ... an array of 1/0.

    These serve as the denominator to the whole range $E$3:$E$32. When you select that whole fraction and hit F9 there is an array of some elements of $E$3:$E$32 and a bunch of #DIV/0! errors. Remember that and hit escape.

    This is all passed to AGGREGATE. AGGREGATE has 19 different functions 1-19 and PERCENTILE.INC is one of them ... function 16. The 6 is one the options to ignore those #DIV/0! errors. The last argument is the k value stating which percentile to return ... 0 - 1. 0.5 is the 50th percentile which is the median. There is an argument 12 which is median, but it applies to ranges and not arrays.

    Applying the F9 examines parts of a formula out of context. To see each step in context apply the Evaluate formula feature.

    With the cell in question the active cell click Formulas on the ribbon and Evaluate formula. Clicking the Evaluate button repeatedly reveals step by step how Excel calculates the formula in context.

    The F9 and Fx are helpful tools for trouble shooting, analysis and self instruction. I recommend them whenever I can. (I think it's putting us out of business. LOL )

    Did this help?

    Edit I forgot to mention.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Last edited by FlameRetired; 08-09-2018 at 07:52 PM. Reason: Closing thought.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-26-2017, 11:28 AM
  2. Replies: 8
    Last Post: 08-09-2016, 12:50 AM
  3. Replies: 1
    Last Post: 11-22-2015, 08:35 AM
  4. [SOLVED] Using Median IF to calculate the median for a specific criteria
    By boynejs in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-20-2014, 01:50 AM
  5. Using Median to get the median of seconds per day per checkpoint
    By dodgerpixie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2014, 03:38 AM
  6. Median Indirect: Find median in range and bring back adjacent cells
    By Keelin in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-27-2014, 08:31 AM
  7. [SOLVED] Median Ifs, need to find median $ amount per deal for each year
    By xenomorph8472 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2012, 02:01 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1