If Formula Expression Limit

[Cullen]

Hey everyone, I need to write a formula that will look at multiple criteria and number ranges to give a result. It is an if formula that check a date and if its above 150k, then goes through compensation ranges and depending on where it falls, multiplies it by a percentage. I am being limited and am wondering if there is a more efficient way of doing this.

=IF(AND(C3<O25,D3>150000),"22000",IF(AND(D3<140000,D3>130000),(0.11*D3),IF(AND(D3<130000,D3>120000),(0.1*D3),IF(AND(D3<120000,D3>110000),(0.09*D3),IF(AND(D3<110000,D3>100000),(0.08*D3),IF(AND(D3<100000,D3>90000),(0.07*D3),IF(AND(D3<90000,D3>80000).....and so on until the number range is 0 to 20k.

Thanks you

 

[shemayisroel]

Maybe...

http://www.ozgrid.com/Excel/seven-nested.htm

If not then please post some sample data with expected results for another solution to be drafted.

 

[Aladin Akyurek]

Hey everyone, I need to write a formula that will look at multiple criteria and number ranges to give a result. It is an if formula that check a date and if its above 150k, then goes through compensation ranges and depending on where it falls, multiplies it by a percentage. I am being limited and am wondering if there is a more efficient way of doing this.

=IF(AND(C3<O25,D3>150000),"22000",IF(AND(D3<140000,D3>130000),(0.11*D3),IF(AND(D3<130000,D3>120000),(0.1*D3),IF(AND(D3<120000,D3>110000),(0.09*D3),IF(AND(D3<110000,D3>100000),(0.08*D3),IF(AND(D3<100000,D3>90000),(0.07*D3),IF(AND(D3<90000,D3>80000).....and so on until the number range is 0 to 20k.

Thanks you

An example you can adjust to your problem...

=D3*LOOKUP(D3,{0,10,25,40},{0.2,0.8,0.89,0.92})

 

[lenze]

Use a macro with Select Case. Something like

Sub Fanugi()
Dim cl as Variant
Dim myVal as Variant
For Each cl in Range("$D$3:$D" & Range("$D$65536").End(xlUp).Row)
Select Case cl
    Case < 20000 : myVal = cl*0.01
    Case < 30000 : myVal = cl*0.02
    Case < 40000 : myVal = cl*0.03
    'etc
    Case < 140000 : myVal =cl*0.11
    Case Else : myVal = 22000
End Select
Cells(cl.Row,5) = myVal
Next cl
End Sub

lenze

 

[Cullen]

The lookup formula worked great. Thank you guys for all your help