Formula construction

[Jon Johny]

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->
I have a question concerning the COUNTIF function.

1)I want to create something below, in cell C2, which basically looks at the range C3:C5 and assigns a value. I have 1 rule which says that if anything in C3:C5 says 'Overtime' cell C2 should say potential risk. If these cells do not say 'Overtime' but has any cells in that range that say 'Regular Season', then make C2 'Regular Season'. Finally if all the cells in C3:C5 say 'Playoffs', than make C2 'Playoffs'.

2)One thing I did not mention, is if all the cells in the C3:C5 range say NA then cell C2 should be NA, if all the cells in the range are blank then C2 should be blank. If all the cells in the range are a mixture of just blank and 'NA', then C2 should be NA.

The formula I have up till now reads,
=IF(COUNTIF(C3:C5,"Overtime")>0,"Overtime",IF(COUNTIF(C3:C5,"Regular Season")>0,"Regular Season","Completed"))

However, I need to incorporate the part in paragraph 2.

Any help is appreciated.

 

[jasonb75]

If I've read it right, try

=IF(COUNTIF(C3:C5,"Overtime")>0,"Potential Risk",IF(COUNTIF(C3:C5,"Regular Season")>0,"Regular Season",IF(COUNTIF(C3:C5,"Playoffs")>0,"Playoffs",IF(COUNTIF(C3:C5,"NA")>0,"NA",""))))

 

[Jon Johny]

This formula works, but I really dont understand the logic. How does it understand, for example to put "Playoffs" in cell C2 - only when the entire range has the value "Playoffs". And the same would go for NA and ""

For 'Regular Season', only one of the fields in the range has to have 'Regular Season' for C2 to turn 'Regular Season'

And how does it know that if just one 'Overtime' is in the range - C2 will have to be 'Overtime'

Thanks,

Is it just the order that youv've constructed it in?

 

[nbrcrunch]

Jon

Excel offers a feature to "Evaluate Formula." Click a cell containing the formula then click the option. it will walk you through it so that you can see how it works.

 

[Jon Johny]

I've used 'Evaluate Formula' option before. But in this formula, I dont udnerstand how it understands that a) the entire range must be 'playoffs' for c2 to go 'playoffs' and only 1 cell in the range has to be 'regular season' for C2 to go regular season. The fomula is constructed the same way in both cases, so i dont uderstand how it distinguishes.

 

[jasonb75]

It's all done with the sequence of the formula, if it finds one cell in the range that matches the first "text" criteria then it stops and gives that result, if it doesn't find that result anywhere it moves to the next "text" criteria and the process is repeated until it eventually finds something that matches.

If there is no match by the time it checks the last criteria it leaves the cell blank.

Try looking at it as a series of events, blue text added in to make it easier to understand

IF(COUNTIF(C3:C5,"Overtime")>0,"Potential Risk", if not
IF(COUNTIF(C3:C5,"Regular Season")>0,"Regular Season", if not
IF(COUNTIF(C3:C5,"Playoffs")>0,"Playoffs", if not
IF(COUNTIF(C3:C5,"NA")>0,"NA", if not
""))))

Hope this helps

 

[Jon Johny]

Ok, but accrding to how this formula performed, all cells in the range had to be 'Playoffs', for C2 to go playoffs. And not just one cell as is the case with overtime. I dont understand this

 

[jasonb75]

By using Countif()>0 the formula stops if it finds 1 or more cells in the range with that "text" in, so 1 or more counts of "overtime" would show "Potential Risk"

If there was no cell with "Overtime" then the formula moves to "Regular Season", again 1 a count of 1 or more cells with this would show that result, a count of 0 would move onto "Playoffs", count of 1 or more gives that result, count of 0 moves to "NA", count of 1 or more gives that result, count of 0 means no match so result is a blank cell.

 

[shemayisroel]

Jon Johny,

jasonb75 solution seems like the right one, if not please post some sample data with expected results and I'm sure some one can post a solution up for you.