| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
| Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
| Running Excel 2000 VBA Application on Excel 2003 | Excel Worksheet Functions | |||
| Need suggestions for some uses of Ms Excel | Excel Discussion (Misc queries) | |||
| Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Linear Mode
