Formula

Trying to build a formula:

To look in R3:R24 and find which value appears the most either GA, GB, GC, GD, GE, or GF. If there is an instance where two or more of the values appear the most, then take the first value.
Who is Participating?

Commented:
but having said that......two shorter options to do the same.

In Excel 2007 or later

=IFERROR(INDEX(R3:R24,MODE(IF(ISNUMBER(MATCH(R3:R24,{"GA","GB","GC","GD","GE","GF"},0)),MATCH(R3:R24,R3:R24,0)*{1,1}))),"")

or in any version

=LOOKUP("zzz",IF({1,0},"",INDEX(R3:R24,MODE(IF(ISNUMBER(MATCH(R3:R24,{"GA","GB","GC","GD","GE","GF"},0)),MATCH(R3:R24,R3:R24,0)*{1,1})))))

both require CTRL+SHIFT+ENTER as before

regards, barry
0

Commented:
Here is one way to do it. Sample file attached. Hope this is what you wanted?

Sid
Book1.xlsx
0

Author Commented:
I tried the formula, in my current data in R3:R24, there is zero instance of any of the values, but it returns a 'GA", it should just be blank.
0

Commented:
If it's only those values then try this array formula

=INDEX(R3:F24,MODE(MATCH(R3:F24,R3:F24,0)))

Confirmed with CTRL+SHIFT+ENTER

assumed no blanks in the range.....

Regards, barry
0

Author Commented:
barry there is the possibility of a blank in there
0

Commented:
>>>>I tried the formula, in my current data in R3:R24, there is zero instance of any of the values, but it returns a 'GA", it should just be blank.

Try this File

Sid
Book1.xlsx
0

Commented:
Ok try this version....

=IF(SUM(COUNTIF(R3:F24,{"GA","GB","GC","GD","GE","GF"})),INDEX(R3:F24,MODE(IF(ISNUMBER(MATCH(R3:R24,{"GA","GB","GC","GD","GE","GF"},0)),MATCH(R3:F24,R3:F24,0)*{1,1},""))),"")

With CSE again

regards, barry
0

Commented:
Ok try this version....

=IF(SUM(COUNTIF(R3:F24,{"GA","GB","GC","GD","GE","GF"})),INDEX(R3:F24,MODE(IF(ISNUMBER(MATCH(R3:R24,{"GA","GB","GC","GD","GE","GF"},0)),MATCH(R3:F24,R3:F24,0)*{1,1},""))),"")

With CSE again

regards, barry
0

Commented:
Sorry, some ranges in that formula are wrongly shown ad R3:F24, change to R3:F24....

=IF(SUM(COUNTIF(R3:R24,{"GA","GB","GC","GD","GE","GF"})),INDEX(R3:R24,MODE(IF(ISNUMBER(MATCH(R3:R24,{"GA","GB","GC","GD","GE","GF"},0)),MATCH(R3:R24,R3:R24,0)*{1,1},""))),"")

barry
0

EngineerCommented:
Barry, why not simply

=INDEX(R3:R24,MODE(IF(ISNA(MATCH(R3:R24,R3:R24,0)),FALSE,MATCH(R3:R24,R3:R24,0))))
0

Commented:
Hello Saqib,

Yes, that would cope with blanks but won't work if the most common value has only 1 instance (e.g.the only value is GA in R3) or if the whole range is blank. Also I wasn't sure whether there might be other values besides "GA", "GB" etc. so if there are entries like "xyz" or 123 then my suggested formula will ignore those, see attached

regards, barry
26908131.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.