+ Reply to Thread
Results 1 to 4 of 4

Adding Subtotal to a Median IF Function

  1. #1
    Registered User
    Join Date
    09-24-2007
    Posts
    60

    Adding Subtotal to a Median IF Function

    Hello All,

    I would like to not account for hidden/filtered cells in my array.

    =Median(IF($A$2:$A$100=J4,$B$2:B$100))

    I looked through the many subtotal median examples but none included the where equal to piece my function has. Any ideas?

    Regards
    Last edited by elfvis; 03-10-2015 at 03:01 PM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Adding Subtotal to a Median IF Function

    I would recommend putting the subtotal into it's own column.
    Say C2 and filled down
    =SUBTOTAL(103,A2)

    Then use
    =Median(IF(($A$2:$A$100=J4)*($C$2:$C$100=1),$B$2:B$100))

  3. #3
    Registered User
    Join Date
    09-24-2007
    Posts
    60

    Re: Adding Subtotal to a Median IF Function

    Great way of thinking thanks!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Adding Subtotal to a Median IF Function

    You're welcome.
    I'm glad you agree.

    Too often I see people avoiding that method simply because they don't like using helper columns.
    They want it all contained in 1 formula.
    I can never really understand that.

    It's particularly useful if you will have more than 1 formula that you want to work with only visible rows.
    So instead of doing the sumproduct work in ALL formulas, you just have it done on one column and all formulas can refer to that.
    Much more efficient.

    And if you have 1 formula that needs to only look at visible rows
    Then I'd bet the farm (I'd never 'really' bet the farm on anything) you will have more.

+ 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. [SOLVED] adding additional parameter to median formula
    By justinhampton81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-19-2013, 10:17 AM
  2. Adding median lines to xy scatter graph
    By Richard Buttrey in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 12-08-2010, 09:05 PM
  3. Chart: adding a median line
    By keepyournose14 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-24-2009, 09:53 AM
  4. MEDIAN Subtotal??
    By elfiky in forum Excel General
    Replies: 1
    Last Post: 08-15-2008, 09:12 AM
  5. Subtotal the median
    By Terri in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-26-2006, 12:45 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