# If Formula Expression Limit

#### Cullen

##### New Member
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

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### shemayisroel

##### MrExcel MVP
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

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

#### lenze

##### Legend
Use a macro with Select Case. Something like
Code:
``````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

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

Replies
3
Views
491
Replies
16
Views
933
Replies
0
Views
425
Replies
3
Views
354
Replies
1
Views
261

1,190,648
Messages
5,982,118
Members
439,756
Latest member
alice128

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back