Results 1 to 5 of 5

Thread: If Formula Error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If Formula Error

    Hi All,

    I am entering the below formula but it says missing parenthesis

    =IF(AND(C10>0%,C10<80%),0,IF(AND(C10>=80%,C10<=100%),C7*C10,IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%,IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5*120%)*2%

    Can any one help

    C5 is Domain Target
    C7 is Achievement


    Range is below
    Range Incentive %
    0-80% Nil
    80-100% 0.75%
    100 -120 % 1.50%
    120 & above% 2.00%
    Last edited by Mangesharalkar; Jan 11th, 2019 at 05:51 AM.

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Formula Error

    Quote Originally Posted by Mangesharalkar View Post
    Hi All,

    I am entering the below formula but it says missing parenthesis

    =IF(AND(C10>0%,C10<80%),0,IF(AND(C10>=80%,C10<=100%),C7*C10,IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%,IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5*120%)*2%

    Can any one help

    C5 is Domain Target
    C7 is Achievement


    Range is below
    Range Incentive %
    0-80% Nil
    80-100% 0.75%
    100 -120 % 1.50%
    120 & above% 2.00%

    Hey,

    You need to close your IFS:

    =IF(AND(C10>0%,C10<80%),0,IF(AND(C10>=80%,C10<=100%),C7*C10,IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%,IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5*120%)*2%))))))

  3. #3
    Board Regular
    Join Date
    Jan 2014
    Location
    Dublin, Ireland
    Posts
    1,421
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Formula Error

    Brackets also need to be closed inside the formula not just on the end.

    =IF(AND(C10>0%,C10<80%),0,IF(AND(C10>=80%,C10<=100%),C7*C10,IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%),IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5*120%)*2%),FALSE))))
    Learn something new everyday.

    be sure to use code tags

    Code:
    [ code ]
    [ / code ]
    ' no spaces

  4. #4
    Board Regular
    Join Date
    Jul 2014
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Formula Error

    BarryL you are correct. Escaped my old tired brain.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,863
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: If Formula Error

    You may find it easier to spot problems by splitting the formula onto separate lines in the cell with {Alt}{Enter}
    - Excel's colour coding help match opening and closing parentheses on each line
    - count opening IF parentheses and match with closing ones

    =IF(AND(C10>0%,C10<80%),0,
    IF(AND(C10>=80%,C10<=100%),C7*C10,
    IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%),
    IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5)*120%*2%),"something missing here"))))
    Last edited by Yongle; Jan 11th, 2019 at 06:12 AM.

Some videos you may like

User Tag List

Tags for this Thread

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
  •