1. 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

2. 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. Re: IF Error (Excel 2002)

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

Hi Hans

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,

4. Re: IF Error (Excel 2002)

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

5. 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"

6. 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. 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

8. Re: IF Error (Excel 2002)

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!

9. 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.

10. Re: IF Error (Excel 2002)

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

Steve

11. 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.

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.

12. 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
•