# If then statement... to many arguments. How to calculate?

#### kate1040

##### New Member
I have too many arguments for the if formula I am trying to create. Here's what I am trying to calculate:

If X is less than or equal to the following amounts, X is to be multiplied by the amount

less than .01 = 0.00
0-20 = .0297
21-30 = .0267
31-50 = .0244
51-70 = .0231
71-90 = .0225
91-120 = .0222
121-150 = .0218
150 and up = .0214

Help!

Thanks,

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Hi Kate
Welcome to the board

Two options:

- you write a table with the values and use it in a formula

- You write a formula and incude in it all the values

I posted an example

In B1, first option, uses table in D1:E9

Code:
``=A1*LOOKUP(A1,D1:D9,E1:E9)``

In B3, 2nd option,harcodes the values:

Code:
``=A1*LOOKUP(A1,{0,0;0.01,0.0297;21,0.0267;31,0.0244;51,0.0231;71,0.0225;91,0.0222;121,0.0218;150,0.0214})``

I prefer the first option. Easy to check for errors and to update. Small formula.

Hope this helps
PGC
Book1.xls
ABCDEFG
1170.504900
20.010.0297
30.5049210.0267
4310.0244
5510.0231
6710.0225
7910.0222
81210.0218
91500.0214
10
11
Sheet13

Replies
3
Views
134
Replies
3
Views
234
Replies
6
Views
203
Replies
4
Views
225
Replies
4
Views
329

1,221,508
Messages
6,160,222
Members
451,631
Latest member
coffiajoseph

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