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

kate1040

New Member
Joined
Sep 13, 2006
Messages
1
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,141,592
Messages
5,707,287
Members
421,500
Latest member
Alex2302

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top