Limitations in array formulas ?

[nunoferreira]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

Two kinds of problems I noticed:

1.The following formula does not produce the expected results:

{=SUM(IF(AND(WEEKDAY($G$26:$G$29)=3,MONTH($G$26:$G$29)=12),$H$26:$H$29,0))}

(The formula tries to sum up the values corresponding to Tuesdays of December)

2. The following formula cannot even be entered in Excel:

{=SUM(IF(2+2=3,$H$26:$H$29,0))}

(The formula has no sense; it is just to highlight that the parser does not recognise the first argument of the IF (2+2=3) as a logical test)

Someone has a workaround (or at least an explanation?). Thanks very much

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

Two kinds of problems I noticed:

1.The following formula does not produce the expected results:

{=SUM(IF(AND(WEEKDAY($G$26:$G$29)=3,MONTH($G$26:$G$29)=12),$H$26:$H$29,0))}

(The formula tries to sum up the values corresponding to Tuesdays of December)

2. The following formula cannot even be entered in Excel:

{=SUM(IF(2+2=3,$H$26:$H$29,0))}

(The formula has no sense; it is just to highlight that the parser does not
recognise the first argument of the IF (2+2=3) as a logical test)

Someone has a workaround (or at least an explanation?). Thanks very much

You'll have to provide some more info. The formula in 1. Is fine. What does
the data look like? What results does it produce? What do you think it
should produce?

The formula in 2 is also fine. I agree it makes no sense, but the syntax is
correct and it is parsed fine, and correctly produces the result of 0. You
are doing something wrong, or misunderstanding something. What happens when
you enter the formula?

 

[nunoferreira]

Thanks Bob for tying to help.

1.

$G$26:$G$29 and $H$26:$H$29 contain

G H

26 2-Dec-08 1
27 7-Dec-08 2
28 9-Dec-08 3
29 12-Dec-08 4

The first and third dates are Tuesdays (WEEKDAY($G$26) produces 3 as does WEEKDAY($G$28)). I expect the formula

{=SUM(IF(AND(WEEKDAY($G$26:$G$29)=3,MONTH($G$26:$G$29)=12),$H$26:$H$29,0))}

to produce the result 4 (1 of Dec 2 + 3 of Dec 9)

The formula produces 0.

I agree, I am doing something wrong or misunderstanding something. Problem is that right now I just can't figure out what.

2.

Well, I have tried to reproduce the problem and ... it disappeared! Strange enough since for the sake of total accuracy in my question I copy-pasted the formula from my worksheet into this message and to reproduce the problem I copy-pasted the formula back from the message into the worksheet! Must be the weather...

Thanks again,

Nuno

 

[nunoferreira]

More info on problem 1.

With the formula

{=SUM(IF(AND(WEEKDAY($G$26:$G$29)=3,
MONTH($G$26:$G$29)=12),$H$26:$H$29,0))}

if ALL dates in range $G$26:$G$29 are tuesdays of december then the formula produces a result that is the sum of the values in range $H$26:$H$29. As soon as one the dates is no longer a tuesday or is not in december, the formula produces 0 as result.

This behaviour of the formula is either a bug or 'wad' (works as designed). If it is 'wad' I don't think it matches what the user intuitively expects the formula to do. I think the user expects the formula to sum up the values in $H$26:$H$29 that correspond to december tuesdays in $G$26:$G$29.

Only way around these shortcomings : write a macro that does the computation (provided you're still using XL 2004, that is).

Also to note:

If you make the array formula a bit simpler

{=SUM(IF(WEEKDAY($G$26:$G$29)=3,$H$26:$H$29,0))}

i.e. instead of testing for tuesdays and december you only test for tuesdays

the formula produces the expected result of 4.

Interestingly enough, if you change this formula to

{=SUM(IF(WEEKDAY($G$26:$G$29)=3,$H$26:$H$29,99))}

i.e. the 'false' branch of the IF is set to 99

the formula produces the much amazing result of... 202! Which may not be too worrying since it is difficult to understand what the user intended to mean by setting, in this context, the 'false' branch of the IF to a constant other than 0.

Provisional conclusion of all this: simple array formulas are ok; but when putting together a complex array formula test very carefully the results that are produced, they may prove very different from what you mean to do. And be prepared to have to write a macro if the formula definitively refuses to behave.

 

[Bob Greenblatt]

Thanks Bob for tying to help.

1.

$G$26:$G$29 and $H$26:$H$29 contain

G H

26 2-Dec-08 1
27 7-Dec-08 2
28 9-Dec-08 3
29 12-Dec-08 4

The first and third dates are Tuesdays (WEEKDAY($G$26) produces 3 as does
WEEKDAY($G$28)). I expect the formula

{=SUM(IF(AND(WEEKDAY($G$26:$G$29)=3,MONTH($G$26:$G$29)=12),$H$26:$H$29,0))}

to produce the result 4 (1 of Dec 2 + 3 of Dec 9)

The formula produces 0.

I agree, I am doing something wrong or misunderstanding something. Problem is
that right now I just can't figure out what.

2.

Well, I have tried to reproduce the problem and ... it disappeared! Strange
enough since for the sake of total accuracy in my question I copy-pasted the
formula from my worksheet into this message and to reproduce the problem I
copy-pasted the formula back from the message into the worksheet! Must be the
weather...

Thanks again,

Nuno

What is happening is that the array WEEKDAY($G$26:$G$29)=3
{False,false,true,false} evaluates to false and the other array
{true,true,true,true} of course evaluates to TRUE. Therefore you have a
condition of And(false,true) which, of course is false. To do what you want,
just multiply the arrays:
=SUM((WEEKDAY($G$26:$G$29)=3)*(MONTH($G$26:$G$29)=12)*$H$26:$H$29)

-don't forget to array enter (control-shift-Enter)

(As a tip for future questions, and formula evaluation, you can easily see
how excel is parsing and evaluating formulas by highlighting a portion of
the formula in the formula bar and pressing F9)

 

[nunoferreira]

Thanks Bob for tying to help.

1.

$G$26:$G$29 and $H$26:$H$29 contain

G H

26 2-Dec-08 1
27 7-Dec-08 2
28 9-Dec-08 3
29 12-Dec-08 4

The first and third dates are Tuesdays (WEEKDAY($G$26) produces 3 as does
WEEKDAY($G$28)). I expect the formula

{=SUM(IF(AND(WEEKDAY($G$26:$G$29)=3,MONTH($G$26:$G$29)=12),$H$26:$H$29,0))}

to produce the result 4 (1 of Dec 2 + 3 of Dec 9)

The formula produces 0.

I agree, I am doing something wrong or misunderstanding something. Problem is
that right now I just can't figure out what.

2.

Well, I have tried to reproduce the problem and ... it disappeared! Strange
enough since for the sake of total accuracy in my question I copy-pasted the
formula from my worksheet into this message and to reproduce the problem I
copy-pasted the formula back from the message into the worksheet! Must be the
weather...

Thanks again,

Nuno

What is happening is that the array WEEKDAY($G$26:$G$29)=3
{False,false,true,false} evaluates to false and the other array
{true,true,true,true} of course evaluates to TRUE. Therefore you have a
condition of And(false,true) which, of course is false. To do what you want,
just multiply the arrays:
=SUM((WEEKDAY($G$26:$G$29)=3)*(MONTH($G$26:$G$29)=12)*$H$26:$H$29)

-don't forget to array enter (control-shift-Enter)

(As a tip for future questions, and formula evaluation, you can easily see
how excel is parsing and evaluating formulas by highlighting a portion of
the formula in the formula bar and pressing F9)
[/QUOTE]
Bob,

This is a clever solution for my problem. Many thanks.

Thanks for the tip also. That problem of the parser mis-behaving I am now convinced was caused by a non-displayable spurious character that somehow I have managed to include in my formula.

Regards,

Nuno