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
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