Fixed-length dynamic array: rolling computation

[marcobm]

Hello to everyone,
I am kinda new to VBA, and I cannot figure out how to implement a rolling computation. Here's an example of my data:

A C D E F G H

A		%	theory	practice	error
0.0052		0.05	-0.0193	-0.0198	2.96E-07		a	0.0000
0.0039		0.10	-0.0135	-0.0136	9.59E-09		b	119.5765
-0.0180		0.15	-0.0101	-0.0103	5.76E-08		c	103.2256
0.0175		0.20	-0.0077	-0.0086	9.61E-07
0.0064		0.25	-0.0058	-0.0061	7.24E-08		SSE	3.04E-05
0.0063		0.30	-0.0043	-0.0044	1.66E-08
0.0028		0.35	-0.0030	-0.0028	4.99E-08
-0.0022		0.40	-0.0019	-0.0015	1.57E-07
-0.0038		0.45	-0.0009	-0.0005	1.70E-07
0.0146		0.50	0.0000	0.0006	3.67E-07
0.0049		0.55	0.0010	0.0026	2.42E-06
0.0058		0.60	0.0022	0.0041	3.93E-06
0.0088		0.65	0.0035	0.0058	5.34E-06
-0.0216		0.70	0.0049	0.0073	5.31E-06
0.0260		0.75	0.0067	0.0088	4.39E-06
0.0025		0.80	0.0089	0.0106	2.82E-06
0.0002		0.85	0.0117	0.0134	2.86E-06
0.0039		0.90	0.0156	0.0166	1.06E-06
-0.0039		0.95	0.0223	0.0220	1.13E-07

<tbody>
</tbody>

In column A, I have some numbers. In columns C, some percentiles. In column D, some values, computed according to the parameters a,b,c. In columns E, I take the x percentile of the data in column A, where the x percentile is contained in column C. In column F, I compute the squared difference between the values in columns D and E, and the sum of such squared differences is reported as SSE. Everything is done because, with the solver, I need to minimize SSE changing a,b,c.

The problem arises because the data in column A are around 500, and I need to compute the possible values for a,b,c 250 times by taking a sample of 250 data at a time.

To better explain, the first simulation should do the following:
- in column E, take the range A1:A250 and compute the values corresponding to the percentiles contained in column C;
- then, activate the solver (minimize SSE, changing a,b,c, constraitns: b>0,c>0), which will change the values for a,b,c;
- copy the values of a,b,c somewhere on the right.

The second simulation should, considering the range A2:A251 instead, perform the same tasks.

I call it fixed-length dynamic array because the array is always 250-data long, but the reference data shift by one element dynamically. In the end, my aim is to have a series of 250 simulations of the values of a,b,c, each computed on a different sample.

I have tried implementing this procedure with a macro, but I could not make the "rolling window" work.

Any help for implementing this procedure in VBA?

Thank you very, very much!!

Cross-posted to:

http://stackoverflow.com/questions/15444359/fixed-length-dynamic-array

http://www.excelforum.com/excel-pro...olling-computation.html?p=3160829#post3160829

http://www.ozgrid.com/forum/showthread.php?t=176356&p=654298#post654298

http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/a9e6804b-daa7-479c-aedd-b5a98d9db603

 

[AAE]

If you are going to Cross Post, then includes to all forums where you've posted this question.
Cross posted at Ozgrid

 

[marcobm]

Issue settled, in some way and without VBA!