+ Reply to Thread
Results 1 to 3 of 3

Price range function

  1. #1
    Registered User
    Join Date
    12-10-2015
    Location
    Latvia,Riga
    MS-Off Ver
    2016
    Posts
    2

    Price range function

    Hello, Im creating price table for my exam in transport logistics.
    For now the formula is as follows:
    =IF(I3*$R$16<$R$15,$R$15,I3*LOOKUP(I3,$Q$17:$R$20))
    Where "I3" is the amount needed to send

    and Q15:R20 is the price list

    Minimal order sum is 110 (R15)
    Nominal price is 19 (R16)
    Price if amount is in range from 45 to 100 is 17 (R17)
    100 to 200 is 15 (R18)
    200 to 300 is 13 (R19)
    and above 300 is 11 (R20)

    My problem is, for example, if the amount is 195 it costs 2925€, in which case i would like to use next range's minimal cost which is 2600€.
    How do i implement this is my formula ?
    I really need help.
    Thanks in advance, Alex

  2. #2
    Registered User
    Join Date
    12-10-2015
    Location
    Latvia,Riga
    MS-Off Ver
    2016
    Posts
    2

    Re: Price range function

    excel.JPG
    This is how the table looks like

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Price range function

    I think that I understand the logic. As in if the amount ordered multiplied by the cost per unit comes out to be more than the minimum at the next order level, then increase the order amount. If you would "Go Advanced" and upload the actual .xlsx file instead of just a picture of I imagine that someone can give you a solution.
    Last edited by JeteMc; 12-10-2015 at 01:07 PM.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 8
    Last Post: 05-24-2014, 08:56 PM
  2. [SOLVED] Median with criteria, price range and date range
    By Bravmik in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2014, 01:11 AM
  3. Replies: 1
    Last Post: 07-24-2013, 12:13 PM
  4. Replies: 3
    Last Post: 01-03-2013, 12:32 AM
  5. Replies: 4
    Last Post: 08-15-2012, 09:49 AM
  6. Replies: 8
    Last Post: 10-26-2010, 10:00 AM
  7. Calculating a Price Range
    By Jim C in forum Excel General
    Replies: 3
    Last Post: 03-11-2008, 01:42 PM

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