Solved

Formula

Posted on 2011-03-23
11
211 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:sandramac
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35204948
Here is one way to do it. Sample file attached. Hope this is what you wanted?

=IF(COUNTIF(R3:R24,"GA")=MAX(COUNTIF(R3:R24,"GA"),COUNTIF(R3:R24,"GB"),COUNTIF(R3:R24,"GC"),COUNTIF(R3:R24,"GD"),COUNTIF(R3:R24,"GE"),COUNTIF(R3:R24,"GF")),"GA",IF(COUNTIF(R3:R24,"GB")=MAX(COUNTIF(R3:R24,"GA"),COUNTIF(R3:R24,"GB"),COUNTIF(R3:R24,"GC"),COUNTIF(R3:R24,"GD"),COUNTIF(R3:R24,"GE"),COUNTIF(R3:R24,"GF")),"GB",IF(COUNTIF(R3:R24,"GC")=MAX(COUNTIF(R3:R24,"GA"),COUNTIF(R3:R24,"GB"),COUNTIF(R3:R24,"GC"),COUNTIF(R3:R24,"GD"),COUNTIF(R3:R24,"GE"),COUNTIF(R3:R24,"GF")),"GC",IF(COUNTIF(R3:R24,"GD")=MAX(COUNTIF(R3:R24,"GA"),COUNTIF(R3:R24,"GB"),COUNTIF(R3:R24,"GC"),COUNTIF(R3:R24,"GD"),COUNTIF(R3:R24,"GE"),COUNTIF(R3:R24,"GF")),"GD",IF(COUNTIF(R3:R24,"GE")=MAX(COUNTIF(R3:R24,"GA"),COUNTIF(R3:R24,"GB"),COUNTIF(R3:R24,"GC"),COUNTIF(R3:R24,"GD"),COUNTIF(R3:R24,"GE"),COUNTIF(R3:R24,"GF")),"GE",IF(COUNTIF(R3:R24,"GF")=MAX(COUNTIF(R3:R24,"GA"),COUNTIF(R3:R24,"GB"),COUNTIF(R3:R24,"GC"),COUNTIF(R3:R24,"GD"),COUNTIF(R3:R24,"GE"),COUNTIF(R3:R24,"GF")),"GF","Not Found"))))))

Sid
Book1.xlsx
0
 

Author Comment

by:sandramac
ID: 35204967
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 35204968
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 Comment

by:sandramac
ID: 35204995
barry there is the possibility of a blank in there
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205155
>>>>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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Expert Comment

by:barry houdini
ID: 35205162
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 35205170
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 35205197
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35205207
Barry, why not simply

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

Expert Comment

by:barry houdini
ID: 35205358
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35205396
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Tags from access to excel 3 28
Highlighting cells in Excel 9 16
Excel case statements 3 24
InStr Function not working properly in macro 3 19
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now