Excel formula based on three ranges 1

[Spenello]

Trying to create formula for old dice game called Kismet.
If basic score is 63 - 70, then a bonus of 35 is added.
If basic score is 71 - 77, then a bonus of 55 is added.
And, if basic score is over 78, then a bonus of 75 is added.

Can get two of three criteria to function, but can't get all three to work. Any ideas?

 

[Lilliabeth]

=IF(A1<71,A1+35,IF(A1>78,A1+75,A1+55))

--Lilliabeth

 

[SkipVought]

hi,

I prefer to make a table that represents the limits and values you have specified...
[tt]
BaseScore Bonus
0 0
63 35
71 55
78 75
[/tt]
and the formula, using Named Ranges and assuming that your score is in A1
[tt]
=INDEX(Bonus,MATCH(A1,BaseScore,1),1)
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lilliabeth]

oops. I didnt account for a score <63. Here is the corrected formula
=IF(A1<63,A1,IF(A1>78,A1+75,IF(A1>70,A1+55,A1+35)))

I prefer lookup tables too, but I'm not always sure a poster will. It helps to know though that this aspect of scoring in Kismet has been the same for several decades.

--Lilliabeth

 

[Spenello]

That worked, Lillabeth.

Nice to see someone else who knows the game of Kismet.
Thanks