Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Spurious Formula Omits Adjacent Cells
70 views
Skip to first unread message
Walter Briscoe
May 18, 2012, 12:12:59 PM5/18/12
to
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. ;)
--
Walter Briscoe
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. ;)
--
Walter Briscoe
Walter Briscoe
May 18, 2012, 3:29:57 PM5/18/12
to
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
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.
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. ;)
--
Walter Briscoe
Vacuum Sealed
May 18, 2012, 10:28:02 PM5/18/12
to
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
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
Vacuum Sealed
May 18, 2012, 10:34:11 PM5/18/12
to
Walter
Check your other post
:)
Check your other post
:)
Walter Briscoe
May 19, 2012, 2:21:19 AM5/19/12
to
In message <DEDtr.7191$%E2....@viwinnwfe01.internal.bigpond.com> of
Sat, 19 May 2012 12:34:11 in microsoft.public.excel.worksheet.functions,
Vacuum Sealed <nood...@gmail.com> writes
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?
--
Walter Briscoe
Sat, 19 May 2012 12:34:11 in microsoft.public.excel.worksheet.functions,
Vacuum Sealed <nood...@gmail.com> writes
>Walter
>
>Check your other post
>
>:)
I must apologise for sending my posting twice. My software did not
>
>Check your other post
>
>:)
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?
--
Walter Briscoe
0 new messages