Median with Multiple Criteria

[Nekamala]

I'm trying to convert the below averageifs formula to median formula and am having trouble. Help?

IFERROR(AVERAGEIFS(SA_Comp[Turnaround],SP_Comp[Individual],$B$10,SP_Comp[Request],Stock,SP_Comp[Year],$B$3,SP_Comp[Quarter],$F$7),"-")

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IFERROR(MEDIAN(IF(SP_Comp[Individual]=$B$10,IF(SP_Comp[Request]=Stock,IF(SP_Comp[Year]=$B$3,IF(SP_Comp[Quarter]=$F$7,SA_Comp[Turnaround]))))),"-")

 

[Nekamala]

I tried the formula you gave me and it's still not working for me

 

[markmzz]

I tried the formula you gave me and it's still not working for me

Maybe I'm wrong, but I think that the problem is in your formula (the correct is SP_Comp and not SA_Comp)

IFERROR(AVERAGEIFS(SP_Comp[Turnaround],SP_Comp[Individual],$B$10,SP_Comp[Request],Stock,SP_Comp[Year],$B$3,SP_Comp[Quarter],$F$7),"-")

The Aladin's formula is ok (only a small modification in red)

Control+shift+enter, not just enter:

=IFERROR(MEDIAN(IF(SP_Comp[Individual]=$B$10,IF(SP_Comp[Request]=Stock,IF(SP_Comp[Year]=$B$3,IF(SP_Comp[Quarter]=$F$7,SP_Comp[Turnaround]))))),"-")​

Markmzz​

 

[Aladin Akyurek]

I tried the formula you gave me and it's still not working for me

Did you apply control+shift+enter?

Control+shift+enter means: Press down the controland the shift keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[Nekamala]

Hi Aladin,

I did apply control+shift+enter, and it gave me "-", which is not correct. Should be 48. I'm just not sure why it's not working

 

[Aladin Akyurek]

Hi Aladin,

I did apply control+shift+enter, and it gave me "-", which is not correct. Should be 48. I'm just not sure why it's not working

Does your AVERAGEIFS work?

Is your Table name SP_Comp or SA_Comp?

 

[Nekamala]

Yes - the averageifs is working perfectly. The table is named SP_Comp. I mistyped the SA_Comp up above for Turnaround, but fixed it within the formula you provided and got "-" as my answer.

 

[Aladin Akyurek]

Yes - the averageifs is working perfectly. The table is named SP_Comp. I mistyped the SA_Comp up above for Turnaround, but fixed it within the formula you provided and got "-" as my answer.

The Table name corrected and control+shift+enter correctly applied, we would see in the formula bar { and } around the formula. Is that the case?

 

[Nekamala]

Yes, that's the case