+ Reply to Thread
Results 1 to 26 of 26

Incremental Volume Pricing calculation

  1. #1
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Incremental Volume Pricing calculation

    Hi

    I'm working on a pricing model for software licensing. We have the following licensing tiers:

    Anything upto 500 licenses is $1000

    501-5000 is $2 per license
    5001-10000 is $1.50
    10001-15000 is $1.00
    15001+ is $0.50

    What I need is when the user inputs the number of requested licenses the results should output the cost of licenses in each tier. Please see attached sheet.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Hi, is the attached what you had in mind.

    It uses formulae like 17
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    Hi

    Thanks for replying. It's not what I had in mind - my fault I should have been more specific in my original request.

    I'll give a couple of examples which may clarify what I'm trying to get to.

    Example 1.

    If a user inputs any number below 500 then in Tier 1 it would display $1000 as that cost is fixed.

    Example 2

    If however a user inputs 600 licenses then Tier 1 shows $1000 and tier 2 shows 400 (100*4) as that's the incremental number of license over 500 and on the enterprise package is $4per user between 501-5000

    and so on
    Last edited by vinaytanna; 11-30-2015 at 08:56 AM.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Please clarify where you expect the results. i.e. where is Tier1.
    If you enter 500 in B13 you see the answer £1000 in B16. Is this not what you want?

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    ....sorry, ignore last post. Back shortly.

  6. #6
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    Thanks for your help

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,006

    Re: Incremental Volume Pricing calculation

    See attached ...


    Formulae as per below


    =IF($B$13>$A16,MIN($B$13-$A16,4500)*VLOOKUP($A16+1,$A$5:$E$8,IF($B$11="Enterprise",4,2),1),0)
    Attached Files Attached Files

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Is the attached more in line?
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Incremental Volume Pricing calculation

    Try this
    If C1 is total licences
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    Thanks. This works perfectly.

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Quote Originally Posted by vinaytanna View Post
    Thanks. This works perfectly.
    Not sure to whom that was addressed. However it seems that one way or another you have a solution.

    Please remember to mark the thread as solved.

  12. #12
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    I have a follow-up question. If I wanted the user to be able to choose a package from a drop down. i.e. Business or Enterprise. How do I structure it so that the correct pricing is displayed based on their package selection and required number of users?

  13. #13
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    Quote Originally Posted by Richard Buttrey View Post
    Not sure to whom that was addressed. However it seems that one way or another you have a solution.

    Please remember to mark the thread as solved.
    Apologies. It was addressed to you. Your assistance is much appreciated.

  14. #14
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Quote Originally Posted by vinaytanna View Post
    I have a follow-up question. If I wanted the user to be able to choose a package from a drop down. i.e. Business or Enterprise. How do I structure it so that the correct pricing is displayed based on their package selection and required number of users?
    Hi,

    If the question is merely how do you create a drop down, in B11 (incidentally I should have deleted C11) choose Data Validation, Then List, then enter 'Business,Enterprise' - without the quotes.

  15. #15
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    Great. Thanks.

    One final question. If I want to show the Tiers in column A - i.e 501-5000 (as per the orginal) - can I do that? It makes it easier for the audience to understand.

  16. #16
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Not quite sure what you mean. If you mean replacing text like "Tier 1 (1-500)" with 1-500 then the answer is yes. None of the formulae are dependent on column A. However let me know if this is not what you mean.

  17. #17
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    Actually I've worked out how to do the above. Was relatively straight forward. I have noticed an error in the formula's. I am using the 1st workbook you uploaded "licensing tiers".

    If you enter number of users as 15000 (on Enterprise) - you get the following results

    Up to …. Monthly
    500 $1,000.00 - is correct
    5000 $18,000.00 - is correct = 4500*4
    10000 $15,750.00 is incorrect should be 5000*3.5=17500. Instead it is doing 4500*3.5
    15000 $13,500.00 is incorrect should be 5000*3 = 15000 . Intead it is doing 4500*3
    15001 $-

  18. #18
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    I have edited the formula

    =IF($B$13>$A17,MIN($B$13-$A17,4500)*VLOOKUP($A17+1,$A$5:$E$8,IF($B$11="Enterprise",4,2),1),0)

    to

    =IF($B$13>$A17,MIN($B$13-$A17,5000)*VLOOKUP($A17+1,$A$5:$E$8,IF($B$11="Enterprise",4,2),1),0)

    in the 10000 and 15000 tiers, it seems to work.

  19. #19
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Hi,

    I don't recognise those numbers. With 15000 in B13 then
    B16 0-500 : 1000
    B17 501-5000 : 19000
    B18 5001-10000 : 35000
    B19 10001-15000 : 15000

    Would,you clarify and if necessary upload the workbook which is giving you the numbers you mention

  20. #20
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Quote Originally Posted by vinaytanna View Post
    Actually I've worked out how to do the above. Was relatively straight forward. I have noticed an error in the formula's. I am using the 1st workbook you uploaded "licensing tiers".

    To whom was that addressed?

  21. #21
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    Hi Richard, the message was for you.

    I am using this workbook

    http://www.excelforum.com/excel-form...lculation.html
    Last edited by vinaytanna; 11-30-2015 at 12:25 PM. Reason: Incomplete message

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,006

    Re: Incremental Volume Pricing calculation

    Richard,
    The formula is mine!!!! I had typos of 4500 instead of 5000.

    Updated version attached.

    John
    Attached Files Attached Files

  23. #23
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Hello John,

    ...in view of #21 clearly some crossed wires somewhere.....

  24. #24
    Registered User
    Join Date
    11-27-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: Incremental Volume Pricing calculation

    Sorry, got posters mixed up. Thanks John and Richard.

    I'll mark this as resolved.

  25. #25
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Incremental Volume Pricing calculation

    Great - and thanks.

  26. #26
    Registered User
    Join Date
    06-08-2019
    Location
    India
    MS-Off Ver
    2007
    Posts
    1

    Re: Incremental Volume Pricing calculation

    This was really helpful. Thanks

+ 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. Calculation of incremental pricing
    By komododragon in forum Excel General
    Replies: 4
    Last Post: 04-29-2016, 12:37 PM
  2. [SOLVED] Index/Match Multiple Criteria: Item # & Volume Level Pricing Relative to Specific Volume
    By dilbert1865 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-15-2014, 09:28 AM
  3. Volume and pricing calculations
    By Karen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 03:05 AM
  4. Volume and pricing calculations
    By Karen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 02:05 AM
  5. Volume and pricing calculations
    By Karen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 01:05 AM
  6. [SOLVED] Volume and pricing calculations
    By Karen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2005, 11:05 PM
  7. Volume and pricing calculations
    By Karen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2005, 10:05 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