Why doesn't my If And formula work?

[DonnaJoh]

Good Morning:

I am trying to utilize the following If And formula but it doesn't seem to be working.

=IF(AND(O8>="95",<="100","25",IF(AND(08>="85",<="94","20",IF(AND(O8>="70",<="84","15",IF(AND(O8>="50",<="69","10",IF(AND(O8>="30",<="49","5",IF(O<="29","0","null")))))))))))

I am trying to display a set value of points for a score with a range.


[TABLE="width: 232"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Score Range[/TD]
[TD][/TD]
[TD]=[/TD]
[TD]


[/TD]
[TD]

# of Pts[/TD]
[/TR]
[TR]
[TD]95to100% 85to94%
70to84% 50to69% 30to49% <=29% [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25 20 15 10 5 0[/TD]
[/TR]
</tbody>[/TABLE]

How can I fix this?

 

[KevCarter]

Good Morning:

I am trying to utilize the following If And formula but it doesn't seem to be working.

=IF(AND(O8>="95",<="100","25",IF(AND(08>="85",<="94","20",IF(AND(O8>="70",<="84","15",IF(AND(O8>="50",<="69","10",IF(AND(O8>="30",<="49","5",IF(O<="29","0","null")))))))))))

I am trying to display a set value of points for a score with a range.


[TABLE="width: 232"]
<tbody>[TR]
[TD]Score Range[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]

# of Pts[/TD]
[/TR]
[TR]
[TD]95to100% 85to94%
70to84% 50to69% 30to49% <=29%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25 20 15 10 5 0[/TD]
[/TR]
</tbody>[/TABLE]

How can I fix this?

Donna, untested, but please try this:

[COLOR=#000000][FONT=&quot]=IF(AND[COLOR=#006107]([/COLOR][COLOR=#0057d6]O8[/COLOR]>=95,[COLOR=#0057d6]O8[/COLOR]<=100[COLOR=#006107])[/COLOR],25,IF[COLOR=#006107]([/COLOR]AND[COLOR=#ab30d6]([/COLOR]8>=85,[COLOR=#0057d6]O8[/COLOR]<=94[COLOR=#ab30d6])[/COLOR],20,IF[COLOR=#ab30d6]([/COLOR]AND[COLOR=#a54a29]([/COLOR][COLOR=#0057d6]O8[/COLOR]>=70,[COLOR=#0057d6]O8[/COLOR]<=84[COLOR=#a54a29])[/COLOR],15,IF[COLOR=#a54a29]([/COLOR]AND[COLOR=#33af4a]([/COLOR][COLOR=#0057d6]O8[/COLOR]>=50,[COLOR=#0057d6]O8[/COLOR]<=69[COLOR=#33af4a])[/COLOR],10,IF[COLOR=#33af4a]([/COLOR]AND[COLOR=#ff9c1b]([/COLOR][COLOR=#0057d6]O8[/COLOR]>=30,[COLOR=#0057d6]O8[/COLOR]<=49[COLOR=#ff9c1b])[/COLOR],58,IF[COLOR=#ff9c1b]([/COLOR]O<=29,0[COLOR=#ff9c1b])[/COLOR][COLOR=#33af4a])[/COLOR][COLOR=#a54a29])[/COLOR][COLOR=#ab30d6])[/COLOR][COLOR=#006107])[/COLOR])[/FONT][/COLOR]

 

[DonnaJoh]

Now I am getting #NAME ? in the field I wanted the answer displayed in. What does that mean? Shouldn't it just display the points value assigned to that score range?

 

[FormR]

Hi, a couple of other options you can try,

If the number in O8 is formated as a percentage:

=5*(MATCH(O8,{0,30,50,70,85,95}/100)-1)

Or if it's just a number.

=5*(MATCH(O8,{0,30,50,70,85,95})-1)

 

[Fluff]

Or sticking with the ifs
=IF(O8<=29,0,IF(O8<=30,5,IF(O8<=50,10,IF(O8<=70,15,IF(O8<=85,20,IF(O8<=100,25,""))))))

 

[DonnaJoh]

Cell O8 is formatted as a percentage, but I'm not sure I'm understanding what I should do with the formula you provided above? Cell O8 already contains a formula which is formatted as a percentage. This formula is: =SUM(Q8:AB8)/12

Not to get side tracked, but I actually have another question on the above Sum formula. Instead of dividing it by 12 (12 Mos), I really would like it to be divided by the number of entries in these 12 cells. For instance, if only 9 of these 12 cells displays a number, then the above Sum formula should be divided by 9 (not 12). Is there a way for excel to determine this and divide it by the correct number of cells which have entries?

 

[FormR]

I'm not sure I'm understanding what I should do with the formula you provided above?

Hi, (assuming you are responding to me) put it in the cell where you want your result and report back if it gives you the answers you expect.

EDIT:

for the side track question try:

=SUM(Q8:AB8)/COUNT(Q8:AB8)

 

[DonnaJoh]

I just tried your formula "Sticking with the Ifs" and it just displays a "0" answer. Thanks anyway

 

[DonnaJoh]

Yes, that at least solves the Sum question.....thank you sooo much!! Now if I can only resolve the original equation issue

 

[Fluff]

Have you tried either of the options provided by FormR in post#4.
Also what is the value of O8?