Question !!!!!

[adaryana]

Sorry for the multiple postings. Hopefully this one will be clear and concise. Thanks for everyone who tried to help me.
Here is my billing Percentage.
0 - $499,000 - 1.15%
$500,000 - $999,999 - 0.98%
$1,000,000 - $1,999,999 - 0.93%
$2,000,000 - $2,999,999 - 0.88%
$3,000,000 - $3,999,999 - 0.83%
$4,000,000 - $4,999,999 - 0.78%
$5,000,000 + - 0.73%

So if someone has $2,250,000. He would pay 1.15% for the first $500,000. For the next $500,000 he would pay 0.98%, For the next $1m he will pay 0.93% and the remaining 250K (which is above 2M) he will pay 0.88%.

I have over a 1000 amounts in Column A, with values. I need to get the fees in column B. Can someone please help me out. If else loops seem to have a limit.
Thanks.

 

[PaddyD]

there is a formula-driven way to do this. see the "progressive pricing" example here:

http://www.cpearson.com/excel/pricing.htm

post back if you can't get it to work.

paddy

 

[adaryana]

Tried to going through the example lookslike something is off with the array formula I am doing it keeps popping back with "#Value"

Any suggestions ?
Thanks

 

[s-o-s]

Tried to going through the example lookslike something is off with the array formula I am doing it keeps popping back with "#Value"

Any suggestions ?
Thanks

Thats a common fault if you forget to Ctrl + Shift + Enter the formula...does this solve it?

 

[adaryana]

Actually I did do CTRL+SHIFT+ENTER.

I can get the example in the webpage to work fine (proves that I an copy and paste) but I can get it to work on my spreadhseet primarily because my table is larger that the example and am not able to modify it.
Can I email this to you so you can have a better idea abt what I am talking.
Thanks again.
AMit

 

[Dave Patton]

I would use an array formula, sumproduct, or VLookup for this type of calculation and similar tax calculations.

I named each each range to keep the fomula simple.
My example is on sheet "1".

Use Insert | Name | Define and name the elements of the rate table.


rB for brackets ='1'!$E$4:$E$10
rR for rates ='1'!$F$4:$F$10
rR_ for Lower range ='1'!$F$3:$F$9


E3:F3 are blank
E4 0 F4 .0115 (format to %)
E5 500000 .0098
E6 1000000 .0093

etc


With amount in A1

Formula is

=SUMPRODUCT((A1>rB)*(A1-rB)*(rR-rR_))

HTH

Dave

You can put the rate table in a range of cells and on a sheet that you select.

 

[adaryana]

That worked Just fine. Thanks a lot for your help !!

 

[RichardS]

I know you have an answer, but can you advise if the following returns the expected results?

Book2
	A	B	C	D	E	F	G
1	Amount	Commission
2	1.00	0.01		Billing	Rate	Marginal	Cumulative
3	25.00	0.29		-	1.15%	0	0
4	500,000.00	5,748.30		499,000.00	0.98%	5,738.50	5,738.50
5	998,000.00	10,628.70		999,000.00	0.93%	4,051.70	9,790.20
6	1,500,000.00	14,449.50		1,999,000.00	0.88%	14,539.00	24,329.20
7	2,500,000.00	28,738.00		2,999,000.00	0.83%	11,852.20	36,181.40
8	3,000,000.00	36,189.70		3,999,000.00	0.78%	21,339.50	57,520.90
9	4,000,000.00	57,528.70		4,999,000.00	0.73%	17,652.70	75,173.60
10	4,500,000.00	61,428.70
11	5,000,000.00	75,180.90
12	5,200,000.00	76,640.90
Sheet1

Richard

edit: Formula in B2 is

=VLOOKUP(A2,$D$3:$G$9,4)+(A2-VLOOKUP(A2,$D$3:$G$9,1))*VLOOKUP(A2,$D$3:$G$9,2)

 

[Dave Patton]

Your VLookup formula looks correct.

You may want to check your lookup table.

I used just three columns and numbers like 500,000 are actually
499,999.9999

My table shows

0 1.15% 0
500,000 0.98% 5,750.00
1,000,000 0.93% 10,650.00
2,000,000 0.88% 19,950.00
3,000,000 0.83% 28,750.00
4,000,000 0.78% 37,050.00
5,000,000 0.73% 44,850.00


And formula

=VLOOKUP(A1,$E$4:$G$10,3)+(A1-VLOOKUP(A1,$E$4:$G$10,1))*VLOOKUP(A1,$E$4:$G$10,2)