Mode if function

[brooksc29]

How can I find the mode using an IF criteria.

I need to say, find the MODE in k2:k77 if b2:b77 = Hernandez and c2:c77 = W

thanks!

 

[barry houdini]

You can use an "array formula" like this

=MODE(IF(B2:B77="Hernandez",IF(C2:C77="W",K2:K77)))

confirmed with CTRL+SHIFT+ENTER

 

[brooksc29]

I'm getting #N/A with that array... when I hit the Fx to look into the function, there's nothing in the second box for Number 2, not sure if that matters.

Instead of putting Hernandez the array, I entered the cell where Hernandez is B89 so the array looks like this...
=MODE(IF(B$2:B$77=$B89,IF(C$2:C$77="W",K$2:K$77)))

still getting N/A however

 

[T. Valko]

I'm getting #N/A with that array... when I hit the Fx to look into the function, there's nothing in the second box for Number 2, not sure if that matters.

Instead of putting Hernandez the array, I entered the cell where Hernandez is B89 so the array looks like this...
=MODE(IF(B$2:B$77=$B89,IF(C$2:C$77="W",K$2:K$77)))

still getting N/A however

Are you sure there is a mode? If there is no mode then the MODE function will return #N/A.

 

[barry houdini]

If you modify the formula like this

=MODE(IF(B$2:B$77=$B89,IF(C$2:C$77="W",K$2:K$77*{1,1})))

then that will give you the modal value....even if there are no repeats

 

[brooksc29]

there is a mode for most, although its a small sample size, sometimes only 3 instances and 2 of them would be the same.

 

[brooksc29]

=MODE(IF(B$2:B$77=$B89,IF(C$2:C$77="W",K$2:K$77*{1,1})))

this is giving me #VALUE.

is that what you meant by modal value?

 

[barry houdini]

Are you confirming the formula with CTRL+SHIFT+ENTER?

select cell with formula, press F2 key then hold down CTRL and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar.

If you have done that and you still get #VALUE! error that may be because you have text in K2:K77, is that possible?

 

[brooksc29]

yes, it is text in K2:K77, sorry, I didn't know that made things difficult.

i have been entering it as an array with CTR/SHFT/ENTER

 

[barry houdini]

MODE itself only works with numbers - if you want the most common text value given those conditions try

=INDEX(K$2:K$77,MODE(IF(B$2:B$77=$B89,IF(C$2:C$77="W",IF(K$2:K$77<>"",MATCH(K$2:K$77,K$2:K$77,0))))))

still with CTRL+SHIFT+ENTER