Conditional formula

[asyamonique]

[TABLE="width: 219"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]-2,000[/TD]
[TD]/[/TD]
[TD]-5,000[/TD]
[TD]=[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]-5,000[/TD]
[TD]/[/TD]
[TD]-10,000[/TD]
[TD]=[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]-10,000[/TD]
[TD]/[/TD]
[TD]-20,000[/TD]
[TD]=[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]-20,000[/TD]
[TD="colspan: 2"]and above[/TD]
[TD]=[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

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...

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]-2,000[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]-5,000[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]-5,000[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]-10,000[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]150[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]-10,000[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]-20,000[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]250[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]-20,000[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]500[/TD]
[/TR]
</tbody>[/TABLE]

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