Median with multiple condition formula

[kogur]

Hey folks,

As the title says, I am trying to make this formula work. Any idea what I might be doing wrong?

{=MEDIAN(IF('Raw Median Incidents'!$Y:$Y="1 - Critical",IF('Raw Median Incidents'!$F:$F="2019",IF('Raw Median Incidents'!$G:$G="2/2019",'Raw Median Incidents'!$I:$I))))}

Column Y=Priority
Column F=Year Closed
Column F=Month Closed
Column I = Hours to resolve

I'm sure you all see what I'm doing but just in case - I want the Median Hours to resolve based on priority, year, and month.

It keeps coming back with a NUM error.

I've been on the fence with kutools for years - would that help with this?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try removing the quotes from "2019"

 

[kogur]

Thank you Fluff I will update now!

 

[Fluff]

Thanks for that, did you try removing the quotes?

 

[kogur]

Just did - now instead of an NUM error it comes back with a NAME error. I think the quotes have to be there. I could definitely be wrong though!

 

[Fluff]

Did you just remove them from the 2019?

 

[kogur]

WHY did that work!?

 

[kogur]

I mean - thank you but HUH!?

 

[Fluff]

Because using "2019" makes it text & not a number.

As you are using xl365, you can also use

=MEDIAN(FILTER($I:$I,($Y:$Y="1 - Critical")*($F:$F=2019)*($G:$G="2/2019")))

Although you should avoid using whole column references.

 

[kogur]

You're formula is much better! Why avoid using entire columns? It keeps you from having to adjust ranges based on newly added data, no?