Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    IF Error (Excel 2002)

    Hi

    I would appreciate any help here my formula creates an error and I can't find it

    =IF(B7="",IF(OR(E7<>0,G7<>0,J7<>0),"ITEM CODE MISSING",""),IF(AND(E7>0,G7=0,J7=0),"PRICE INFO MISSING",IF(E7=0,"VOLUMES MISSING","OK",IF(I7="xx","OK")))))

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF Error (Excel 2002)

    In the part

    IF(E7=0,"VOLUMES MISSING","OK",IF(I7="xx","OK"))

    the first IF has 4 arguments instead of 3. Perhaps you should take out the first "OK" plus comma:

    =IF(B7="",IF(OR(E7<>0,G7<>0,J7<>0),"ITEM CODE MISSING",""),IF(AND(E7>0,G7=0,J7=0),"PRICE INFO MISSING",IF(E7=0,"VOLUMES MISSING",IF(I7="xx","OK"))))

    but I don't know if that will do what you want.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: IF Error (Excel 2002)

    Screenshot moved to zip attachment by HansV because it was much too wide (caused horizontal scrolling)

    Hi Hans

    Thanks for the reply

    Unfortunatley it did not work, I don't know if this will help you may be able to see what I am trying to achieve,


    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF Error (Excel 2002)

    No. Can you explain in words what you want to accomplish?

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: IF Error (Excel 2002)

    Hi Hans

    Here we go

    If cell B7=is empty then nothing,if(E7 is greater or less than 0 or G7 is greater of less than 0 or J7 is greater or less than 0 then "Item code is missing"),if(E7 is greater than 0 and G7=0 and J7=0 then "Price info missing"),if(E7=0 then "Volumes Missing")if(I7="XX" then "OK"

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IF Error (Excel 2002)

    Is this it:
    =IF(B7="","",IF(OR(E7<>0,G7<>0,J7<>0),"ITEM CODE MISSING",IF(AND(E7>0,G7=0,J7=0),"PRICE INFO MISSING",IF(E7=0,"VOLUMES MISSING",IF(I7="xx","OK")))))

    Steve
    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17><small>The "<>0" is usually described as "not equal to 0" and not "greater or less than 0"

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: IF Error (Excel 2002)

    Hi Steve

    It still does not work, I think I will have to give up on this one, thanks for the whisper, it's noted.

    This works,
    =IF(B7="",IF(OR(E7<>0,G7<>0,J7<>0),"ITEM CODE MISSING",""),IF(AND(E8>0,G8=0,J8=0),"PRICE INFO MISSING",IF(E8=0,"VOLUMES MISSING","OK")))

    it's only when I tried to add IF(I7="XX","OK") the problem occurs


    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Error (Excel 2002)

    Hi Braddy

    The formula I came up with is similar to Steves:
    =IF(ISBLANK(B7),"",IF(OR(E7<>0,G7<>0,J7<>0),"Item code is missing",IF(AND(E7>0,G7=0,J7=0),"Price Info Missing",IF(E7=0,"Volumes missing",IF(I7="xx","ok")))))

    The problem is, I don't think you have the underlying logic right. What you're saying is:

    1. <LI>If B7 is empty, then N7 should also appear blank.
      <LI>If (B7 is non-empty) and (ANY of E7, G7 and J7 contain non-zero values), then N7 displays "Item code is missing"
      <LI>If (B7 is non-empty) and (E7, G7 and J7 all equal zero) and (E7 is greater than zero, and G7 and J7 both equal zero) then N7 displays "Price Info Missing"
      <LI>If (B7 is non-empty) and (E7, G7 and J7 all equal zero) and (E7 equals zero) then N7 displays "Volumes missing"
      <LI>If (B7 is non-empty) and (E7, G7 and J7 all equal zero) and (E7 is less than zero) and (I7 is "xx") then N7 displays "OK"
    As you can see, there are some impossible pieces of logic in there. Lines 3 and 5, for example, will never occur since they require E7 to be both zero and non-zero at the same time. In line 4, you don't need to repeat the condition that E7 is zero. Lines 1, 2 and 4 are the only bits which actually make sense, and so the formula for this could be simplified to:

    =IF(ISBLANK(B7),"",IF(OR(E7<>0,G7<>0,J7<>0),"Item code is missing","Volumes missing"))

    I don't think this will do what you want it to, but it's what you asked for!
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: IF Error (Excel 2002)

    Hi Dave

    I understand what you are saying, so I will give up on this one and try another way round the problem.

    Thank you for your reply

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IF Error (Excel 2002)

    Maybe if you explained the complete logic we could make a formula from scratch.

    Steve

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: IF Error (Excel 2002)

    Hi Steve

    I thank you for your offer, but I think I have found another way to achieve my goal.

    Many thanks to all who replied.

    Braddy

    NB What I was trying to achieve was to stop a discount being applied to a cell if a anothe cell contained KK, I got round this by adjusting to table to read the list price and the discounted price being the same.
    If you are a fool at forty, you will always be a fool

  12. #12
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Error (Excel 2002)

    Have you thought about making a VLOOKUP table? I've found they are sometimes easier to deal with than trying to stretch an if...then statement to fit every possibility. If you can figure out exactly what you want, you can even nest these several layers deep, giving you a large number of possibilities.

Posting Permissions

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