Reproducible Bug in Excel X with labels and 'and' function.

[jaques]

I can post a file demonstrating this bug.
When you set your preferences to use 'labels in formulas' [sic], it
permits nice and readable formulae.
I generally select the area in question, then use 'create names' using
'top row'.
But I burned up a lot of time yesterday puzzling over why a complex
boolean formula didn't work. It turned out to be a bug.
When you use the 'and' function, it refers to the next row, not the
current row.
I quickly looked at Microsoft for a place to post bug reports and
couldn't find one.

 

[JE McGimpsey]

I quickly looked at Microsoft for a place to post bug reports and
couldn't find one.

You don't give nearly enough detail to be able to reproduce this, either
in XLv.X or XL04. If you have an ftp site, you can post your file, but
in my experience, a file's just about useless without a detailed
procedure for how you created it - so the procedure's usually sufficient.

Your problem statement mixes two different types of references - labels
are dynamic ranges that use the intersection operator at the sheet
level, while the named ranges created with Insert/Names/Create... are
workbook level objects. Which does the problem occur with (or does it
happen with both?)?


As far as bugs go, posting here should be sufficient, or you can use the
MacOffice Send Feedback page:

http://www.microsoft.com/mac/default.aspx?pid=feedback

 

[jaques]

Thank you for reply, JE McGimpsey.

As you suggested, I put the demo file on my ftp site. It may be found
at http://hierlaw:@hierlaw.com/demo bug named cols.xls.

Actually, finding the bug in the file should be quite obvious. The file
is designed to demonstrate the bug. There is a single cell under the
'bug' column which contains "#REF", demonstrating that it referred to
the next line. Beside it, under a column called 'tester', there is an
identical formula which does not use 'named ranges' which works
correctly. There is even a cell under the 'works' column to show that
the named range refers to the current row OUTSIDE an 'and' function.

As to your complaint about my terminology. When I use 'label', that is
consistent with the Application's preference setting "use labels in
formulas[sic]". So, if I'm wrong, so is Microsoft.

As to your complaint that I wasn't specific enough, I said that when
using 'named ranges' "[w]hen you use the 'and' function, it refers to
the next row, not the current row." Are you saying that there is a case
where this DOES work or where it is considered correct?

Interestingly, I posted this through Google groups, which I had to join
to post. But I only discovered that somebody had answered through a
Google Alert I have set up concerning my domain name. And the Google
Alert did not refer to a Google Group, but to a Microsoft support site
(http://support.microsoft.com/newsgr...&tid=e2bb6acc-9e66-4f15-9ccf-c2607366919a&p=1).
So, it would be helpful to send a copy of your reply to me.

 

[Bob Greenblatt]

Thank you for reply, JE McGimpsey.

As you suggested, I put the demo file on my ftp site. It may be found
at http://hierlaw:@hierlaw.com/demo bug named cols.xls.

Actually, finding the bug in the file should be quite obvious. The file
is designed to demonstrate the bug. There is a single cell under the
'bug' column which contains "#REF", demonstrating that it referred to
the next line. Beside it, under a column called 'tester', there is an
identical formula which does not use 'named ranges' which works
correctly. There is even a cell under the 'works' column to show that
the named range refers to the current row OUTSIDE an 'and' function.

As to your complaint about my terminology. When I use 'label', that is
consistent with the Application's preference setting "use labels in
formulas[sic]". So, if I'm wrong, so is Microsoft.

As to your complaint that I wasn't specific enough, I said that when
using 'named ranges' "[w]hen you use the 'and' function, it refers to
the next row, not the current row." Are you saying that there is a case
where this DOES work or where it is considered correct?

PMJI, but this is not a bug. It is a bug in your understanding of named
ranges and arrays. The formula in A10 is not at all identical to the formula
in B10 as you suggest. The named ranges used in b 10 refer to a range of
thousands of cells. The formula in A10 refers to 2 single cells. The named
range PST refers to E10:E65536. So, in B10, the part of the formula PST<0
resolves to true, while SBNeg resolves to a ref error because of the error
in D11.

I have no idea what you are trying to do or illustrate here or in your
actual sheet, but try changing the sizes of the ranges so you can see how
they resolve in the formula. For example, if PST was defined to refer to
$e$10:$e$15, you can easily see the referenced values by highlighting a PST
in the formula bar and hitting F10.

The reason you think the formula in c10 is correct (it isn't) is because you
can not return an array to a single cell. The name sbNegkj simply returns
the first value of the array (in this case the value of cell D10 to C10.
Prove this by filling down the formula from C10 downwards. You'll see that
the second value of SBNegjk, the ref error appears properly in C11.

You need to rethink what you are doing, and what you are trying to prove
here, and read up on arrays and array formulas. Also whether it is
intentional or not, your formulas refer to offset ranges. SBNeg refers to
D9:d65536, while all the others refer to the beginning row as 10. This may
be intentional, but it is probably not.

 

[Bob Greenblatt]

OOPS, at the end of the second paragraph, I meant F9 not F10.

 

[jaques]

That's so painful! A 'bug in my understanding'!? Oh well, I will just
have to try to get past that.

Yes, a range is not necessarily just one cell, and yes, the
labels/names do refer to long columns of cells. But that's a feature of
Excel to facilitate working with table-like structures. That's why I
included the 'where it works' column, to show that, yes, you can refer
to a column of cells and XL is USUALLY smart enough to apply the
correct one (matching the same row), but NOT when you use the 'and'
function.

I wouldn't have been surprised if such names never worked. But if you
make them work, you must make them work reliably, or it is a bug.

To help you see this, I have filled down the table, 'where it works'
and put some sample data in SBNegKJ, and then re-uploaded it.

Thank you for reply, JE McGimpsey.

As you suggested, I put the demo file on my ftp site. It may be found
at http://hierlaw:@hierlaw.com/demo bug named cols.xls.

Actually, finding the bug in the file should be quite obvious. The file
is designed to demonstrate the bug. There is a single cell under the
'bug' column which contains "#REF", demonstrating that it referred to
the next line. Beside it, under a column called 'tester', there is an
identical formula which does not use 'named ranges' which works
correctly. There is even a cell under the 'works' column to show that
the named range refers to the current row OUTSIDE an 'and' function.

As to your complaint about my terminology. When I use 'label', that is
consistent with the Application's preference setting "use labels in
formulas[sic]". So, if I'm wrong, so is Microsoft.

As to your complaint that I wasn't specific enough, I said that when
using 'named ranges' "[w]hen you use the 'and' function, it refers to
the next row, not the current row." Are you saying that there is a case
where this DOES work or where it is considered correct?

PMJI, but this is not a bug. It is a bug in your understanding of named
ranges and arrays. The formula in A10 is not at all identical to the formula
in B10 as you suggest. The named ranges used in b 10 refer to a range of
thousands of cells. The formula in A10 refers to 2 single cells. The named
range PST refers to E10:E65536. So, in B10, the part of the formula PST<0
resolves to true, while SBNeg resolves to a ref error because of the error
in D11.

I have no idea what you are trying to do or illustrate here or in your
actual sheet, but try changing the sizes of the ranges so you can see how
they resolve in the formula. For example, if PST was defined to refer to
$e$10:$e$15, you can easily see the referenced values by highlighting a PST
in the formula bar and hitting F10.

The reason you think the formula in c10 is correct (it isn't) is because you
can not return an array to a single cell. The name sbNegkj simply returns
the first value of the array (in this case the value of cell D10 to C10.
Prove this by filling down the formula from C10 downwards. You'll see that
the second value of SBNegjk, the ref error appears properly in C11.

You need to rethink what you are doing, and what you are trying to prove
here, and read up on arrays and array formulas. Also whether it is
intentional or not, your formulas refer to offset ranges. SBNeg refers to
D9:d65536, while all the others refer to the beginning row as 10. This may
be intentional, but it is probably not.

 

[Bob Greenblatt]

That's so painful! A 'bug in my understanding'!? Oh well, I will just
have to try to get past that.

Yes, a range is not necessarily just one cell, and yes, the
labels/names do refer to long columns of cells. But that's a feature of
Excel to facilitate working with table-like structures. That's why I
included the 'where it works' column, to show that, yes, you can refer
to a column of cells and XL is USUALLY smart enough to apply the
correct one (matching the same row), but NOT when you use the 'and'
function.

I wouldn't have been surprised if such names never worked. But if you
make them work, you must make them work reliably, or it is a bug.

To help you see this, I have filled down the table, 'where it works'
and put some sample data in SBNegKJ, and then re-uploaded it.

I still maintain that this is not a bug in Excel. The names do work reliably
when they are used correctly. In the Tester column, it is quite simple as
you can see. Each cell is resolved correctly. In the Where it works column,
Excel is correctly resolving the name SBNegj because the name resolution
refers to a single cell.

In the bug column, the formula =and(sbneg,pst<0), excel resolves and(sbneg)
which ALWAYS results in FALSE, and then resolves and(pst<0). To get this to
work as you intend, you need to coerce an array manipulation to occur BEFORE
the logical resolution. To do this, change the formula to
=and(1*sbneg,pst<0).

And, again, although the name resolution works correctly your definition of
sbneg begins one row off from all the other definitions. While this may,
indeed be what you want, it is a dangerous programming practice likely
resulting in unintended results that are hard to audit.