Results 1 to 12 of 12
Thread: IF Error (Excel 2002)

20050408, 06:50 #1
 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
BraddyIf you are a fool at forty, you will always be a fool

20050408, 06:57 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20050408, 07:47 #3
 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,
BraddyIf you are a fool at forty, you will always be a fool

20050408, 07:51 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: IF Error (Excel 2002)
No. Can you explain in words what you want to accomplish?

20050408, 08:46 #5
 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"
BraddyIf you are a fool at forty, you will always be a fool

20050408, 10:26 #6
 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"

20050408, 10:59 #7
 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
BraddyIf you are a fool at forty, you will always be a fool

20050408, 12:31 #8
 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:
 <LI>If B7 is empty, then N7 should also appear blank.
<LI>If (B7 is nonempty) and (ANY of E7, G7 and J7 contain nonzero values), then N7 displays "Item code is missing"
<LI>If (B7 is nonempty) 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 nonempty) and (E7, G7 and J7 all equal zero) and (E7 equals zero) then N7 displays "Volumes missing"
<LI>If (B7 is nonempty) and (E7, G7 and J7 all equal zero) and (E7 is less than zero) and (I7 is "xx") then N7 displays "OK"
=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've probably missed something.

20050408, 18:03 #9
 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
BraddyIf you are a fool at forty, you will always be a fool

20050408, 18:41 #10
 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

20050408, 19:20 #11
 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

20050409, 13:36 #12
 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.