Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spurious Formula Omits Adjacent Cells
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spurious Formula Omits Adjacent Cells
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Omits Adjacent Cells | Excel Worksheet Functions | |||
How do I make a rank equation that omits particular cells? | Excel Discussion (Misc queries) | |||
Formula omits adjacent cells | Excel Discussion (Misc queries) | |||
Formula to sum non-adjacent cells | Excel Programming | |||
Formula omits adjacent cells error message | Excel Discussion (Misc queries) |