MODE Function

[david park]

Hello,

I'm trying to build a single statement that finds the most
common answer (MODE) but it has to conditional.
Essentially, I'm looking for a function similar to countif
or sumif, but that pertains to MODE. Is there one? or a
workaround?

DP

 

[Jason Morin]

One possibility is to apply an autofilter first to your
data (Data > Filter > AutoFilter). Let's assume it columns
A and B, and the "condition" is in column A. Select that
condition or criterion in the drop-down list for A. Now
use this formula (array-entered, meaning press
ctrl/shift/enter):

=MODE(IF(SUBTOTAL(9,OFFSET(B2:B9,ROW(B2:B100)-MIN(ROW
(B2:B100)),,1))>0,B2:B100))

to get the mode of the range of values in col. B, based on
the condition selected in col. A.

HTH
Jason
Atlanta, GA

 

[Harlan Grove]

One possibility is to apply an autofilter first to your
data (Data > Filter > AutoFilter). Let's assume it columns
A and B, and the "condition" is in column A. Select that
condition or criterion in the drop-down list for A. Now
use this formula (array-entered, meaning press
ctrl/shift/enter):

=MODE(IF(SUBTOTAL(9,OFFSET(B2:B9,ROW(B2:B100)-MIN(ROW
(B2:B100)),,1))>0,B2:B100))

to get the mode of the range of values in col. B, based on
the condition selected in col. A.

....

And the benefits of this rather than the array formula

=MODE(IF(A$2:A$100>x,B$2:B$100))

where the criteria would vary with x are what?

 

[Jason Morin]

Very good point. I'm still at work after 16 hours, which
means my brain is obviously off.

Jason