SOLVED

Median & Mode with IF conditions AND eliminating blank cells

Copper Contributor

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?

3 Replies
best response confirmed by kyledupic (Copper Contributor)
Solution

@kyledupic 

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,""))

@kyledupic 

 

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

 

 

 

@kyledupic 

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

 

1 best response

Accepted Solutions
best response confirmed by kyledupic (Copper Contributor)
Solution

@kyledupic 

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,""))

View solution in original post