Create a dollar value from a range of values that are capped

[Scottc7120]

Hi All,

I am wanting to create a formula that calculates a fee dependent on the value of the sale. Where Cell A1 would be the value of the sale

0 - 29,999.99 Capped $500

30,000 - 49,999.99 2.5% Capped $1,000

50,000 - 99,999.99 1.9% Capped $1,500

100,000 - 149,999.99 1.5% Capped $2,000

150,000 - 249,999.99 1.5% Capped $2,500

250,000+ Capped $3,000

 

[Peter_SSs]

Welcome to the MrExcel board!

There is no % given for the first or last range so I have assumed they are fixed fees rather than a 'cap' as such. See if this is what you want. If not, further explanation and examples of sales and expected fees please.

20 10 01.xlsm
	A	B	C	D	E	F
1	Sale	Fee		Amount	Fee %	Cap
2	2000	500		0		500
3	105000	1575		30000	2.50%	1000
4	148000	2000		50000	1.90%	1500
5	3000000	3000		100000	1.50%	2000
6	45000	1000		150000	1.50%	2500
7	151000	2265		250000		3000
8	1	500
9	52222	992.218
Fee

Cell Formulas
Range	Formula
B2:B9	B2	=IF(VLOOKUP(A2,D$2:E$7,2)=0,VLOOKUP(A2,D$2:F$7,3),MIN(A2*VLOOKUP(A2,D$2:E$7,2),VLOOKUP(A2,D$2:F$7,3)))

 

[Scottc7120]

Hi,
Thanks, that work for me
Kind regards

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.