mercredi 3 février 2021

Incorrect result for nested condition in MEDIAN-IF excel

I have a following excel spreadsheet which consist of following fields:

  • Col A: Timestamp
  • Col B: Numerical result
  • Col C: Time duration taken for calculation of result

enter image description here

Now, I'm trying to find the median value of col C (Duration) for various month and year combinations.

e.g. For the month of march in 2019, what's the median value of duration?

I could've used the MEDIANIFS, but sadly it didn't exists. I'm trying the below thing also, but it's not giving the correct result(G1 is a drop-down which consists numerical valued years i.e. 2019, 2020 and so on)

MEDIAN(IF(YEAR(A3:A100) = G1, IF(MONTH(A3:A100) = 3, C3:C100)))

I also tried ANDing the conditions but it also didn't worked:

MEDIAN(IF((YEAR(A3:A100) = G1) * (MONTH(A3:A100) = 3), C3:C100))

If I put one condition inside the Median(If()), it's working fine. But, whenever I nest or concat conditions, it's not giving the correct result.

Any help/pointers will be highly appreciated.

Aucun commentaire:

Enregistrer un commentaire