Help on a formula

[leonemc]

I am trying to write an IF formula to do the following...assume the sales price is in cell a2...

Up to $250,000 $3.30
Over $250,000 and up to $500,000, add $2.65
Over $500,000 and up to $1,000,000, add $2.20
Over $1,000,000 and up to $5,000,000, add $2.00
Over $15,000,000, add $1.80


Any help would be appreciated.

 

[Excelestial]

=if(X1<=250,000,3.30,IF(X1<=50,000,3.30+2.65,IF(X1<=1,000,000,3.30+2.20,IF(X1<=5,000,000,3.30+2.00,IF(X1>15,000,000,3.30+1.80,...what happens between 5M and 15M?

 

[Scott Huish]

IF isn't the best function for this, LOOKUP is. This can be done in the formula or reference a table.

=A2+LOOKUP(A2,{0,250000.01,500000.01,1000000.01,15000000.01},{3.3,2.65,2.2,2,1.8})

In a TABLE:

Excel Workbook
	A	B	C	D	E	F	G	H
1							0	3.3
2	149000	149003.3					250000.01	2.65
3							500000.01	2.2
4							1000000.01	2
5							15000000.01	1.8
Sheet1

 

[VoG]

Not entirely clear but maybe

=A2*LOOKUP(A2,{0,250000,50000,1000000,1500000},{3.32.65,2.2,2,1.8})

 

[Scott Huish]

The op said add. I think you want + not *

 

[VoG]

The op said add. I think you want + not *

Oops! Thanks


=A2+LOOKUP(A2,{0,250000,50000,1000000,1500000},{3.32.65,2.2,2,1.8})

 

[leonemc]

Sorry, I havent had a chance to check out all the responses, i got called into a meeting. Regardless of if they work or not i appreciate the help!

 

[leonemc]

Actually, when it says add it means your adding 2.65 it means something like this...

600,000 sales price

the first $250k is 250k*3.3 plus 500k-250k * 2.65 plus 600k-500k * 2.2 = $162,500

I wanted the formula to change w/ the sales price which i have a colum for in column A.

Thanks again!