ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Wow, never seen this error before (Excel science experiment) (https://www.excelbanter.com/excel-worksheet-functions/117864-wow-never-seen-error-before-excel-science-experiment.html)

S Davis

Wow, never seen this error before (Excel science experiment)
 

Try this out for yourself in a new worksheet:

Here is the required information:

A3: Type some text
A4-A14: 2101
F4-F14: =date(2005,8,5)
I4-I14: 23974
K4-K14: 42
N3: Type some text
N4 [drag to N14]: =IF(A4=A3,N3+330,IF(M4="",(F4+330),((M4+330))))
O4 [drag to )14]:
=IF(ISERROR(IF(A4=A3,(O3+((N4-N3)*L4)),IF(((N4-TODAY())*L4)<0,"OVERDUE",((N4-TODAY())*L4)+I4))),((N4-N3)*L4),IF(A5=A4,(O4+((N5-N4)*L5)),IF(((N5-TODAY())*L5)<0,"OVERDUE",((N5-TODAY())*L5)+I5)))

Put the formula in O4 and drag down to O14 and then calculate the
workbook (F9). Alternately, keep pressing shift-F9 to watch the results
roll upward for infinity.

Strange strange strange...

Whats causing this? Is it documented? Can it be used somehow?

-Sean


Stephen Bye

Wow, never seen this error before (Excel science experiment)
 
A circular reference.
Your formula in O4 refers to the value of cell O4.

"S Davis" wrote in message
oups.com...

Try this out for yourself in a new worksheet:

Here is the required information:

A3: Type some text
A4-A14: 2101
F4-F14: =date(2005,8,5)
I4-I14: 23974
K4-K14: 42
N3: Type some text
N4 [drag to N14]: =IF(A4=A3,N3+330,IF(M4="",(F4+330),((M4+330))))
O4 [drag to )14]:
=IF(ISERROR(IF(A4=A3,(O3+((N4-N3)*L4)),IF(((N4-TODAY())*L4)<0,"OVERDUE",((N4-TODAY())*L4)+I4))),((N4-N3)*L4),IF(A5=A4,(O4+((N5-N4)*L5)),IF(((N5-TODAY())*L5)<0,"OVERDUE",((N5-TODAY())*L5)+I5)))

Put the formula in O4 and drag down to O14 and then calculate the
workbook (F9). Alternately, keep pressing shift-F9 to watch the results
roll upward for infinity.

Strange strange strange...

Whats causing this? Is it documented? Can it be used somehow?

-Sean




S Davis

Wow, never seen this error before (Excel science experiment)
 
Yeah, I guess I've just never seen excel take it and run with it like
that. The fact it works is weird.

Stephen Bye wrote:
A circular reference.
Your formula in O4 refers to the value of cell O4.

"S Davis" wrote in message
oups.com...

Try this out for yourself in a new worksheet:

Here is the required information:

A3: Type some text
A4-A14: 2101
F4-F14: =date(2005,8,5)
I4-I14: 23974
K4-K14: 42
N3: Type some text
N4 [drag to N14]: =IF(A4=A3,N3+330,IF(M4="",(F4+330),((M4+330))))
O4 [drag to )14]:
=IF(ISERROR(IF(A4=A3,(O3+((N4-N3)*L4)),IF(((N4-TODAY())*L4)<0,"OVERDUE",((N4-TODAY())*L4)+I4))),((N4-N3)*L4),IF(A5=A4,(O4+((N5-N4)*L5)),IF(((N5-TODAY())*L5)<0,"OVERDUE",((N5-TODAY())*L5)+I5)))

Put the formula in O4 and drag down to O14 and then calculate the
workbook (F9). Alternately, keep pressing shift-F9 to watch the results
roll upward for infinity.

Strange strange strange...

Whats causing this? Is it documented? Can it be used somehow?

-Sean



Elkar

Wow, never seen this error before (Excel science experiment)
 
You have "Iterations" turned on.

TOOLS
OPTIONS
CALCULATIONS Tab
Uncheck "Iterations"
OK

Now Excel should behave "normally" towards Circular References.

HTH,
Elkar


"S Davis" wrote:

Yeah, I guess I've just never seen excel take it and run with it like
that. The fact it works is weird.

Stephen Bye wrote:
A circular reference.
Your formula in O4 refers to the value of cell O4.

"S Davis" wrote in message
oups.com...

Try this out for yourself in a new worksheet:

Here is the required information:

A3: Type some text
A4-A14: 2101
F4-F14: =date(2005,8,5)
I4-I14: 23974
K4-K14: 42
N3: Type some text
N4 [drag to N14]: =IF(A4=A3,N3+330,IF(M4="",(F4+330),((M4+330))))
O4 [drag to )14]:
=IF(ISERROR(IF(A4=A3,(O3+((N4-N3)*L4)),IF(((N4-TODAY())*L4)<0,"OVERDUE",((N4-TODAY())*L4)+I4))),((N4-N3)*L4),IF(A5=A4,(O4+((N5-N4)*L5)),IF(((N5-TODAY())*L5)<0,"OVERDUE",((N5-TODAY())*L5)+I5)))

Put the formula in O4 and drag down to O14 and then calculate the
workbook (F9). Alternately, keep pressing shift-F9 to watch the results
roll upward for infinity.

Strange strange strange...

Whats causing this? Is it documented? Can it be used somehow?

-Sean





All times are GMT +1. The time now is 07:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com