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

[kate1040]

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,

 

[pgc01]

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

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

In B3, 2nd option,harcodes the values:

=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
	A	B	C	D	E	F	G
1	17	0.5049		0	0
2				0.01	0.0297
3		0.5049		21	0.0267
4				31	0.0244
5				51	0.0231
6				71	0.0225
7				91	0.0222
8				121	0.0218
9				150	0.0214
10
11
Sheet13