Prev: vlookup multiple data
Next: Repeat the macro using different ranges
From: Beena K Beena on 5 Mar 2010 03:28
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

From: Per Jessen on 5 Mar 2010 03:58
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

"Beena K" <Beena K(a)discussions.microsoft.com> skrev i meddelelsen
news:0D116FAD-116E-46B1-A85B-CA0BC09BFEE0(a)microsoft.com...
> 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
>
From: Fred Smith on 5 Mar 2010 04:01
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

"Beena K" <Beena K(a)discussions.microsoft.com> wrote in message
news:0D116FAD-116E-46B1-A85B-CA0BC09BFEE0(a)microsoft.com...
>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
>

 | 
Pages: 1
Prev: vlookup multiple data
Next: Repeat the macro using different ranges