Possible Excel Bug

K

kurtforrester

I am using Excel 2007 and belive I have found a bug. If I have two
identical formulas (the string representation of the formulas are
identical) in two different cells I get two different results.

=MAX((ROSTERING!$A3>=AVAILABILITY!$B$2:$B$6)*(ROSTERING!
$A3<=AVAILABILITY!$C$2:$C$6)*(AVAILABILITY!$D$2:$D$6))

If the above formula is in cell C2 it returns the correct result,
however, if it is in B3 it returns the wrong number.

Any ideas would be appreciated in detecting my error or confirming it
as a bug (not expected behaviour)

Cheers
 
K

kurtforrester

I am using Excel 2007 and belive I have found a bug. If I have two
identical formulas (the string representation of the formulas are
identical) in two different cells I get two different results.

=MAX((ROSTERING!$A3>=AVAILABILITY!$B$2:$B$6)*(ROSTERING!
$A3<=AVAILABILITY!$C$2:$C$6)*(AVAILABILITY!$D$2:$D$6))

If the above formula is in cell C2 it returns the correct result,
however, if it is in B3 it returns the wrong number.

Any ideas would be appreciated in detecting my error or confirming it
as a bug (not expected behaviour)

Cheers
Click to expand...

Update to the bug.

I tested the spreadsheet in OpenOffice Calc 2.4 and it also did not
produce the expected result.

I made further changes to the formula to transpose the relevant arrays
(within the formula with TRANSPOSE) and the OpenOffice version
produces the correct result however excel still produces unexpected
results.
 
M

Mike Middleton

kurtforrester -

One thing to check: How are you entering the formula into the two different
cells? If you are using copy & paste, the mixed reference $A3 will yield a
different row reference.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel
 
K

kurtforrester

kurtforrester  -

One thing to check: How are you entering the formula into the two different
cells? If you are using copy & paste, the mixed reference $A3 will yield a
different row reference.

-  Mike Middletonhttp://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel
Click to expand...

Still no resolution to this bug issue. I am confident that the
formulae entered is correct and the tips by other users have not
resolved the issue.
 
H

Harlan Grove

(e-mail address removed) wrote...
....
=MAX((ROSTERING!$A3>=AVAILABILITY!$B$2:$B$6)
*(ROSTERING!$A3<=AVAILABILITY!$C$2:$C$6)*(AVAILABILITY!$D$2:$D$6))

If the above formula is in cell C2 it returns the correct result,
however, if it is in B3 it returns the wrong number.
Click to expand...
....

This isn't a bug if *YOU* haven't entered these formulas as ARRAY
FORMULAS. Search online help on how to enter array formulas.

Simpler example. With A1:A12 containing

2
4
3
1
6
4
1
5
4
3
5
4

enter the following formulas

C2: =MAX(A1:A12+0)

C3: =MAX(A1:A12+0)

C2 will evaluate to 4 and C3 to 3. Why? Because in most situations
Excel uses implicit array indexing when formulas contain array
expressions derived from ranges. In this example, A1:A12+0 is such an
array expression, and MAX is one of the functions that won't evaluate
such derived arrays as arrays unless the formula is entered as an
array formula.

Excel implicitly returns just 4 for A1:A12+0 to the C2 formula and
just 3 for A1:A12+0 for the C3 formula.

If you enter these as array formulas, both C2 and C3 return 6.

So not a bug. User error (in this case an error of misunderstanding).
 
H

Harlan Grove

(e-mail address removed) wrote...
....
Still no resolution to this bug issue. I am confident that the
formulae entered is correct and the tips by other users have not
resolved the issue.
Click to expand...

But you still haven't entered these formulas as array formulas.
 
D

D8.Consultor

(e-mail address removed) wrote...

...


But you still haven't entered these formulas as array formulas.
Click to expand...

Try testing your formula components individually within each cell to
check for possible variations.
 
K

kurtforrester

(e-mail address removed) wrote...

...>=MAX((ROSTERING!$A3>=AVAILABILITY!$B$2:$B$6)


...

This isn't abugif *YOU* haven't entered these formulas as ARRAY
FORMULAS. Search online help on how to enter array formulas.

Simpler example. With A1:A12 containing

2
4
3
1
6
4
1
5
4
3
5
4

enter the following formulas

C2:  =MAX(A1:A12+0)

C3:  =MAX(A1:A12+0)

C2 will evaluate to 4 and C3 to 3. Why? Because in most situations
Excel uses implicit array indexing when formulas contain array
expressions derived from ranges. In this example, A1:A12+0 is such an
array expression, and MAX is one of the functions that won't evaluate
such derived arrays as arrays unless the formula is entered as an
array formula.

Excel implicitly returns just 4 for A1:A12+0 to the C2 formula and
just 3 for A1:A12+0 for the C3 formula.

If you enter these as array formulas, both C2 and C3 return 6.

So not abug. User error (in this case an error of misunderstanding).
Click to expand...

I have moved to OpenOffice calc for spreadsheet works as it returns
the expected results.

Thanks for the advise.
 
K

kurtforrester

Try testing your formula components individually within each cell to
check for possible variations.
Click to expand...

I tested the components individually and it works. When used in
combination Excel returns garbage. I open the document in OpenOffice
calc and it works as expected. Cheers thanks for the advise.
 
K

kurtforrester

(e-mail address removed) wrote...

...>=MAX((ROSTERING!$A3>=AVAILABILITY!$B$2:$B$6)


...

This isn't abugif *YOU* haven't entered these formulas as ARRAY
FORMULAS. Search online help on how to enter array formulas.

Simpler example. With A1:A12 containing

2
4
3
1
6
4
1
5
4
3
5
4

enter the following formulas

C2:  =MAX(A1:A12+0)

C3:  =MAX(A1:A12+0)

C2 will evaluate to 4 and C3 to 3. Why? Because in most situations
Excel uses implicit array indexing when formulas contain array
expressions derived from ranges. In this example, A1:A12+0 is such an
array expression, and MAX is one of the functions that won't evaluate
such derived arrays as arrays unless the formula is entered as an
array formula.

Excel implicitly returns just 4 for A1:A12+0 to the C2 formula and
just 3 for A1:A12+0 for the C3 formula.

If you enter these as array formulas, both C2 and C3 return 6.

So not abug. User error (in this case an error of misunderstanding).
Click to expand...

I am not adding a scalar to an array I am adding arrays of equal size
and I would expect that excel returns an array. If it does not then it
is unexpected behaviour. Using the same logic I opened the same file
in OpenOffice calc and it works... Maybe microsoft logic is a little
different to mine.
 
D

Dave Peterson

Or you could use ctrl-shift-enter and array enter the formula.

Wait, that's been suggested before and you just ignore that suggestion.
 
D

Don Guillett

You and Open Office should have a nice life together. Go and enjoy.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
(e-mail address removed) wrote...

...>=MAX((ROSTERING!$A3>=AVAILABILITY!$B$2:$B$6)


...

This isn't abugif *YOU* haven't entered these formulas as ARRAY
FORMULAS. Search online help on how to enter array formulas.

Simpler example. With A1:A12 containing

2
4
3
1
6
4
1
5
4
3
5
4

enter the following formulas

C2: =MAX(A1:A12+0)

C3: =MAX(A1:A12+0)

C2 will evaluate to 4 and C3 to 3. Why? Because in most situations
Excel uses implicit array indexing when formulas contain array
expressions derived from ranges. In this example, A1:A12+0 is such an
array expression, and MAX is one of the functions that won't evaluate
such derived arrays as arrays unless the formula is entered as an
array formula.

Excel implicitly returns just 4 for A1:A12+0 to the C2 formula and
just 3 for A1:A12+0 for the C3 formula.

If you enter these as array formulas, both C2 and C3 return 6.

So not abug. User error (in this case an error of misunderstanding).
Click to expand...

I am not adding a scalar to an array I am adding arrays of equal size
and I would expect that excel returns an array. If it does not then it
is unexpected behaviour. Using the same logic I opened the same file
in OpenOffice calc and it works... Maybe microsoft logic is a little
different to mine.
 
H

Harlan Grove

Don Guillett said:
You and Open Office should have a nice life together. Go and enjoy.
Click to expand...
....

The OP may be an idiot, but that implies nothing about OpenOffice.
Still, fitting in a way to respond thoughtlessly to thoughtless OPs.
 
H

Harlan Grove

(e-mail address removed) wrote...
....
I am not adding a scalar to an array I am adding arrays of equal size
and I would expect that excel returns an array. If it does not then it
is unexpected behaviour. Using the same logic I opened the same file
in OpenOffice calc and it works... Maybe microsoft logic is a little
different to mine.
Click to expand...

I was providing the simplest example of an array expression derived
from a range - a range plus a scalar. If you'd prefer a more
complicated example closer to your original formula, here goes.

In OpenOffice Calc 2.4.0, I have the following in A1:C12.

06/26/2008 06/27/2008 2
06/27/2008 06/27/2008 4
06/27/2008 06/27/2008 3
06/26/2008 06/27/2008 1
06/27/2008 06/28/2008 6
06/27/2008 06/29/2008 4
06/26/2008 06/28/2008 1
06/27/2008 06/27/2008 5
06/26/2008 06/26/2008 4
06/27/2008 06/27/2008 3
06/27/2008 06/29/2008 5
06/27/2008 06/27/2008 4

And I have 06/26/2008 in A14 and 06/27/2008 in B14. If I enter the
following REGULAR formulas

E2: =MAX((A1:A12>A14)*(B1:B12<=B14)*C1:C12)

E3: =MAX((A1:A12>A14)*(B1:B12<=B14)*C1:C12)

then - SURPRISE! SURPRISE! - E2 evaluates to 4 and E3 to 3. OTOH, if I
enter either formula AS AN ARRAY FORMULA then they return the correct
result: 5. FWIW, the OpenOffice Calc 3.0 beta reproduces these EXACT
SAME results.

So while you could use OpenOffice to do this, what *YOU* seem
unwilling to understand is that OpenOffice Calc works EXACTLY THE SAME
AS EXCEL when evaluating array formulas like these. If you really want
the correct result most of the time rather than occasionally by happy
accident, *YOU* need to enter such formulas *BOTH* in Excel *AND*
OpenOffice Calc as ARRAY FORMULAS.

FWIW, gnumeric 1.9.1 returns the same result as Excel and OOo Calc. In
my experience, only the Applix Spreadsheet (Applixware running on
Linux) would have evaluated this sort of formula correctly when not
entered as an array formula because it's the only spreadsheet I've
used that didn't provide array formulas distinct from regular formulas
but did evaluate derived arrays the same as they would have been
evaluated in Excel array formulas. I suppose it's possible Quattro Pro
would also evaluate this correctly using
@ARRAY(@MAX((A1..A12>A14)*(B1..B12<=B14)*C1..C12), but I gave up on QP
before I had torture tested its @ARRAY. If the range above had been in
A2:C13 rather than A1:C12 and had had column labels in A1:C1 like
StartDate, EndDate, Amount, this could have been done in Lotus 123
Release 3.0 and later as

@DMAX(A1:C13,"Amount",(StartDate>$A$14)#AND#(EndDate<=$B$14))

Don't BS about other spreadsheet programs in this newsgroup.

So still user error rather than a bug, whether in Excel or OOo Calc. A
fine example of none so blind as those who will not see.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif function giving incorrect answer 5
Very strange behaviour of VLOOKUP in Excel 2010 4
sum working hours each month (sum hours in the last row for a monthin a table with dates) 3
Index match with multiple criteria(different than usual) 3
(Advanced Excel) Figuring out a formula of continuous progressiontrying to create 19 Formulas into O 5
Lookup 14
Compare sheet utility that can compare formula too 3
Excel VBA 1

Top