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; 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.
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.