+ Reply to Thread
Results 1 to 12 of 12

If statement

  1. #1
    Registered User
    Join Date
    01-17-2008
    Posts
    9

    If statement

    "I Have A Value And Depending On The Size Of That Value I Wish To Add A%. Eg If That Value = 50 Add 10%
    If That Value Exceeds 50 But Does Not Exceed 100 Add 15%
    If That Value Exceeds 100 But Does Not Exceed 150 Add 20%
    If That Value Exceed 150 Add 25%."


    Can anyone please provide the correct formula for me.
    Thank you.
    Buster Bill
    Last edited by VBA Noob; 01-17-2008 at 04:14 AM.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe

    Please Login or Register  to view this content.
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Bill,

    Please keep posts in thread (see forum rules below)

    See if example helps

    VBA Noob
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-17-2008
    Posts
    9

    formulas

    I have tried that formula and unfortunately it failed. Anyone else who may have some ideas would be appreciated.


    Thanks very much.

    Buster Bill

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    I have tried that formula and unfortunately it failed
    Why did it fail ??

    VBA Noob

  6. #6
    Registered User
    Join Date
    07-16-2007
    Posts
    43
    You can try the following:

    =if(a1<=50,a1*1.10,if(a1<=100,a1*1.15,if(a1<=150,a1*1.2,a1*1.25)))

    This checks if the number is lesser than or equal to 50.
    If it is, it multiplies by 1.10 (adds 10%).
    Otherwise (number is greater than 50), it checks if the number is lesser than or equal to 100.
    If it is, it multiplies by 1.15 (adds 15%).
    Otherwise (number is greater than 100), it checks if the number is lesser than or equal to 150.
    If it is, it multiplies by 1.20 (adds 20%).
    Otherwise (number is greater than 150), it multiplies by 1.25 (adds 25%).

    Excel does have a limit on the number of nested IFs it can handle, so if you have several more possible ranges than you do in your example, this may not work for you.

    Brigitte

  7. #7
    Registered User
    Join Date
    01-17-2008
    Posts
    9
    [QUOTE=VBA Noob]Why did it fail ??
    It may well have been my mistake not yours and my description was misleading, for which I apologize. Please let me attempt to clarify more clearly.I am attempting to establish a reasonable price to pay for a product that varies considerably over time. Experience has proved that by taking the lowest price paid + a percentage based on the volatility of the product works reasonably well. However product with a high volatility throws my calculation well out and I require a wider scope.My current formula considers a volatility only below & above 50%. If possible I want to extend that to show below 50% over 50% to 100% over 100% to 150% and over 150%. My current spread sheet is as attached.I sincerely hope this attachment accompanies message I am not very good at this!

    BUSTER BILL.

  8. #8
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    No attachment

    Zip the file first by right clicking and sending to zip

    Then click on the paperclip symbol to attach

    VBA Noob

  9. #9
    Registered User
    Join Date
    01-17-2008
    Posts
    9
    Quote Originally Posted by VBA Noob
    No attachment

    Zip the file first by right clicking and sending to zip

    Then click on the paperclip symbol to attach

    VBA Noob
    Hope this is satisfactory
    Regards
    BUSTER BILL
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-17-2008
    Posts
    9
    Quote Originally Posted by Brigitte_P
    You can try the following:

    =if(a1<=50,a1*1.10,if(a1<=100,a1*1.15,if(a1<=150,a1*1.2,a1*1.25)))

    This checks if the number is lesser than or equal to 50.
    If it is, it multiplies by 1.10 (adds 10%).
    Otherwise (number is greater than 50), it checks if the number is lesser than or equal to 100.
    If it is, it multiplies by 1.15 (adds 15%).
    Otherwise (number is greater than 100), it checks if the number is lesser than or equal to 150.
    If it is, it multiplies by 1.20 (adds 20%).
    Otherwise (number is greater than 150), it multiplies by 1.25 (adds 25%).

    Excel does have a limit on the number of nested IFs it can handle, so if you have several more possible ranges than you do in your example, this may not work for you.

    Brigitte
    Thank you very much for your advice.It works perfectly for any amount below 50. However it does not seem to calculate accurately when the amount exceeds 50. This is possibly my fault. I will attempt to send with this message a sample of my spread sheet, "I'm no computer expert" but please bare with me. The formula provides a price range high and low over a given period. It also calculates the volatility. Based on the volatility range it must add a % to the lower end of the price range.
    Again, thank you for your patience.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-16-2007
    Posts
    43

    Another try

    Try the following formula in cell B2 of your Example workbook.

    =IF(E2<=0.5,0.1,IF(E2<=1,0.15,IF(E2<=1.5,0.2,0.25)))

    This formula will make the percentage in B2 follow the rules you posted at the beginning of this thread, based on the volatility.

    Let me know if that works!

    Brigitte

  12. #12
    Registered User
    Join Date
    01-17-2008
    Posts
    9

    Cool If Formula

    Quote Originally Posted by Brigitte_P
    Try the following formula in cell B2 of your Example workbook.

    =IF(E2<=0.5,0.1,IF(E2<=1,0.15,IF(E2<=1.5,0.2,0.25)))

    This formula will make the percentage in B2 follow the rules you posted at the beginning of this thread, based on the volatility.

    Let me know if that works!

    Brigitte
    Brigitte you are a star. Thank you it works beautifully.
    Regards

    Buster Bill

+ 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