Nested IF statement Trouble

I am getting very frustrated and I think I just need another set of eyes to see this.

this is a little complicated, but here's what I'm trying to do...

I'm creating a bowling spreadsheet that displays awards won depending on what the person bowled, their average, and scores.

A bowler bowls 3 games. I need to know if any of those games deserve an award based on all of this criteria..

If they have bowled 21 games, check to see if current average is 90 or below
If they have not bowled 21 games, check to see if last years average is 90 or below. (if they bowled 21 games or more, do not check last years average)
If the average is 90 or below, then check to see if any of the 3 games are between 125 and 149.

If they're average is 90 or below and they bowled between 125 and 149, they receive an award.

I know this sounds a little confusing, but I'm at a loss. I think I have so many IF, AND and OR statements that I got really confused myself.

Thank you for any help you can give me.

Sheesh, that took some thinking, but here goes:

=IF(OR(AND(B5>=125,B5<=149),AND(C5>=125,C5<=149),AND(D5>=125,D5<=149)),IF(OR(AND(F5>=21,I5<=90),AND(F5<21,last_year<90)),"AWARD","no award"),"no award")

I tossed the order of the logic test around a bit so it checks in this order:

Please Login or Register  to view this content.

Replace the "No award" with "" if you want nothing to show in the cell.

HTH

Could someone please close my jaw ... still trying to work out this one, daddy L.

Lemme see if I get this right ....

=IF(IF($F5<21,$C$2,$I5)<=90,IF(SUM(COUNTIF($B5:$D5,{">=125",">149"})*{1,-1}),"Award",""),"")

blue part means: return last year avge if total games < 21, otherwise return Average and check if returned value is <= 90.

cool. Nice and short. now for

IF(IF($F5<21,$C$2,$I5)<=90,IF(SUM(COUNTIF($B5:$D5,{">=125",">149"})*{1,-1}),"Award",""),"")

working from inside out

Count how often there is in B5 to D5
A.: a number >= 125
B.: a number >149

multiply A with 1
multiply B with -1

Sum the previous two results. This will yield either a 1 or bigger or a 0 which translates to TRUE of FALSE for the inner IF statement.

phew!

Trying to disseminate the formula, I found a funny thing. If i calculate

=COUNTIF(B110:D110,{">=124",">149"})*{1,-1}

the result would be 3 (for values in B=250 C=127 D=127)

but wrapped in the SUM()

=SUM(COUNTIF(B110:D110,{">=124",">149"})*{1,-1})

the result is 2.

Going over the two formulas with the Evaluate Formula tool, I see that Excel evaluates the Countif function bit by bit in the first version, but wrapped in the SUM function the Countif function gets evaluated in one go. How come?

Where can I learn more about the hidden things going on with SUM????