Solved

Formula

Posted on 2011-03-23
11
210 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
barry there is the possibility of a blank in there
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>>>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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 50

Expert Comment

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

772 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

10 Experts available now in Live!

Get 1:1 Help Now