+ Reply to Thread
Results 1 to 2 of 2

excel multiply by different values based on quantity

  1. #1
    ronzander1
    Guest

    excel multiply by different values based on quantity

    I would like to multiply the value of a cell based on quantity.

    Think of it as paying more (higher multiplication factor) for a few pounds
    of something, and paying less for bigger quantity (lower multiplication
    factor) and then and paying far less still for a huge quantity (even lower
    multiplication factor)

    I need to have at least three variables to make my factors work. THANKS!


  2. #2
    Roger Govier
    Guest

    Re: excel multiply by different values based on quantity

    Hi

    There are many ways to tackle this problem. The following will allow you
    to create as many price breaks as you wish, without needing to amend the
    formula.
    Somewhere on a sheet set up a table in 2 columns like the following (I
    use cells E1:F4 for this example)
    0 100
    100 80
    500 70
    1000 65

    This would be charging 100 per unit for quantities up to 99, 80 per unit
    for quantities 100 to 499, 70 for quantities 500 to 999 and 65 per unit
    for any quantity above 1000
    Now if you enter the quantity in cell A1, in cell B1 enter
    =A1*VLOOKUP(A1,$E$1:$F$4,2)

    Change value and locations to suit your needs. If you need more price
    breaks, extend the table. If you made the formula
    =A1*VLOOKUP(A1,$E$1:$F$4,2) from the outset, then you would have 6 more
    rows available to have further price breaks available to you.



    --
    Regards

    Roger Govier


    "ronzander1" <ronzander1@discussions.microsoft.com> wrote in message
    news:A7F6022C-8024-46AE-AAD8-6F3ABA90F96F@microsoft.com...
    >I would like to multiply the value of a cell based on quantity.
    >
    > Think of it as paying more (higher multiplication factor) for a few
    > pounds
    > of something, and paying less for bigger quantity (lower
    > multiplication
    > factor) and then and paying far less still for a huge quantity (even
    > lower
    > multiplication factor)
    >
    > I need to have at least three variables to make my factors work.
    > THANKS!
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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