Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I compensate this...pleass contribute...
Try this drill.
< i draft this for 2 hours ....its best when u place a conditional format on all cells : cell value is equal "=$a$1" Open excel, open a virgin workbook *Book1.xls* .. On sheet1,make a clockwise series of *=(next cell along sheet edges)* a) put any number on A1 b) on B1, type *=A1*, copy B1, paste until IV1 c) on IV2,type *=IV1*, copy and paste down to IV65536 d) on IU65536,type *=IV65536*, copy and paste towards A65536 e) on A65535,type *=A65536*, copy and paste up to A2....ctrl+save as: with any file name... then make a counterclockwise series of *=(next cell along another range edges)* choose an inner-range like C3:IT65534 <3 cells o/s from the sheet edges a) put any different number on C3 and place *=(next cell)* formula in a counter-clockwise direction until you stop on D3...ctrl+s ---as you see there are no circular formulation *Yet* from the above... then a)go to toolsoptionscalculation tab click *automatic* click *iteration* put value of 1 (e.g.) . b) on A1, put *=A2* <now theres a circular clockwise formula wave along the sheet edges. c) on C3, put *=A1 <now there's the counterclockwise formula on the inner range with cell values that must be equal to the value on the *edges*. But this innerrange is not in a circular formula with itself.. * observe the result along the bottom-right corner of the innerrange,you can see the start of *iterated result(s)*... *Exit excel with file save as something like "DIR1/step1.xls. *Re-open Excel, open the saved file, <the differences are still there. Now i know that iteration mode governs the basic automatic mode. Then try to file save_as something like "*DIR2/step1.xls a) on A1, put any number <delete *=A2*, now there's no circular formulation. *All values now are equal to A1* b) then again on A1, put *=A2* <now there's a circular clockwise formula activated along the sheet edges. *You can see that max&min values are All-Equal in an instant!!! But when you try this... a) on C3, place any DIFFERENT number <delete the *=A1*. *u see the non-circular innerrange of cells react very fast with equal values as in C3.. b) then again on C3, put *=A1* You can see the same results from the first procedure. For bothe files, having same filename, and residing on different directory, if i close and open them both at the same time under one window...which one will someone select? *I may also forget which one is correct! Assuming that if the *last formulated cell *D3* is linked on other formulated report sheet of the same workbook...its a nightmare.. I may have numbered results without *errors* yet Now i'm not sure which one from the two files is the Properly Entered iteration formula with *correctly SAVED* result. Maybe, these automatically saved sheets can be interpreted as *same* for all *function and template_sheet* users <who have no knowledge of which cell has to be edited in the last place If this drill reacts differently with yours, then i may need to dump this very slow and dummy PC or claim for my stupidity.<g Thanks for any advices. -- regards, driller ***** - dive with Jonathan Seagull |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I compensate this...pleass contribute...
sorry for the typo imean "...please contribute..."
-- regards, driller ***** - dive with Jonathan Seagull "driller" wrote: Try this drill. < i draft this for 2 hours ...its best when u place a conditional format on all cells : cell value is equal "=$a$1" Open excel, open a virgin workbook *Book1.xls* .. On sheet1,make a clockwise series of *=(next cell along sheet edges)* a) put any number on A1 b) on B1, type *=A1*, copy B1, paste until IV1 c) on IV2,type *=IV1*, copy and paste down to IV65536 d) on IU65536,type *=IV65536*, copy and paste towards A65536 e) on A65535,type *=A65536*, copy and paste up to A2....ctrl+save as: with any file name... then make a counterclockwise series of *=(next cell along another range edges)* choose an inner-range like C3:IT65534 <3 cells o/s from the sheet edges a) put any different number on C3 and place *=(next cell)* formula in a counter-clockwise direction until you stop on D3...ctrl+s ---as you see there are no circular formulation *Yet* from the above... then a)go to toolsoptionscalculation tab click *automatic* click *iteration* put value of 1 (e.g.) . b) on A1, put *=A2* <now theres a circular clockwise formula wave along the sheet edges. c) on C3, put *=A1 <now there's the counterclockwise formula on the inner range with cell values that must be equal to the value on the *edges*. But this innerrange is not in a circular formula with itself.. * observe the result along the bottom-right corner of the innerrange,you can see the start of *iterated result(s)*... *Exit excel with file save as something like "DIR1/step1.xls. *Re-open Excel, open the saved file, <the differences are still there. Now i know that iteration mode governs the basic automatic mode. Then try to file save_as something like "*DIR2/step1.xls a) on A1, put any number <delete *=A2*, now there's no circular formulation. *All values now are equal to A1* b) then again on A1, put *=A2* <now there's a circular clockwise formula activated along the sheet edges. *You can see that max&min values are All-Equal in an instant!!! But when you try this... a) on C3, place any DIFFERENT number <delete the *=A1*. *u see the non-circular innerrange of cells react very fast with equal values as in C3.. b) then again on C3, put *=A1* You can see the same results from the first procedure. For bothe files, having same filename, and residing on different directory, if i close and open them both at the same time under one window...which one will someone select? *I may also forget which one is correct! Assuming that if the *last formulated cell *D3* is linked on other formulated report sheet of the same workbook...its a nightmare.. I may have numbered results without *errors* yet Now i'm not sure which one from the two files is the Properly Entered iteration formula with *correctly SAVED* result. Maybe, these automatically saved sheets can be interpreted as *same* for all *function and template_sheet* users <who have no knowledge of which cell has to be edited in the last place If this drill reacts differently with yours, then i may need to dump this very slow and dummy PC or claim for my stupidity.<g Thanks for any advices. -- regards, driller ***** - dive with Jonathan Seagull |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I compensate this...please contribute...
Dear friends,
*Here's the record for your a possible advice..* with regards to the inquiry mentioned on the first line-post... ---------- Option Explicit Sub MacroTestmyAutoIteration() ActiveWindow.SplitRow = 19.7647058823529 ActiveWindow.Panes(3).Activate Range("A29").Select Selection.End(xlDown).Select ActiveWindow.Zoom = 100 Selection.End(xlToRight).Select Selection.End(xlToLeft).Select ActiveWindow.SplitColumn = 5 ActiveWindow.Panes(4).Activate Range("G65536").Select Selection.End(xlToRight).Select ActiveWindow.Panes(1).Activate Range("A1").Select ActiveCell.FormulaR1C1 = "7" Range("B1").Select ActiveCell.FormulaR1C1 = "=RC[-1]" Range("B1").Select Selection.Copy Range("B1:IV1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.Panes(2).Activate Range("IV2").Select ActiveCell.FormulaR1C1 = "=R[-1]C" Range("IV2").Select Selection.Copy Range("IV2:IV65536").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.Panes(4).Activate Range("IU65536").Select ActiveCell.FormulaR1C1 = "=RC[1]" Selection.Copy Range("A65536:IU65536").Select Range("IU65536").Activate ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.Panes(3).Activate Range("A65535").Select ActiveCell.FormulaR1C1 = "=R[1]C" Range("A65535").Select Selection.Copy Range("A2:A65535").Select Range("A65535").Activate ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.Panes(1).Activate Range("C3").Select ActiveCell.FormulaR1C1 = "4" Range("C4").Select ActiveCell.FormulaR1C1 = "=R[-1]C" Range("C4").Select Selection.Copy Range("C4:C65534").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.Panes(3).Activate Range("D65534").Select ActiveCell.FormulaR1C1 = "=RC[-1]" Range("D65534").Select ActiveCell.FormulaR1C1 = "=RC[-1]" Range("D65534").Select Selection.Copy Range("D65534:IT65534").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.Panes(4).Activate Range("IT65533").Select ActiveCell.FormulaR1C1 = "=R[1]C" Range("IT65533").Select Selection.Copy Range("IT3:IT65533").Select Range("IT65533").Activate ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.Panes(2).Activate Range("IS3").Select ActiveCell.FormulaR1C1 = "=RC[1]" Range("IS3").Select Selection.Cut Application.CutCopyMode = False Selection.Copy Range("D3:IS3").Select Range("IS3").Activate ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.Panes(1).Activate Cells.Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=$A$1" Selection.FormatConditions(1).Interior.ColorIndex = 44 Range("C3:IT65534").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=$A$1" Selection.FormatConditions(1).Interior.ColorIndex = 44 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=$C$3" Selection.FormatConditions(2).Font.ColorIndex = 2 Selection.FormatConditions(2).Interior.ColorIndex = 3 Cells.Select Selection.Interior.ColorIndex = xlNone ActiveWindow.Panes(1).Activate Range("C3").Select ActiveCell.FormulaR1C1 = "=R[65533]C[253]" Range("C4").Select With Application .Iteration = True .MaxIterations = 1 .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False ActiveWorkbook.Save Range("A1").Select ActiveCell.FormulaR1C1 = "=R[1]C" Range("A1").Select ActiveCell.FormulaR1C1 = "100" Range("A2").Select With Application .MaxIterations = 10 .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Range("A1").Select ActiveCell.FormulaR1C1 = "90" Range("A1").Select ActiveCell.FormulaR1C1 = "=R[1]C" Range("C3").Select ActiveCell.FormulaR1C1 = "200" Range("C3").Select ActiveCell.FormulaR1C1 = "=R[65533]C[253]" Range("C4").Select ActiveWindow.Panes(4).Activate Range("IT65525:IT65534").Select Range("IT65534").Activate Calculate ActiveWindow.Panes(1).Activate Range("C3").Select ActiveCell.FormulaR1C1 = "500" Range("C3").Select ActiveCell.FormulaR1C1 = "=R[-2]C[-2]" Range("C3").Select With Application .MaxIterations = 1 .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Calculate ActiveWorkbook.Save End Sub --------- hope u have some fun! <g -- regards, driller ***** - dive with Jonathan Seagull "driller" wrote: Try this drill. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|