Spurious Formula Omits Adjacent Cells

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

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; "Ignore Error" can be clicked against
an individual cell or globally by clicking Tools/Options/Error
Checking/Formula omits cells in region against the installation. I want
to do neither. I just want to persuade Excel to give me warnings when
there seems to be an issue. ;)
 
In message <DEDtr.7191$%[email protected]> of
Sat, 19 May 2012 12:34:11 in microsoft.public.excel.worksheet.functions,
Vacuum Sealed said:
Walter

Check your other post

:)
Click to expand...

I must apologise for sending my posting twice. My software did not
report the first sending attempt succeeded.

In 2003, I find fixing one end of the range does the job.
=Average($B1$1:E1)
That makes some sort of sense to me.
It is a better solution, because it is generally applicable, than mine,
which happens to work as it is easy to use a formula.

I have =AVERAGE(D5:D11) in D13 which correctly gets an adjacent numbers
warning due to the contents of D4 and D12. =AVERAGE(D$5:D$11) does the
job and can be copied through to B13:E13 and suppress the warnings more
easily than by manually changing each cell.

Thanks for your insight. It does not address the original question.
Why is a warning given for dd and ddd, dd-mm, but not for a Date format?
 
Back
Top