PDA

View Full Version : Incremental Change in Values of Data Table



maani
08-18-2009, 05:41 AM
Hello,

I have a 2-variable data table in excel 2007 under the What-if-analysis tools. I would like to create the ability to set a minimum and maximum value for each input and the increment at which I would like to test. For example, if my minimum value for the column input is 10 and my maximum value for the column input is 50, and the increment that I select is 10, then I would like to see the 10,20,30,40,and 50 values in the column of my data table. I would like to have this ability for both the row and column inputs in the data table. I have spent the better part of the past couple of days trying to figure this out and search online but have had no luck. Would someone know how to go about doing this?

Thanks,

Maani

mdmackillop
08-18-2009, 05:49 AM
Can you post a small sample file? Use Manage Attachments in the Go Advanced reply section.

maani
08-18-2009, 09:27 AM
Hi,

Thanks for your response. Please find attached a condensed version of spreadsheet. You can find the 4 data table in cells AE15:AH37 of the 'Mov_Avg_chart' tab. Additionally, my partially constructed optimisation table is in cells A21:B27 of the 'Mov_Avg_chart' tab.

Kind Regards,

Maani

maani
08-18-2009, 11:24 AM
Hello,

I hadn't received a response, so I thought I'd add a bit more information.

The input values (cells C6 and C8) which are linked to the data table are moving average period (C6) and moving average gradient (C8). The 4 data tables are for 4 statistics that are calculated in cells AE7:AF13. I am trying to optimise the statistics based on the changing inputs (C6 and C8). For example, Data Table 1 is an optimisation for Annualized Return, where cells AE16:AE18 represent values that cell C6 would change to and cells AF15:AH15 represent values that cell C8 would change to.

I have re-attached the spreadsheet with formulas for AE16:AE18 and AF15:AH15 (linking them to the opimisation table in cells A21:B25). What I would like is to be able to change the values in the optimisation table and have it automatically adjust in the data tables. This is being done right now, but the issue is, the data tables only go 3 rows down and 3 rows across. There will be a point where the incremental variable (cells B23 and B25) will be such a number that the data tables will need to be increased greater than or smaller than 3 rows down and across.

The other issue is that my actual spreadsheet is about 6mb and has quite a bit of data, thus, I had to put the calculation method on automatic except for data tables. Otherwise, it takes a long time to open the spreadsheet and make changes to it without the data tables recalculating constantly. Would you know of a way through VBA to make the data tables only recalculate when the cells in the optimisation table are changed?

Thanks, and sorry about the lack of clarity in previous posts.

Maani