Excel 2000 - Get 'if' to ignore blank columns 1

[dsmith910]

=IF(OR(K17=L17,K17=M17,K17=N17,K17=O17,K17=P17,L17=M17,L17=N17,L17=O17,L17=P17,M17=N17,M17=O17,M17=P17,N17=P17),"Wrong","OK&quot

The above formula is meant to discover whetgher any 4 out of 6 columns has the same value. i.e. if 'Collie' is in column K17 as well as L17 it will return a value of 'Wrong'

Unfortunately in every case two of the columns will be empty - this means that the formula returns 'wrong' every time.

Anyone any idea how to get it to ignore an empty or 'zero' column?

Thanks

Doreen

 

[MisterC]

LOL, There MUST be a better way, but...

=IF(OR(
AND(K17=L17, K17<>&quot;&quot;),
AND(K17=M17, K17<>&quot;&quot;),
AND(K17=N17, K17<>&quot;&quot;),
AND(K17=O17, K17<>&quot;&quot;),
AND(K17=P17, K17<>&quot;&quot;),
AND(L17=M17, L17<>&quot;&quot;),
AND(L17=N17, L17<>&quot;&quot;),
AND(L17=O17, L17<>&quot;&quot;),
AND(L17=P17, L17<>&quot;&quot;),
AND(M17=N17, M17<>&quot;&quot;),
AND(M17=O17, M17<>&quot;&quot;),
AND(M17=P17, M17<>&quot;&quot;),
AND(N17=P17, N17<>&quot;&quot;)),&quot;Wrong&quot;,&quot;OK&quot;)

Good Luck!

 

[MisterC]

Oh hey, it looks like you need a comparison for O17=P17 also.

 

[MisterC]

=IF(OR(
AND(K17=L17, K17<>&quot;&quot;),
AND(K17=M17, K17<>&quot;&quot;),
AND(K17=N17, K17<>&quot;&quot;),
AND(K17=O17, K17<>&quot;&quot;),
AND(K17=P17, K17<>&quot;&quot;),
AND(L17=M17, L17<>&quot;&quot;),
AND(L17=N17, L17<>&quot;&quot;),
AND(L17=O17, L17<>&quot;&quot;),
AND(L17=P17, L17<>&quot;&quot;),
AND(M17=N17, M17<>&quot;&quot;),
AND(M17=O17, M17<>&quot;&quot;),
AND(M17=P17, M17<>&quot;&quot;),
AND(N17=O17, N17<>&quot;&quot;)
AND(N17=P17, N17<>&quot;&quot;)
AND(O17=P17, O17<>&quot;&quot;)),&quot;Wrong&quot;,&quot;OK&quot;)

 

[TonyJollans]

Hi Doreen,

If I understand you correctly and you just want to find out if there are any duplicates, then this should do it for you ..

=IF(MAX(COUNTIF(K1717,K1717))>1,"Wrong","OK&quot

.. array entered - that is press <Ctrl><Shift><Enter> when you input it (or edit it)

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[MisterC]

Nice!

 

[bluedragon2]

Tony,

Wouldn't that formula work without being entered as an array also?



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[TonyJollans]

Hi Blue,

No - if not array entered it will only check K17 against the other cells; to work on the array of cells in the first parameter it must be array-entered.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[bluedragon2]

Tony,

I agree that it is an array formula, but when used in conjunction with the MAX and IF functions, it takes into account the array logic without being entered as an array. One way to see this is to put =MAX(COUNTIIF(K17:O17=K17:O17)) in cells J16 through O16.

I may be on the wrong track in my thinking, but that is how I thought it worked.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

I take it back Tony, I do think it does what I am thinking, but will not give out a standard (correct) output of its logic every time unless it is entered as an array.

I copied the whole formula to about 20 cells and had an 80-20 mix of OK's and Wrong's when it was Wrong to start with. Strange to say the least.

The only common thing is if I clicked on the Fx button, the result was &quot;wrong&quot; but the cell was &quot;OK&quot;.

Anyway, good formula for the task at hand and a SFU...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[dsmith910]

thanks Guys

I did in the end manage to get around the problem by putting yet another 6 columns in, each of which said if the column you are copying is = to zero then enter Dog1, Dog2 - etc. for each of the original columns. Then my original rather inelegant solution does work with the new columns since there are no empty cells. But.... I will definitely be trying out your suggestions as if I can get this to work it will mean I can dispense with the extra 6 columns.

Thanks again Doreen

 

[TonyJollans]

Hi Blue,

I think the problem is that, unless array-entered, the formula doesn't make any sense.

I wasn't correct in what I said before - I had just got that result by chance because I had entered the formula in column 17. Having done a bit more testing this is what appears to happen for me when the formula is normally entered:

When the range being checked is a single row AND the formula is entered in a cell in a column which intersects with the range, THEN the cell at the intersection of that column and the range is checked against all other cells in the range; otherwise the result is &quot;OK&quot; regardless. The equivalent happens if the range is in a single column, but if the range has more than one row AND more than one column the result is always &quot;OK&quot;.

I have to admit that array-entered formulae are one of the things I find it hardest to get my head round. Some days it all makes sense, some days it doesn't

Thanks for the star,

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.