Spurious Formula Omits Adjacent Cells

[Walter Briscoe]

I have found curious behavior in both Excel 2003 and 2007.
I expect it happens in 2010 also.

I think Excel is behaving as designed and would value an explanation.
A1 contains a date formatted as "ddd, dd-mmm". e.g. "Fri, 18-May".
B1:E1 contain numbers
F1 contains =AVERAGE(B1:E1)

F1 is marked with a small green triangle in the top left corner.
If I select F1, I see an "!" on a yellow square (An American road
traffic sign?), oriented at 45 degrees . If I point to that "!", I am
given "The formula in this cell refers to a range that has additional
numbers adjacent to it." and an arrow appears. If I click that arrow and
click "Update Formula to Include Cells", F1 is changed to
=AVERAGE(A1:E1). i.e. My average is taken of numbers and a date.

There is a simple workaround. Rather than A1 being "18/05/2012" (my
default date format is "dd/mm/yyyy", set it to =DATE(2012, 05, 18).

This is a trivial matter, but I thought I would report it as I have not
seen it reported before in detail.
If a default date format is used, the warning is not given.

"Help on this error" points nowhere in 2003 and, uselessly, to "Correct
common errors in formulas" in 2007.

I know I can suppress the error; I believe both against an individual
cell and globally against the installation. I want to do neither. I just
want to persuade Excel to give me warnings which make sense to me.

 

[Vacuum Sealed]

Hi Walter

Yep, it also happens in xl2010 although you can make it disappear simply
by making the range absolute.

=Average($B1$1:$E$1)

HTH
Mick