Conditional formula

[asyamonique]

-2,000	/	-5,000	=	100
-5,000	/	-10,000	=	150
-10,000	/	-20,000	=	250
-20,000	and above	=	500

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Hello what can be the formula to show those results between the numbers?

Thanks

 

[Joe4]

For a value in cell A1:

=IF(A1>=20000,500,IF(A1>=10000,250,IF(A1>=5000,150,IF(A1>=2000,100,0))))

 

[asyamonique]

Hi,
Thanks for your help but I cannot get any result.

 

[Aladin Akyurek]

Let A1:C4 house your table...

-2,000	-5,000	100
-5,000	-10,000	150
-10,000	-20,000	250
-20,000		500

<tbody>
</tbody>

Let E1 house a value of interest that you want to evaluate...

In F1 enter...

Either:

=INDEX($C$1:$C$4,MATCH(E1,$A$1:$A$4,-1))

Or:

=LOOKUP(ABS(E1),ABS($A$1:$A$4),$C$1:$C$4)

 

[Joe4]

Sorry, if your values really are negatives, we just need to change the signs and direction of arrows:

=IF(A1<=-20000,500,IF(A1<=-10000,250,IF(A1<=-5000,150,IF(A1<=-2000,100,0))))

 

[asyamonique]

Many Thanks