Nested IF statement

[CJJC]

Hello,

I'm trying to return a value based on a range. If the value in D17 is >=50%, then return 3, if 20-49% then return 2, if 0-19% return 1, if -1 to -20%, return 0.3, if -20 - -49% return 0.2 and if <=-50% return 0.1

I've tried the following IFS statement =IFS(D17>=50,"3",20,"2",0,"1",-1,"0.3",-20,"0.2",-50,"0.1"), but it seems to be hung up on the range of 0-19 the return is 2 for all the values.

Where have I gone wrong?

 

[Fluff]

Hi & welcome to MrExcel.
You need to specify the D17>= for each check, not just the 1st one.

 

[CJJC]

D17 = 33%

When I try =IF(D17>=50,"3",IF(D17>=20,"2",IF(D17>=0,"1",IF(D17>=-1,"0.3",IF(D17>-20,"0.2",IF(D17>=-50,"0.1")))))) the return is 1 instead of 2

??????

 

[JGordon11]

try

=LOOKUP(D17,{-9E+99,-0.2,0,0.2,0.5},{0.2,0.3,1,2,3})

 

[Fluff]

If D17 is a real percentage then you need to use

=IF(D17>=50%,"3",IF(D17>=20%,"2",IF(D17>=0%,"1",IF(D17>=-1%,"0.3",IF(D17>-20%,"0.2",IF(D17>=-50%,"0.1"))))))

 

[CJJC]

If D17 is a real percentage then you need to use

=IF(D17>=50%,"3",IF(D17>=20%,"2",IF(D17>=0%,"1",IF(D17>=-1%,"0.3",IF(D17>-20%,"0.2",IF(D17>=-50%,"0.1"))))))

That worked!!! Thank you so very much

 

[Fluff]

Glad we could help & thanks for the feedback.