# Median formula with multiple conditions

1. ## Median formula with multiple conditions

I am frustrated with several formulas today:

1) I am trying to get a median number of J based on specific criteria from 2 columns (E and I in the attached file)
{=MEDIAN(IF('2013'!I:I="Hospice",IF('2013'!E:E="Private",'2013'!J:J),0))}

It is returning 0 when I should get 9.50 - I have tried several variations of formulas to do this including:

=IF(AND('2013'!I:I="Hospice",'2013'!E:E="Private"),MEDIAN('2013'!J:J),0) as well as several others that I have lost during the process

And I am getting either 0 or the NUM error

2) I am also trying to get the median number of J based on when E=private but I does not equal hospice and cannot figure that one out in any way, shape or form

3) I am also working on the average of the same items
I have =AVERAGEIF('2013'!E:E,"*Private*",'2013'!J:J) to get the average of Private (includes Hospice) (comes to 11)
and =AVERAGEIFS('2013'!J:J,'2013'!E:E,"*Private*",'2013'!I:I,"*Hospice*") to get the average of Private with Hospice (comes to 12.6)
but I would like the Private number to be the average of the Private without the Hospice, and I have done circles with that one too, I keep ending up with a negative number

Any and all assistance is greatly appreciated

CHillFL aka Carol

2. ## Re: Median formula with multiple conditions

Hi Carol,

Use array Formulas (I'm sure you know how to commit them):
Formula:
`Please Login or Register  to view this content.`

3. ## Re: Median formula with multiple conditions

Kaper - awesome! Thank you this worked great, I appreciate the assist! ~Carol

4. ## Re: Median formula with multiple conditions

To deal with "nothing found" situation you could use either (as you suggested):
Formula:
`Please Login or Register  to view this content.`

or my suggestion would be rather:
Formula:
`Please Login or Register  to view this content.`

Of course array formulas, and shown here only for average, but the same approach would work for median

5. ## Re: Median formula with multiple conditions

basically wrapping the whole thing with the iferror...got it! Thanks again! Carol

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