Formula help required

[Beena K]

I have 3 conditions
score divided into 3 parts 50, 75 and 100 %
If score in cell = > 27 then -3
If score in cell = >26<36 = -2
If score in cell = >18<27 = -1
My formula

=IF(F48>27,"-3",IF(F48>=26<36,"-2",IF(F48<=18<27,"-1")))
If score in cell is > 27 shows false
Please advise
Cordially
Beena K
Process Analst

 

[Per Jessen]

Your conditions are a bit ambigous, shouldn't the first criteria be >=37 ?

Conditions for the formula below:

F48 >=37 result -3
F48 between 26 and 36 result -2
F48 between 18 and 25 result -1
F48 < 18 result 0

=IF(F48>=37,-3,IF(F48>=26,-2,IF(F48>=18,-1,0)))

Hopes this helps.
....
Per

 

[Fred Smith]

First, do *not* put numbers in quotes. That changes them to text, which
means you can't use them in other formulas.
Second, you want the AND function the way you have written your statement,
as in:
=IF(F48>27,-3,IF(AND(F48>=26,F48<36),-2,IF(AND(F48<=18,F48<27),-1)))

Third, your requirements conflict. With the options you've listed, the
result can never be -2. You also had your test for 18 backwards. So do you
mean:
=IF(F48>37,-3,IF(AND(F48>=26,F48<36),-2,IF(AND(F48>=18,F48<27),-1)))

Fourth, you can simplify this a lot, because once you've tested for F48>37,
you don't have to do it again. So use:
=IF(F48>37,-3,IF(F48>=26,-2,IF(F48>=18,-1)))

Fifth, what happens when F48<18? What do you want then? It would be either:
=IF(F48>37,-3,IF(F48>=26,-2,IF(F48>=18,-1,"Error")))
=IF(F48>37,-3,IF(F48>=26,-2,-1))

Regards,
Fred