Odd behaviour: A bug?

[Felipe]

Could someone explain me the reason the formula: =SUM(IF($C$7:$C$18<=$E
$3;$D$7:$D$18;0)) gives different results when placed in 2 different
places?

To replicate the problem:

-Fill C7:C18 with dates, for example from 01/01/2009 to 01/12/2009
-Fill E3 with 01/11/2009 or any other date
-Fill D718 with numerical values
-Fill E6:E19 with this formula: =SUM(IF($C$7:$C$18<=$E$3;$D$7:$D
$18;0))

What I get:
-In E6 and E19 the result is #Value!
-In E18 the result is 0
-In E7:E17 the result is the correct sum (the expected behavior).

Any enlightenment on this??

I would really appreciate your help in different versions of Office. I
was able to reproduce the same problem in 2003 and 2007.

Best Regards,
Felipe

 

[Dave Peterson]

The formula you used =sum(if(...)) is an array formula.

That means you have to enter the formula by using ctrl-shift-enter. If you do
it correctly, you'll see that excel wraps your formula with {}'s. (Don't type
them yourself.)

I find that array entered formulas can be a problem to remember.

So if there's an alternative, I'd use an alternative.

In your case, the =sumif() formula seems to fit nicely:

=SUMIF($C$7:$C$18;"<="&$D$3;$D$7:$D$18)

This formula doesn't need the ctrl-shift-enter stuff.

 

[Dave Peterson]

ps.

The formula that worked ok was correctly array entered. The failing formula was
not.

 

[Felipe]

ps.

The formula that worked ok was correctly array entered.  The failing formula was
not.

Hi Dave!! Thanks for the ctrl+shit+enter tip. I never heard about it
although i'm not a excel guru!


But is there an explanation to work in some cells and others don't
with enter only?

Thank a lot!
Best Regards,
Felipe

 

[Dave Peterson]

I would guess you just got lucky with your data so you didn't see an error. But
I wouldn't trust the results of that non-array entered formula.

Array enter those formula and see if what you see is different.

 

[Felipe]

I would guess you just got lucky with your data so you didn't see an error.  But
I wouldn't trust the results of that non-array entered formula.

Array enter those formula and see if what you see is different.

Yes, you are right.... the array enter method works and the result is
different.

Regards,
Felipe

 

[JoeU2004]

Hi Dave!! Thanks for the ctrl+shit+enter tip.
[....]
But is there an explanation to work in some cells
and others don't with enter only?

You understand now that your original SUM(IF(...)) should have been entered
as an array formula; that is, committed with ctrl+shift+Enter instead of
just Enter.

But to explain the non-array formula (mis)behavior....


You wrote previously:

-Fill E3 with 01/11/2009 or any other date

Not just any date. To get exactly the results you saw, it must be 1 Nov
2009.

-Fill E6:E19 with this formula:
=SUM(IF($C$7:$C$18<=$E$3;$D$7:$D$18;0))

What I get:
-In E6 and E19 the result is #Value!
-In E18 the result is 0
-In E7:E17 the result is the correct sum (the expected behavior).

Not really the "correct" (intended) sum. To see the difference clearly,
fill D718 with 1 through 12.

You can get some insight into what is happening if you use the feature Tools

Formula Auditing > Evaluate Formula to step through the evaluation of E7

and E8, for example.

Ostensibly, C7:C18 represents an array of dates. But since you entered a
non-array formula, Excel tries to choose an "appropriate" element from the
array.

What's "appropriate"?

If the location of the formula is parallel to (in this case, in the same row
as) one of the cells in the range C7:C18, Excel chooses the corresponding
value from the range. For the formula in E7, Excel chooses the value in C7;
for the formula in E8, Excel chooses C8; for the formula in E18, Excel
chooses the value in C18.

Since the dates in C7 through C17 are less than or equal to the date in E3,
the IF() condition in E7:E17 is true, and Excel evaluates SUM(D718).
Since the date in C18 is greater than the date in E3, the iF() condition in
E18 is false, and Excel evaluates SUM(0).

However, the formulas in E6 and E19 are not parallel to any cell in the
range C7:C18. Consequently, Excel returns a #VALUE error, meaning: there
is no corresponding value.


The behavior of the array formula is very different, as is its result. You
can clearly see this behavior step-by-step by using Evaluate Formula. The
behavior is effectively:

sum({if(C7<=E3,D7,0), if(C8<=E3,D8,0),...., if(C18<=E3,D18,0)})

So, effectively we get SUM(D717,0), which is different from SUM(D718).


PS.... We make use of the non-array interpretation when we use named
ranges. For example, suppose the name oldCost is the range A1:A100, and the
name newCost is the range B1:B100. If we enter the formula =newCost-oldCost
into C1:C100, we expect C1 to be evaluated as =B1-A1, C2 to be =B2-A2, etc.
FYI, we could just as easily have entered the formula =B1:B100-A1:A100 into
C1:C100.

But if you had intended the non-array interpretation of your original
formula (which, of course, you did not), it would been clearer to write and
copy down the following non-array formula (commit with Enter as usual, not
ctrl+shift+Array):

=IF($C$7:$C$18<=$E$3, SUM($D$7:$D$18), 0)


----- original message -----

ps.

The formula that worked ok was correctly array entered. The failing
formula was
not.

Hi Dave!! Thanks for the ctrl+shit+enter tip. I never heard about it
although i'm not a excel guru!


But is there an explanation to work in some cells and others don't
with enter only?

Thank a lot!
Best Regards,
Felipe