Mode function using variable range

Not sure if this is a General forum question or a VBA problem, but since im not having any luck in the general forum I will try here.


I am trying to create a mode formula that returns the mode for a specific range when all criteria is satisfied. So basically I have a long list of data I want the formula to search through all the criteria columns and then calculate the mode for only those corresponding data rows in column N that satisfy my criteria.

I have tried various if formulas and just cant do it!! Anyone got any ideas?

Please Login or Register  to view this content.

The idea would be to use the conditional formula below BUT I don't want the calculation to return the mode for the entire range I only want the mode for the numbers that meet my conditions.

for example say I have data in in the range X1:X100 and in that rows 24, 33,34 and 99 have satisfied my criteria I then need the formula to return the mode for the range X24, X33, X34 and X99.

You're not far away but you can't use AND in these type of formulas, try

=Mode(if(B5:B8219=B5,if(C5:C8219=C5,if(D5:D8219=D5,if(E5:E8219=E5,N5:N8219)))))

confirmed with CTRL+SHIFT+ENTER

Thanks thats almost perfect,

Just one more question when the mode range is only one cell I get a #N/A value how can I adjust the formula to say if the formula result is #N/A then the value should = column N and the row where the criteria was satisfied.

Actually this is not working out for me. Need a different solution

In fact MODE can give #N/A even if multiple rows satisfy your criteria because it'll give #N/A whenever there's no repeated value, so 10 rows might fulfill the criteria, if they all have a different value in column N then you'll get #N/A.

In those circumstances what value would you like to return, the first, the lowest.....?

for the first you could try this:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},INDEX(N5:N8219,MIN(IF((B5:B8219=B5)*(C5:C8219=C5)*(D5:D8219=D5)*(E5:E8219=E5),ROW(N5:N8219)-ROW(N5)+1))),MODE(IF((B5:B8219=B5)*(C5:C8219=C5)*(D5:D8219=D5)*(E5:E8219=E5),N5:N8219))))