Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Formula for adding correct percentage.

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to produce a conditional sell price
    dependant upont the cost price.
    E.G. A10 Could contain a cost price between 2 and 500.
    If the cost is between 2 - 20 I would like a sell price in A2 to be cost price plus 20%.
    If the cost is between 20 - 100 I would like a sell price in A2 to be cost price plus 15%.
    If the cost is between 100 - 500 I would like a sell price in A2 to be cost price plus 10%.
    Whats the formula please ?

    Thanks Ted

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Eddy

    Not tested, but try:


    =IF(A1>100,A1*0.1,IF(A1>20,A1*0.15,A1*0.2))


    You may also want to look at a "Validation list" to prevent amoumts over 500 etc See Data>Validation

    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-24 07:42 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,422
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You can do such a calculation many ways.
    First, a question. What do you want to do if cost is < 2 or > 500?

    The formula ignores lower and upper factors.
    Check the formula at the thresholds. What is the correct rate at 20, 100?


    Other excellent solutions include:
    - VLookup, Hlookup, or Lookup.
    - If

    =A10*((A10>0)*0.2-(A10>20)*0.05-(A10>100)*0.05)

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    =IF(AND(A10<=20,A10>=2),A10+(A10*0.2),IF(AND(A10<=100,A10>20),A10+(A10*0.15),IF(AND(A10<=500,A10>100),A10+(A10*0.1),"Invalid Number")))

    Tom



    [ This Message was edited by: TsTom on 2002-03-24 07:47 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,422
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    sorry

    I did not add the original amount

    =A10+A10*((A10>0)*0.2-(A10>20)*0.05-(A10>100)*0.05)

    =IF(A10>100,A10*1.1,IF(AND(A10<=100,A10>20),A10*1.15,A10*1.2))

    [ This Message was edited by: Dave Patton on 2002-03-24 07:54 ]

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ted:
    Here we go
    =IF(cost>500,"NA",IF(cost>=100,cost*110%,IF(cost>=20,cost*115%,IF(cost>=2,cost*120%,"NA"))))

    HTH
    Please post back if it works for you ... otherwise explain a little further and let us take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    GREAT !!!!!!
    Thanks all. So many different ways! They all seem to work fine.
    Thanks again for everyones efforts and time
    Regards Eddy

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh bugger, I did the same as Dave Patton, I forgot to add the original amount. Just add one to the percentage. EG:

    =IF(A1>100,A1*1.1,IF(A1>20,A1*1.15,A1*1.2))

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •