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; "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. ;) -- Walter Briscoe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spurious Formula Omits Adjacent Cells
Walter
Check your other post :) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spurious Formula Omits Adjacent Cells
In message . com of
Sat, 19 May 2012 12:34:11 in microsoft.public.excel.worksheet.functions, Vacuum Sealed writes Walter Check your other post :) 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? -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Omits Adjacent Cells | Excel Worksheet Functions | |||
Spurious 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 omits adjacent cells error message | Excel Discussion (Misc queries) |