+ Reply to Thread
Results 1 to 4 of 4

How do I create a formula for multiple levels of discounts?

  1. #1
    bearkm02
    Guest

    How do I create a formula for multiple levels of discounts?

    I am trying to create a pricing sheet that will include a cell for discount
    percentages. The criteria are: if material in square inches (cell D18) is
    between 288 and 288 square inches, then a disount of 10% is subtracted from
    the subtotal. If material in cell D18 is greater than 488 square inches,
    then a disount of 15% is subtracted from the subtotal. I am a novice at
    this, so I need some help from the experts.

  2. #2
    Peo Sjoblom
    Guest

    Re: How do I create a formula for multiple levels of discounts?


    I am assuming you made a typo and that you want

    less than 288 no discounts
    greater than or equal to 288 AND less than 488 10% discount
    greater than or equal to 488 15%

    I assume that subtotal amount is in E18

    then something like this

    =E18*(1-IF(D18="",0,LOOKUP(D18,{0;288;488},{0;0.1;0.15})))


    --

    Regards,

    Peo Sjoblom

    Northwest Excel Solutions

    www.nwexcelsolutions.com

    (remove ^^ from email address)

    Portland, Oregon

    "bearkm02" <[email protected]> wrote in message
    news:[email protected]...
    >I am trying to create a pricing sheet that will include a cell for discount
    > percentages. The criteria are: if material in square inches (cell D18) is
    > between 288 and 288 square inches, then a disount of 10% is subtracted
    > from
    > the subtotal. If material in cell D18 is greater than 488 square inches,
    > then a disount of 15% is subtracted from the subtotal. I am a novice at
    > this, so I need some help from the experts.



  3. #3
    bpeltzer
    Guest

    RE: How do I create a formula for multiple levels of discounts?

    The discount % can be calculated as =if(d2>=488,15%,if(d2>=288,10%,0))
    (you may want to adjust the >= inequalities to >, dpending on precisely
    where you want the discount to kick in). BTW, this assumes that the
    discount, if applicable, applies back to the first square inch.
    --Bruce

    "bearkm02" wrote:

    > I am trying to create a pricing sheet that will include a cell for discount
    > percentages. The criteria are: if material in square inches (cell D18) is
    > between 288 and 288 square inches, then a disount of 10% is subtracted from
    > the subtotal. If material in cell D18 is greater than 488 square inches,
    > then a disount of 15% is subtracted from the subtotal. I am a novice at
    > this, so I need some help from the experts.


  4. #4
    Niek Otten
    Guest

    Re: How do I create a formula for multiple levels of discounts?

    <this assumes that the discount, if applicable, applies back to the first square inch.>

    But if this is not the case, look here:

    http://www.mcgimpsey.com/excel/variablerate.html


    --
    Kind regards,

    Niek Otten

    "bpeltzer" <[email protected]> wrote in message news:[email protected]...
    > The discount % can be calculated as =if(d2>=488,15%,if(d2>=288,10%,0))
    > (you may want to adjust the >= inequalities to >, dpending on precisely
    > where you want the discount to kick in). BTW, this assumes that the
    > discount, if applicable, applies back to the first square inch.
    > --Bruce
    >
    > "bearkm02" wrote:
    >
    >> I am trying to create a pricing sheet that will include a cell for discount
    >> percentages. The criteria are: if material in square inches (cell D18) is
    >> between 288 and 288 square inches, then a disount of 10% is subtracted from
    >> the subtotal. If material in cell D18 is greater than 488 square inches,
    >> then a disount of 15% is subtracted from the subtotal. I am a novice at
    >> this, so I need some help from the experts.




+ 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