This is really a maths question... calc a % fee to charge based on a number that is only found when you take away the fee... :/

[momentumons]

Hello! Can you help?

I have a fee table that is a sliding scale like the below:

IF X is between:		Fee applied
0	$20	$2
$21	$50	2%
$51	$100	1%
$100	+	0.5%

The fee is a sliding scale based on "X". But X can only be found once the fee has been taken out.

So if my total project budget is $81 (this is X + fee), how do I write up a formula to work out what X & the fee are so it will auto update every time I change the total.

Does that make sense?

So my formula needs to return:

Total (Incl Fees)	$ 81.00
Fees	?
X	?

 

[RobP]

Hi,
I'll let you test it, as its probably not perfect, but it might get you close ..:

IF(C4-C4*0.005>100,C4*0.005,IF(C4-C4*0.01>50,C4*0.01,IF(C4-C4*0.02>19.6,C4*0.02,2)))

The issue in your range is that you have dead spots (ie. what if X is 20.5 ? or 100.75 ?) so I've generalised a bit.

I've also assumed if X>20, rather than 21 etc..

cheers
Rob

 

[Dave Patton]

Can you give some sample results?

Consider the following

T202209a.xlsm
	A	B	C
6	Total	X	Fee
7	22	20.00	2.00
8	81	79.80	1.20
9	25	24.51	0.49
10	60	59.11	0.89
11	120	118.81	1.19
12
1a

Cell Formulas
Range	Formula
B7:B11	B7	=IF(A7<=22,A7-2,A7/(1+LOOKUP(A7,{22.01;50.762;101.01},{0.02;0.015;0.01})))
C8:C11	C8	=A8-B8

 

[RobP]

Sure, I was within 0.01 with my original formula (as I knew it wasn't perfect), except for the <20 which was an error on my part.

So I've tweaked it to use this new formula : (assuming C4 was my total).

=IF(C4/1.005>100,C4-C4/1.005,IF(C4/1.01>50,C4-C4/1.01,IF(C4/1.02>21.57,C4-C4/1.02,2)))

To give you these results: Let me know if its suitable for you.

zombies.xlsx
	C	D	E	F
3	Total		X	Fee
4	22		20.00	2.00
5	81		80.20	0.80
6	25		24.51	0.49
7	60		59.41	0.59
8	120		119.40	0.60
Sheet2

Cell Formulas
Range	Formula
E4:E8	E4	=+C4-F4
F4:F8	F4	=IF(C4/1.005>100,C4-C4/1.005,IF(C4/1.01>50,C4-C4/1.01,IF(C4/1.02>21.57,C4-C4/1.02,2)))

 

[momentumons]

Thank you! I so appreciate your brain power on this. I will play around with this.. but I can also be more helpful with my instructions.

The issue is - my sliding sale fees are to be applied to the project budget ("X"). This was working fine with a normal IFAND formula. However, now I get TOTAL budgets that are to include GST and fees... so I need to start with that total, take off GST AND take off fees to determine the project budget... :/

So if my total budget is $100 - GST = $90. How do i then work out what the fees are and what the project budget is from that $90...

The total budget could be any number under the sun so can't really do a look up table.

See below. The number in green will change every project.... what formulas need to replace the ?'s to get fees and project budget?

 

[RobP]

you just put my formula in your cell B14, and then change my references from C4 to B13 ?

Rob

 

[momentumons]

Ah yep! You got it @RobP !! I just couldn't wrap my head around that but that's it! I've played around with it and it's working! Thanks so much

 

[RobP]

great, thanks for the feedback.