+ Reply to Thread
Results 1 to 15 of 15

Calculation to work out service fee

  1. #1
    Registered User
    Join Date
    07-01-2021
    Location
    Manchester, England
    MS-Off Ver
    18.2104.12721.0
    Posts
    9

    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%
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-01-2021
    Location
    Manchester, England
    MS-Off Ver
    18.2104.12721.0
    Posts
    9

    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!

  3. #3
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: Calculation to work out service fee

    Try this:


    =IF(F2 < 2000,200,VLOOKUP(F2,$A$2:$B$6,2,TRUE)*F2)
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    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
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Registered User
    Join Date
    07-01-2021
    Location
    Manchester, England
    MS-Off Ver
    18.2104.12721.0
    Posts
    9

    Re: Calculation to work out service fee

    Quote Originally Posted by etaf View Post
    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

  6. #6
    Registered User
    Join Date
    07-01-2021
    Location
    Manchester, England
    MS-Off Ver
    18.2104.12721.0
    Posts
    9

    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

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    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"))

  8. #8
    Registered User
    Join Date
    07-01-2021
    Location
    Manchester, England
    MS-Off Ver
    18.2104.12721.0
    Posts
    9

    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

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,865

    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))
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-01-2021
    Location
    Manchester, England
    MS-Off Ver
    18.2104.12721.0
    Posts
    9

    Re: Calculation to work out service fee

    Quote Originally Posted by JohnTopley View Post
    =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!

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,865

    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.

  12. #12
    Registered User
    Join Date
    07-01-2021
    Location
    Manchester, England
    MS-Off Ver
    18.2104.12721.0
    Posts
    9

    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!
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,865

    Re: Calculation to work out service fee

    try ..

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    07-01-2021
    Location
    Manchester, England
    MS-Off Ver
    18.2104.12721.0
    Posts
    9

    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!

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,865

    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}))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calling sap web service via kerberos does not work
    By tavi3006 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-18-2019, 05:01 AM
  2. Long Service Calculation
    By SuziBaz in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-31-2018, 07:33 AM
  3. Different Years service working out redundancy calculation
    By Webbn111 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-26-2014, 12:44 PM
  4. [SOLVED] Years of Service for Benefit Calculation
    By monid78 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-06-2013, 11:20 AM
  5. Macro for calculation of Service Cost
    By excelhelp18 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2010, 02:43 AM
  6. Years of Service Calculation
    By JS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-24-2006, 02:55 PM
  7. [SOLVED] Service work order
    By schoolgeek63 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-07-2005, 05:06 PM

Bookmarks

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