Forum Discussion
Median & Mode with IF conditions AND eliminating blank cells
Attached screenshot.
I'm having trouble figuring out how to eliminate the formula from considering blank cells from our attendance on median and mode functions that already have an IF function attached to them.
Any ideas?
Confirm the formula for median and mode by pressing Control+Shift+Enter or Command+Enter, to turn it into an array formula.
For median, use formulas such as
=MEDIAN(IF((B2:B49="Monday")*(F2:F49<>""),F2:F49,""))
For mode, use formulas such as
=MODE(IF((B2:B49="Monday")*(F2:F49<>""),F2:F49,""))
3 Replies
As variants, without CSE on traditional Excel
=AGGREGATE(17,6,($F$2:$F$49)/($B$2:$B$49="Monday")/($F$2:$F$49<>""), 2)On 365
=MEDIAN( TOCOL( FILTER($F$2:$F$49, $B$2:$B$49="Monday"),3 ) )Straight from Microsoft documentation:
"If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included."
Confirm the formula for median and mode by pressing Control+Shift+Enter or Command+Enter, to turn it into an array formula.
For median, use formulas such as
=MEDIAN(IF((B2:B49="Monday")*(F2:F49<>""),F2:F49,""))
For mode, use formulas such as
=MODE(IF((B2:B49="Monday")*(F2:F49<>""),F2:F49,""))
