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 |
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 |
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 |
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