# Calculation to work out service fee

1. ## 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%  Register To Reply

2. ## Re: Calculation to work out service fee

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!  Register To Reply

3. ## Re: Calculation to work out service fee

Try this:

=IF(F2 < 2000,200,VLOOKUP(F2,\$A\$2:\$B\$6,2,TRUE)*F2)  Register To Reply

4. ## Re: Calculation to work out service fee

Without the use of a reference table
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  Register To Reply

5. ## Re: Calculation to work out service fee 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  Register To Reply

6. ## Re: Calculation to work out service fee

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  Register To Reply

7. ## Re: Calculation to work out service fee

so the rules are not as straighht forward as the table then ?

BUT a NESTED IF would do it , if you specified the EXACT Rules
Spend below 2000 = 200
Spend above 2000 = 12.5%
Spend above 5000 = 10%
Spend above 10000 = 7.5%
Spend above 20000 = 5%
IS NOT the SAME as
0 to 5000 (5000) is charged at 12.5% = 625
5000 to 10000 (5000) is charged at 10% = 500
=IF ( Cell < = 5000 , cell * 12.5% , IF( cell < = 10000 , 625+ ( cell -5000)*10% , "what ever is next in the table"))  Register To Reply

8. ## Re: Calculation to work out service fee

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  Register To Reply

9. ## Re: Calculation to work out service fee

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

10. ## Re: Calculation to work out service fee 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!  Register To Reply

11. ## Re: Calculation to work out service fee

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.  Register To Reply

12. ## Re: Calculation to work out service fee

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!  Register To Reply

13. ## Re: Calculation to work out service fee

try ..

Formula:  `Please Login or Register  to view this content.`  Register To Reply

14. ## Re: Calculation to work out service fee

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!  Register To Reply

15. ## Re: Calculation to work out service fee

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}))  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1