Calculation to work out service fee

Hi there,

I'm hoping someone can help me, I've reviewed several existing threads which cover this subject but I haven't managed to adapt things to my own use case. I feel like I'm not a million miles away!

I'm trying to create a single formula (Without the use of a reference table) to calculate a service fee, based on the following structure;


Spend below 2000 = 200
Spend above 2000 = 12.5%
Spend above 5000 = 10%
Spend above 10000 = 7.5%
Spend above 20000 = 5%

Unfortunately the firewall on the forum won't allow me to post my work so far in the thread, as it's flagging as HTML code.

Any help would be greatly appreciated!

Originally Posted by etaf

so you are looking for code like a NESTED IF

Are these amounts accumulated
so if they spent, 5000

then that would be 200 + (2000-200)*0.125 + (5000-2000)*0.1

Spend below 2000 = 200
Spend above 2000 = 12.5%
Spend above 5000 = 10%

IF i put 2000 into the spreadsheet you supplied I get FEE = 0
2001 , i get -0.125

Hi there etaf,

Thanks for your response.

To clarify, the fee is charged based on advertising spend, and each 'segment' of spend is charged at its respective percentage.

So for example, for 10000 in spend.

0 to 5000 (5000) is charged at 12.5% = 625
5000 to 10000 (5000) is charged at 10% = 500

Total fee = 1125

I am looking for this to be expressed as a sumproduct, or some other formula which can be contained within a single cell!

Here is another thread which contains a similar challenge

excelforum.com/excel-formulas-and-functions

/1063101-formula-for-tiered-pricing.html

Thanks again for your help

Hi there Alan,

Thanks for your response.

Apologies my explanation wasn't the clearest, i'm hoping to have this expressed in a single cell as a sumproduct or something similar.

SUMPRODUCT(--(F2>{2000;5000;10000;20000}), (F2-{2000;5000;10000;20000}), {-0.125;-0.025;-0.025;-0.025}))

That is my best attempt so far!

Thanks

Hi etaf,

Yes apologies, one thing I neglected to mention was that if spend is above 2000, then the 200 fee is replaced with the 12.5% (so it's 12.5% of the full 0 - 5000, rather than 200 for the first 2000 then 12.5% for the remaining 3000).

I think I see how your suggestion works, i'll give that a go!

Thanks again

=if($f$2<=2000,200,sumproduct(--($f$2>$a$2:$a$5),((f2)-$a$2:$a$5),$c$2:$c$5))

Originally Posted by JohnTopley

=if($f$2<=2000,200,sumproduct(--($f$2>$a$2:$a$5),((f2)-$a$2:$a$5),$c$2:$c$5))

Hi John,

Thanks for your help - I was hoping to contain this within' a single cell using a legerdemain (as mentioned in the McGimpsey & Associates tutorial)

This is one of my other attempts so far

SUMPRODUCT(--(J13>{0,2000,5000,10000,20000}),--(J13-{0,2000,5000,10000,20000}),{0,0.125,-0.025,-0.025,-0.025})

This doesn't work though, I think I'm getting something wrong with the decreasing % differentials.

Thanks again!

This is because you are not accounting for 2000 rule: that is why you need the IF statement and ignore the "2000" in the above formula.

You should able to extrapolate from the SUMPRODUCT I provided to your desired format.

Hey John,

Ah yes sorry, I was ignoring that part as the if part seems to work fine - It's the sumproduct I seem to be struggling with I think;

I tried to convert the version you suggested into using legerdemain but I'm still outputting a minus number!

IF(L2<=2000,200,SUMPRODUCT(--(L2>{0,5000,10000,20000}),L2-{0,5000,10000,20000},{0,-0.125,-0.025,-0.025}))

I have attached the worksheet again with my most recent attempt.


Any thoughts much appreciated, as is probably apparent.. I'm way out of my depth here!

try ..

Formula:

Please Login or Register  to view this content.

Oh wow I think you've cracked it!

Amazing, thank you so much for your help.

If you wouldn't mind, what was I doing wrong with my version? I can see a wildcard added in your version there!

It is not a "wildcard" - it is a multiply sign. Compare your posted formula with mine and you see the values are wrong and there are missing brackets


IF(L2<=2000,200,SUMPRODUCT(--(L2>{0,5000,10000,20000})*(L2-{0,5000,10000,20000}),({0.125,-0.025,-0.025,-0.025}))