+ Reply to Thread
Results 1 to 4 of 4

adding additional parameter to median formula

  1. #1
    Registered User
    Join Date
    12-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    adding additional parameter to median formula

    Stuck on adding a single additional parameter to a MEDIAN formula. I've decided to use MEDIAN instead of AVERAGEIFS, but there is no MEDIANIFS.

    =MEDIAN(IF(MARKET!E:E=A8,MARKET!M:M)) is the formula I am currently using, but I need to add an additional parameter that states if Market!D:D = "X"

    I've been toying with this for an hour or so and can't get there. Any help would be much appreciated. The info I found online was far more complex than I needed, and I wasn't able to make heads or tails of it.

    Thanks in advance.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: adding additional parameter to median formula

    Maybe..

    =MEDIAN(IF((MARKET!E:E=A8)*(Market!D:D = "X"),MARKET!M:M))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    12-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: adding additional parameter to median formula

    That works perfectly. That syntax looks almost identical to what I tried, but I was using a comma instead of an asterisk. Would you mind explaining why the asterisk was used, instead of the comma?

    A8 is a text field, so it can't be multiplying..... Thanks a ton!

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: adding additional parameter to median formula

    The asterisk multiplies results of both conditions

    Excel reads TRUE as 1 and FALSE as 0. Only if both conditions are met will the formula include the relevant cell in the MEDIAN calculation

+ 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 'IF' statements into a dynamic list formula (Pt:2)
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-14-2012, 04:56 AM
  2. [SOLVED] Adding additional 'IF' statements into a dynamic list formula
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-13-2012, 05:47 AM
  3. Replies: 4
    Last Post: 04-22-2012, 07:55 AM
  4. Adding additional string to formula
    By rhudgins in forum Excel General
    Replies: 2
    Last Post: 06-23-2011, 03:38 PM
  5. [SOLVED] Adding additional rows with formula
    By Mark in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2006, 08:40 AM

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