+ Reply to Thread
Results 1 to 4 of 4

automated price calculation with no. of user dependent pricing

  1. #1
    Registered User
    Join Date
    02-10-2015
    Location
    Berlin, Germany
    MS-Off Ver
    2013
    Posts
    2

    automated price calculation with no. of user dependent pricing

    Hey guys,

    so I've been trying to figure out a way to automatically get my final price calculated for a product which has a number of user dependent pricing.

    As you can see in the table the price goes down the more user rights are purchased. Yet, the next cheaper price applies only to the number of users above the break.
    This means that for example if someone has licensed:

    180 user rights, its (15 x 90€ + 35 x 80€ + 50 x 65€ x 80 x 55€) x 12 to get the yearly final price
    260 user rights, its (15 x 90€ + 35 x 80€ + 50 x 65€ x 100 x 55€ + 60 x 45) x 12 to ge the yearly final price


    SO my goal is to have two fields, one in which I enter a user number so that the second field what then tell me the yearly final price. Now my problem is (granted, Im no expert in Excel) is that I dont know which formula to use for this. I tried some variations of If clauses with the field being < or > than the "From" fields, but nothing really worked out...

    Any help is greatly appreciated!!

    Thanks,

    Dan
    [Office 2013]
    table.png

  2. #2
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: automated price calculation with no. of user dependent pricing

    the attached works with a helper column;
    Attached Files Attached Files

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: automated price calculation with no. of user dependent pricing

    With A1 is user number

    B1 is expected yearly final price:

    Please Login or Register  to view this content.
    Quang PT

  4. #4
    Registered User
    Join Date
    02-10-2015
    Location
    Berlin, Germany
    MS-Off Ver
    2013
    Posts
    2

    Re: automated price calculation with no. of user dependent pricing

    hey thanks so much for the quick replies.
    I'm using AlanY's approach for now,still figuring out bebo's

+ 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. Determining unit price from a pricing matrix...
    By pmantey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2014, 10:34 PM
  2. [SOLVED] Secondary Loan Pricing Calculation (price of loan being bought/sold)
    By Romsky in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 07:02 PM
  3. Replies: 6
    Last Post: 11-13-2009, 02:02 AM
  4. Pricing Sheet - If xx quantity, use this price
    By Hendy_100 in forum Excel General
    Replies: 3
    Last Post: 10-31-2006, 05:46 PM
  5. [SOLVED] automatic markup calculation dependent on price vale
    By Veg-man in forum Excel General
    Replies: 1
    Last Post: 01-24-2005, 09:12 AM

Tags for this Thread

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