locked
Excel Help for If Statements- Percentage ranges RRS feed

  • Question

  • Im having some difficulty getting this formula to work.

    My desire is to have a formula that determines a value based on the percentage and percentage range.  The values to be assigned are 0,1,2.  The completion % less than or equal to 94.98 should be assigned a value of 0.  The completion % with the range of 94.99-97.98 should be assigned a value of 1.  The completion % above 97.99 should be assigned a value of 2.

    Here is the formula I have enter into column E.  =IF(D8<94.98%,0,IF(D8>=94.99,1,IF (D8<97.99,1,IF(D8>=97.99,2)))) This is working for the first couple of parameters ( values below 94.88 return a value of 0, and all values above 94.99 return a value of 1) but not the last parameter of a completion percentage above 97.99.  All values above 97.99 still return 1 as their value.

    I also tried this amended formula: =IF(D8<94.98%,0,IF (D8>=94.99%,1,IF (D8>97.99%,2))) The does the same thing as the formula above; it wont calculate the value for the percentage above 97.99. Those values still return as 1.

    Data is below:

    Please help.

    column D                                 Column E
    Completion %                         value of 0,1,2

    95.19                                        1

    Thursday, January 4, 2018 12:29 PM

Answers

  • I'd ask for help over here.

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    • Proposed as answer by Just Karl Thursday, January 4, 2018 3:08 PM
    • Marked as answer by Richard MuellerMVP Thursday, January 11, 2018 1:49 PM
    Thursday, January 4, 2018 1:17 PM
  • Hi,

    I'm afraid your description does not satify all conditions. They should be:
      0: <=94.98 ----------- Less Equal
      1: 94.99-97.98  ------ this range (Greater Equal - Less Equal)
      2: >= 97.99 ---------- Greater Equal

    Formula in column E should be:
    =IF(D11<=94.98,0,IF(D11>=97.99,2,1))
       
    If I misunderstand what you want to do, please explain more.

    Regards,

    Ashidacchi

    • Proposed as answer by WendellHill Friday, January 5, 2018 12:40 PM
    • Unproposed as answer by WendellHill Friday, January 5, 2018 12:40 PM
    • Proposed as answer by Ashidacchi Friday, January 5, 2018 12:51 PM
    • Marked as answer by Richard MuellerMVP Thursday, January 11, 2018 1:50 PM
    Thursday, January 4, 2018 11:56 PM

All replies

  • I'd ask for help over here.

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    • Proposed as answer by Just Karl Thursday, January 4, 2018 3:08 PM
    • Marked as answer by Richard MuellerMVP Thursday, January 11, 2018 1:49 PM
    Thursday, January 4, 2018 1:17 PM
  • Hi,

    I'm afraid your description does not satify all conditions. They should be:
      0: <=94.98 ----------- Less Equal
      1: 94.99-97.98  ------ this range (Greater Equal - Less Equal)
      2: >= 97.99 ---------- Greater Equal

    Formula in column E should be:
    =IF(D11<=94.98,0,IF(D11>=97.99,2,1))
       
    If I misunderstand what you want to do, please explain more.

    Regards,

    Ashidacchi

    • Proposed as answer by WendellHill Friday, January 5, 2018 12:40 PM
    • Unproposed as answer by WendellHill Friday, January 5, 2018 12:40 PM
    • Proposed as answer by Ashidacchi Friday, January 5, 2018 12:51 PM
    • Marked as answer by Richard MuellerMVP Thursday, January 11, 2018 1:50 PM
    Thursday, January 4, 2018 11:56 PM
  • That worked perfectly!

    Thanks so much for the help!

    Friday, January 5, 2018 12:41 PM
  • You want different values if you put 94.98% its show zoro but the third one 97.99% is not working right
    Friday, January 5, 2018 12:42 PM
  • Hi SurePest,

    Please insert many / various values in column D, and check if the function in column E works as you expect.
    (someone seems to disagree with my suggestion, without providing any solution)

    If you are satisfied with my formula, please mark my post as an answer. That will be helpful for those who will search for answer.

    Regards,  

    Ashidacchi

    • Edited by Ashidacchi Friday, January 5, 2018 1:00 PM
    Friday, January 5, 2018 12:57 PM