Problem with Data Tables

[maani]

Hello,

I have built a model which aims to calculate various information/statistics based upon 2 inputs which I can change (see 'Mov_Avg_Chart' tab cells C6 and C8). In cell C6 I have identified the moving average period that I would like, and in cell C8 the period for moving average of the gradient/standard deviation of the gradient.

I have created 4 data tables (one for annualized return, sharpe ratio,max drawdown, and annualized risk/reward). The first data table can be seen in cells AE15:AH18. In cells AE16:AE18, there are the moving average period inputs (as in cell C6) and in cells AF 16,AG16 and AH 16 are the moving average gradient inputs (as in cell C8). What I am trying to do is create a table that will allow me to state a minimum moving average period (as in C6) and a maximum moving average period (as in C6), and an increment which I would like to look at the data in. Thus, in this example, the minimum moving average period would be 20, the maximum would be 40 and the increment would be 10. Also, I want to create a minimum moving average gradient (as in C8) and a maximum moving average gradient (as in C8) and an increment figure. Thus, in this example, it would be minimum of 75, maximum of 125 and an increment of 25. The point in the example is to see which outcomes based on the inputs are the most favorable.

The trouble I am having is that I don't know how to put these as additional inputs and have them drop into the data table (in place of cells AE16 to AE18 for example). Would anyone have any ideas? Also, the automated recalculation of the data table is needlessly slowing down the spreadsheet; is there a way that the data table can be recalculated only when there are changes to the input functions? I have set the calculation method to 'automatic except data tables' because otherwise it takes 15 minutes for the file to open. Thus, the data table isn't calculating automatically. Would someone be able to help me with this?

Kind Regards,

Maani

(I have tried to zip the file and attach it but it kept giving me the upload file failed error. Thus, I have attached it below in a link)
http://www.4shared.com/file/125733535/c61f1ca8/Moving_Average_Model_August_15_2009.html

 

[maani]

Hi,

As I had no responses, I figured I would try and re-state the problem in simpler terms. I have a 2-variable data table 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 be able to help me with this?

Thanks,

Maani