Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with formula that unexpectedly returns an answer
Hi, hope someone can help. I have a template spreadsheet that contains the
following formula =IF(AND(ISNUMBER(L23),ISNUMBER(M23),ISNUMBER(L24), ISNUMBER(M24)),(L23+M23+L24+M24)/2,IF(AND(ISNUMBER(L23),ISNUMBER(M23)),L23+M23,"Not calc.")) L23 and L24 are dates, M23 and M24 are times. It's meant to add together the two sets of date and time fields if they are all present, and calculate their average. If only the first date and time are present, it should sum them. It works fine. However, on a recent occasion a user accidentally deleted row 24 and managed to change the formula to =IF(AND(ISNUMBER(L23),ISNUMBER(M23),ISNUMBER(#REF! ),ISNUMBER(#REF!)),(L23+M23+#REF!+#REF!)/2,IF(AND(ISNUMBER(L23),ISNUMBER(M23)),L23+M23,"Not calc.")) Surprisingly (to me) this returned the sum of L23 and M23, and gave the user an apparently plausible answer that was not noticed for some time. This seems to be because the formula ISNUMBER(#REF!) does not return an error or #REF!, just "False". Please don't suggest protecting the worksheet, because the users have to be able to delete empty rows. (e.g. if rows 23 and 24 had been both empty, they would have been deleted). Can anyone suggest a way to rework the overall formula so that if such deletion occurs it is obvious that something has gone wrong? Similar formulae are dotted all over the template, so something simple would be appreciated! Many thanks, Geoff. |
#2
|
|||
|
|||
try
=if(or(iserror(L23),iserror(M23),iserror(L24),iser ror(M24)),"error",IF(AND(ISNUMBER(L23),ISNUMBER(M2 3),ISNUMBER(L24),ISNUMBER(M24)),(L23+M23+L24+M24)/2,IF(AND(ISNUMBER(L23),ISNUMBER(M23)),L23+M23,"Not calc."))) "Geoff C" wrote: Hi, hope someone can help. I have a template spreadsheet that contains the following formula =IF(AND(ISNUMBER(L23),ISNUMBER(M23),ISNUMBER(L24), ISNUMBER(M24)),(L23+M23+L24+M24)/2,IF(AND(ISNUMBER(L23),ISNUMBER(M23)),L23+M23,"Not calc.")) L23 and L24 are dates, M23 and M24 are times. It's meant to add together the two sets of date and time fields if they are all present, and calculate their average. If only the first date and time are present, it should sum them. It works fine. However, on a recent occasion a user accidentally deleted row 24 and managed to change the formula to =IF(AND(ISNUMBER(L23),ISNUMBER(M23),ISNUMBER(#REF! ),ISNUMBER(#REF!)),(L23+M23+#REF!+#REF!)/2,IF(AND(ISNUMBER(L23),ISNUMBER(M23)),L23+M23,"Not calc.")) Surprisingly (to me) this returned the sum of L23 and M23, and gave the user an apparently plausible answer that was not noticed for some time. This seems to be because the formula ISNUMBER(#REF!) does not return an error or #REF!, just "False". Please don't suggest protecting the worksheet, because the users have to be able to delete empty rows. (e.g. if rows 23 and 24 had been both empty, they would have been deleted). Can anyone suggest a way to rework the overall formula so that if such deletion occurs it is obvious that something has gone wrong? Similar formulae are dotted all over the template, so something simple would be appreciated! Many thanks, Geoff. |
#3
|
|||
|
|||
Thanks!
"bj" wrote: try =if(or(iserror(L23),iserror(M23),iserror(L24),iser ror(M24)),"error",IF(AND(ISNUMBER(L23),ISNUMBER(M2 3),ISNUMBER(L24),ISNUMBER(M24)),(L23+M23+L24+M24)/2,IF(AND(ISNUMBER(L23),ISNUMBER(M23)),L23+M23,"Not calc."))) "Geoff C" wrote: Hi, hope someone can help. I have a template spreadsheet that contains the following formula =IF(AND(ISNUMBER(L23),ISNUMBER(M23),ISNUMBER(L24), ISNUMBER(M24)),(L23+M23+L24+M24)/2,IF(AND(ISNUMBER(L23),ISNUMBER(M23)),L23+M23,"Not calc.")) L23 and L24 are dates, M23 and M24 are times. It's meant to add together the two sets of date and time fields if they are all present, and calculate their average. If only the first date and time are present, it should sum them. It works fine. However, on a recent occasion a user accidentally deleted row 24 and managed to change the formula to =IF(AND(ISNUMBER(L23),ISNUMBER(M23),ISNUMBER(#REF! ),ISNUMBER(#REF!)),(L23+M23+#REF!+#REF!)/2,IF(AND(ISNUMBER(L23),ISNUMBER(M23)),L23+M23,"Not calc.")) Surprisingly (to me) this returned the sum of L23 and M23, and gave the user an apparently plausible answer that was not noticed for some time. This seems to be because the formula ISNUMBER(#REF!) does not return an error or #REF!, just "False". Please don't suggest protecting the worksheet, because the users have to be able to delete empty rows. (e.g. if rows 23 and 24 had been both empty, they would have been deleted). Can anyone suggest a way to rework the overall formula so that if such deletion occurs it is obvious that something has gone wrong? Similar formulae are dotted all over the template, so something simple would be appreciated! Many thanks, Geoff. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) |