Home |
Search |
Today's Posts |
#1
|
|||
|
|||
looping through a set of calculations
Hi,
I had posted this question in the General section but thought that it may be more appropriate here ... I have a number of financial calculations that need to be repeated for a list of dates where the result is dependent on the input date. I need the results form all these dates for other calculations in the spreadsheet. I have set the calculation to manual since the spread sheet is quite large and use F9 to recalculate. I used the Data - Table function to repeat the calculations for the set of dates. However, the Table does not recalculate when I hit F9. The bottom left portion of the Excel window says "TABLE" when I hit F9, which I guess means that the Table has not re-calculated. I have to go the the sheet that contains the Table and hit Shift-F9 to force the Table to recalculate. How can I force the Table to recalculate when I hit F9, or is there a better way to perform a repeating loop in Excel? Thanks Anand |
#2
|
|||
|
|||
Hi Anand,
It ought to work that way and it's a good way to do it. Are you sure the data in your table depends on something that has been changed? Do you not have any User Defined Functions that take data from sheets directly instead of via arguments? Sometimes (but very, very rarely) Excel loses track of dependencies. You can rebuild the dependency tree by pressing CTRL+ALT+SHIFT+F9. -- Kind regards, Niek Otten Microsoft MVP - Excel "Anand" wrote in message ... Hi, I had posted this question in the General section but thought that it may be more appropriate here ... I have a number of financial calculations that need to be repeated for a list of dates where the result is dependent on the input date. I need the results form all these dates for other calculations in the spreadsheet. I have set the calculation to manual since the spread sheet is quite large and use F9 to recalculate. I used the Data - Table function to repeat the calculations for the set of dates. However, the Table does not recalculate when I hit F9. The bottom left portion of the Excel window says "TABLE" when I hit F9, which I guess means that the Table has not re-calculated. I have to go the the sheet that contains the Table and hit Shift-F9 to force the Table to recalculate. How can I force the Table to recalculate when I hit F9, or is there a better way to perform a repeating loop in Excel? Thanks Anand |
#3
|
|||
|
|||
Niek:
Thanks for you quick response. You were right, it is working. But it does seem slower... when I hit F9, I first see "Calculating xx%" in the bottom left of the screen, then it displays "Table: 1" for quite some time after which it displays "iter x" and then finally "Ready" When I saw the "Table 1" displayed for some time, I thought that the program had got stuck at that point. Does the Data table make the calculations slower than if I physically copied the calculation section of the multiple times? Thanks, Anand "Niek Otten" wrote: Hi Anand, It ought to work that way and it's a good way to do it. Are you sure the data in your table depends on something that has been changed? Do you not have any User Defined Functions that take data from sheets directly instead of via arguments? Sometimes (but very, very rarely) Excel loses track of dependencies. You can rebuild the dependency tree by pressing CTRL+ALT+SHIFT+F9. -- Kind regards, Niek Otten Microsoft MVP - Excel "Anand" wrote in message ... Hi, I had posted this question in the General section but thought that it may be more appropriate here ... I have a number of financial calculations that need to be repeated for a list of dates where the result is dependent on the input date. I need the results form all these dates for other calculations in the spreadsheet. I have set the calculation to manual since the spread sheet is quite large and use F9 to recalculate. I used the Data - Table function to repeat the calculations for the set of dates. However, the Table does not recalculate when I hit F9. The bottom left portion of the Excel window says "TABLE" when I hit F9, which I guess means that the Table has not re-calculated. I have to go the the sheet that contains the Table and hit Shift-F9 to force the Table to recalculate. How can I force the Table to recalculate when I hit F9, or is there a better way to perform a repeating loop in Excel? Thanks Anand |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repeat calculations | Excel Worksheet Functions | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
looping question | New Users to Excel | |||
How do I set up a standard form showing incentive calculations | Excel Discussion (Misc queries) | |||
time interval calculations in excel | Excel Discussion (Misc queries) |