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.

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.

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.