If condition excel 2007

Microsoft Microsoft office excel 2007 -...
September 23, 2009 at 02:00:13
Specs: Windows XP
I have the following query:
E.g. I have 10 cells with either of the following values:
I need an IF condition where I could fill another cell based on the number of occurances of "High", "Medium", "Low". So, if the out of those 10 cells, "High" occurs more number of times than "Medium" or "Low", then fill a particular selected cell with "H"

See More: If condition excel 2007

Report •

September 23, 2009 at 04:52:08

There are likely several ways to do this.

Here is one way.

In cells B3 to B12 are your 10 input statuses
Cells C3 to C5 provide a count of the number of occurences of each status
C3 contains =COUNTIF($B$3:$B$12,D3)
and C4, C5 follow the same pattern, taking the value to be counted from the cells in column D
(The words must be identical to those used in the ten entries in column B - use data validation to ensure entries are valid)
Cell E3 reports the most frequent.
The formula in E3 is =VLOOKUP(MAX(C3:C5),C3:D5,2,FALSE)

	B	C	D	E
	Inputs	Counts	Status	Result
3	High	4	High	Medium
4	Medium	5	Medium		
5	High	1	Low		
6	Medium				
7	Medium				
8	High				
9	High				
10	Medium				
11	Low				
12	Medium				

There is a problem in that you can get two statuses present in equal numbers
In the above solution if High=5 and Low=5 for example, the result returned will be High
If you reversed the order of the statuses in column D with Low first, the result would be Low as Vlookup returns the first match it finds.

If you wanted a different output when two statuses occurred at the same frequency you will have to define the logic for the outcome you want.


Report •

September 23, 2009 at 06:03:15
Thanks. I also created a code using if and countif...seems to work as of now :)


Report •

September 23, 2009 at 06:21:12
What are you looking for if there is a tie?

As Humar says, there are multiples ways to solve this. This will handle 10 - or any even number of - cells since there can't be a tie between more than 2 values. If you use a range of cells that is divisble by 3, there could be a three way tie. You could easily add a condition to hanlde that.

=IF(AND(COUNTIF(A1:A10,"High")>COUNTIF(A1:A10,"Medium"),COUNTIF(A1:A10,"High")>COUNTIF(A1:A10,"Low")),"H",IF(AND(COUNTIF(A1:A10,"Medium")>COUNTIF(A1:A10,"High"),COUNTIF(A1:A10,"Medium")>COUNTIF(A1:A10,"Low")),"M",IF(AND(COUNTIF(A1:A10,"Low")>COUNTIF(A1:A10,"Medium"),COUNTIF(A1:A10,"Low")>COUNTIF(A1:A10,"High")),"L",IF(COUNTIF(A1:A10,"High")=COUNTIF(A1:A10,"Medium"),"Tie Between H & M",IF(COUNTIF(A1:A10,"High")=COUNTIF(A1:A10,"Low"),"Tie Between H & L","Tie Between M & L")))))

Report •

Related Solutions

September 24, 2009 at 05:43:03
Thanks for your reply...What I have done is to select the higher value in case of a tie. For e.g. If there is a tie between High and Medium, select High as the answer...This has been done in accordance with business requirements....

Thanks a lot people

Report •

Ask Question