Excel 2007 : Array formulas condition problem-bug!

[luckylooke]

I am using Excel 2007 with slovak regional settings so my in-function separator is semicolon ";" instead of colon "," and here is my problem:

When I select range A1:A5 and then put formula: =IF(OR(ROW()=1;ROW()=3);1;0)
with keys CTRL+SHIFT+ENTER to make it array formula
all I get is ONEs form A1 to A5 even when condition is evaluated as FALSE, excel put there 1 instead of 0

Any solution???

[zbor]

Actually, it's not an array... It can be entered with normal Enter.

So select A1 to A5 and comfirm with Ctrl+Enter (without shift)...

And it will work.

Or put in first row (with normal enter) and pull down.

But you are right, it will evaluate 1 even it's FALSE

[squiggler47]

Its not an array formula, it is only evaluating on the first cell, just enter it as a normal formula and copy down!

[squiggler47]

it would work if you put row($a$1:$a$5) in to replace the two row() parts, but its redundant array formulas should be used as a last resort!

[DonkeyOte]

As outlined above the Array isn't necessary but in terms of explanation...

First off - it's not a bug

ROW returns an Array at all times - a cell can only display 1 item so though ROW is returning an Array of values the cell itself will only display 1 item (relative to index pos. etc)

This is why

Please Login or Register  to view this content.

applied to A1:A5 as an Array displays 1 to 5 - yet if you F9 on any of the cells formulae from within the Formula Bar you will note {1;2;3;4;5} displays in the Formula Bar

For this reason your tests all return 1 because in each Array there is a 1 and a 3 and thus the OR is always true.

To get 1s and 0s using Array entry you could use:

Please Login or Register  to view this content.

again though - we're not saying you should

[DonkeyOte]

it would work if you put row($a$1:$a$5) in to replace the two row() parts, but its redundant array formulas should be used as a last resort!

Darren, I don't think it would.

[luckylooke]

Actually, it's not an array... It can be entered with normal Enter.

So select A1 to A5 and comfirm with Ctrl+Enter (without shift)...

And it will work.

Or put in first row (with normal enter) and pull down.

But you are right, it will evaluate 1 even it's FALSE

and

Its not an array formula, it is only evaluating on the first cell, just enter it as a normal formula and copy down!

I have it as a part of array formula, but this was the problem why whole formula didnt work so I have simplified it.

As outlined above the Array isn't necessary but in terms of explanation...

First off - it's not a bug

ROW returns an Array at all times - a cell can only display 1 item so though ROW is returning an Array of values the cell itself will only display 1 item (relative to index pos. etc)

This is why

Please Login or Register  to view this content.

applied to A1:A5 as an Array displays 1 to 5 - yet if you F9 on any of the cells formulae from within the Formula Bar you will note {1;2;3;4;5} displays in the Formula Bar

For this reason your tests all return 1 because in each Array there is a 1 and a 3 and thus the OR is always true.

To get 1s and 0s using Array entry you could use:

Please Login or Register  to view this content.

again though - we're not saying you should

Thanks for your answer the fact that ROW() returns array is the reason why it was faulty... I didnt realised it... thanks again

PS: Your solution with ROWS function works perfectly But whats wierd that when I use one condition, it works:

Please Login or Register  to view this content.

[DonkeyOte]

whats wierd that when I use one condition, it works

So it does... ok this leads me to believe that what I wrote above is in the truest sense of the word - nonsense - at least in this context
(ROW does return an Array though at all times)

I'm not savvy enough to know why the OR would cause the Array to be evaluated differently however - I will ask others it the guys here don't have an explanation.
(ie rather than {2} I presume it must be being processed as {1;2;3;4;5})

[squiggler47]

I think the problem is that OR takes array arguments, so it evaluates the 2 expressions into 2 arrays and evaluates all 10 with an or, since there is always a 3 and a 1 it has to return true

[squiggler47]

I dindt quite word that right!

because the OR can take arguments as an array, it over rides the array formula use of the arrays!

[DonkeyOte]

My guess is it's simply the age old issue of using ORs in Arrays

Please Login or Register  to view this content.

would work just as well (conducting OR by addition)

[zbor]

Or this (for this particular case, your is more general):

=ABS(2-ROW())=1

Also, sorry if I miss explanation, user said (and I also get evaluating formula) that he get (in step before last):

=IF(FALSE, 1, 0) returns 1