SOLVED

# Median & Mode with IF conditions AND eliminating blank cells

Copper Contributor

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

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

# Re: Median & Mode with IF conditions AND eliminating blank cells

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

# Re: Median & Mode with IF conditions AND eliminating blank cells

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

# Re: Median & Mode with IF conditions AND eliminating blank cells

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

# Re: Median & Mode with IF conditions AND eliminating blank cells

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