Can in Array Calculations replace my loop

[GeeWhiz7]

Hi folks,

I'm not really sure if this is possible, or maybe it is, but more than I should be asking the forum for?
At a minimum, if its not doable, at lease someone can get the useful formulas for adjustable average and standard deviation from cells E4 and F4.

So,
I currently have a worksheet that takes measured values for a process and a residual, then it

• Calculates a ratio of the two
• An average of the ratio defined by a user input and
• The standard deviation of that same average ratio given the user input.

Actually there are a lot of other calculations, but I'm trying to simplify so I can clearly state the task I'm looking at.

Right now I use a loop procedure to change the input value (E4 in mini-sheet) and record the outcomes which for 6 months of run data and about 1200 individual processes/residuals, among other things, takes a little while.
I am okay with arrays, but don't even know how to approach calculating only in array and not in the spreadsheet one at a time.

Goal: To do this all in array.

I think I can figure out how to do simple math +, -, /, x inside an array, but have no idea how to go about doing the formulas below.

• The average calculation formula =AVERAGE(OFFSET(D4,0,0,-(MIN(ROW()-ROW($D$3)+1,$E$1)),1))
• The standard deviation formula = IFERROR(STDEV.S(OFFSET(D4,0,0,-(MIN(ROW()-ROW($D$3)+1,$E$1)),1)),"")
• You can see in the manual example what these are doing, basically allowing a calculation of average or standard deviation when the date approaches less than the user input #.

Thanks for any guidance you can provide, in particular to handling the formulas above when going from input array to output array.

Example mini-sheet showing the data and calculations:

ArrayWorking.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L
1				Input (x)	4
2
3	Date	A_Process	A_Residual	Ratio	Avg. Ratio	StD Avg Ratio
4	44746	89.74	3.74	23.97	23.97
5	44745	99.81	3.76	26.51	25.24	1.80
6	44744	4.74	3.57	1.33	17.27	13.87
7	44743	50.18	3.67	13.69	16.37	11.46
8	44742	42.20	3.65	11.56	13.27	10.35
9	44741	25.70	3.55	7.24	8.45	5.46
10	44740	10.68	3.59	2.98	8.87	4.76
11	44739	48.78	3.66	13.32	8.77	4.63
12	44738	44.18	3.65	12.09	8.91	4.75
13	44737	43.31	3.65	11.86	10.06	4.77
14	44736	15.86	3.60	4.41	10.42	4.06
15	44735	61.96	3.69	16.80	11.29	5.12
16	44734	64.22	3.69	17.39	12.61	6.00
17	44733	21.97	3.61	6.09	11.17	6.88
18	44732	57.24	3.68	15.56	13.96	5.30
19	44731	71.39	3.71	19.26	14.57	5.85
20	44730	14.32	3.59	3.98	11.22	7.35
21	44729	87.23	3.74	23.33	15.53	8.33
22	44728	6.25	3.58	1.75	12.08	10.81
23	44727	39.13	3.64	10.74	9.95	9.70
24
25	Desired result in-array only to then be either saved to external file or put in another sheet for record.
26	Date	A_Process	A_Residual	Ratio	Avg. Ratio 4	Avg. Ratio 6	Avg. Ratio 8	Avg. Ratio 10	StD Avg Ratio 4	StD Avg Ratio 6	StD Avg Ratio 8	StD Avg Ratio 10
27	44746	89.74	3.74	23.97	23.97	12.15	12.75	14.08
28	44745	99.81	3.76	26.51	25.24	20.41	17.26	15.65	1.80	1.80	1.80	1.80
29	44744	4.74	3.57	1.33	17.27	17.27	12.86	13.09	13.87	13.87	13.87	13.87
30	44743	50.18	3.67	13.69	16.37	16.37	15.25	14.47	11.46	11.46	11.46	11.46
31	44742	42.20	3.65	11.56	13.27	15.41	15.41	12.79	10.35	10.16	10.16	10.16
32	44741	25.70	3.55	7.24	8.45	14.05	14.05	13.58	5.46	9.68	9.68	9.68
33	44740	10.68	3.59	2.98	8.87	10.55	12.47	12.47	4.76	9.16	9.78	9.78
34	44739	48.78	3.66	13.32	8.77	8.35	12.57	12.57	4.63	5.35	9.06	9.06
35	44738	44.18	3.65	12.09	8.91	10.15	11.09	12.52	4.75	4.20	7.81	8.47
36	44737	43.31	3.65	11.86	10.06	9.84	9.26	12.46	4.77	3.95	4.82	7.99
37	44736	15.86	3.60	4.41	10.42	8.65	9.64	10.50	4.06	4.38	4.18	7.22
38	44735	61.96	3.69	16.80	11.29	10.24	10.03	9.53	5.12	5.39	4.72	5.19
39	44734	64.22	3.69	17.39	12.61	12.64	10.76	11.13	6.00	4.67	5.39	4.84
40	44733	21.97	3.61	6.09	11.17	11.44	10.62	10.37	6.88	5.34	5.51	4.99
41	44732	57.24	3.68	15.56	13.96	12.02	12.19	10.77	5.30	5.61	4.76	5.25
42	44731	71.39	3.71	19.26	14.57	13.25	12.93	11.97	5.85	6.33	5.38	5.71
43	44730	14.32	3.59	3.98	11.22	13.18	11.92	12.08	7.35	6.45	6.26	5.54
44	44729	87.23	3.74	23.33	15.53	14.27	13.35	13.08	8.33	7.63	7.44	6.59
45	44728	6.25	3.58	1.75	12.08	11.66	13.02	12.04	10.81	8.91	7.94	7.51
46	44727	39.13	3.64	10.74	9.95	12.44	12.26	11.93	9.70	8.53	7.82	7.52
Sheet1

Cell Formulas
Range	Formula
D4:D23,D27:D46	D4	=B4/C4
E4:E23	E4	=AVERAGE(OFFSET(D4,0,0,-(MIN(ROW()-ROW($D$3)+1,$E$1)),1))
F4:F23	F4	=IFERROR(STDEV.S(OFFSET(D4,0,0,-(MIN(ROW()-ROW($D$3)+1,$E$1)),1)),"")
A4,A27	A4	=TODAY()
A5:A23,A28:A46	A5	=A4-1
E27:E46	E27	=AVERAGE(OFFSET(D27,0,0,-(MIN(ROW()-ROW($D$3)+1,4)),1))
F27:F46	F27	=AVERAGE(OFFSET(D27,0,0,-(MIN(ROW()-ROW($D$3)+1,6)),1))
G27:G46	G27	=AVERAGE(OFFSET(D27,0,0,-(MIN(ROW()-ROW($D$3)+1,8)),1))
H27:H46	H27	=AVERAGE(OFFSET(D27,0,0,-(MIN(ROW()-ROW($D$3)+1,10)),1))
I27:I46	I27	=IFERROR(STDEV.S(OFFSET(D27,0,0,-(MIN(ROW()-ROW($D$26)+1,4)),1)),"")
J27:J46	J27	=IFERROR(STDEV.S(OFFSET(D27,0,0,-(MIN(ROW()-ROW($D$26)+1,6)),1)),"")
K27:K46	K27	=IFERROR(STDEV.S(OFFSET(D27,0,0,-(MIN(ROW()-ROW($D$26)+1,8)),1)),"")
L27:L46	L27	=IFERROR(STDEV.S(OFFSET(D27,0,0,-(MIN(ROW()-ROW($D$26)+1,10)),1)),"")

 

[offthelip]

I can't seem to work exactly what you formulae are doing but here is some simple code to work out the average and the standard deviation for values in D4 to d23:

Sub test()
lastrow = 23
inarr = Range(Cells(1, 4), Cells(lastrow, 4)) ' pick up all of column D
avarr = Range(Cells(1, 11), Cells(lastrow, 11)) ' pick up all of column K for averages
stdarr = Range(Cells(1, 12), Cells(lastrow, 12)) ' pick up all of column L for stdev
sumr = 0
For i = 4 To lastrow
   sumr = sumr + inarr(i, 1)
   avarr(i, 1) = sumr / (i - 3)
   deltasq = 0
   For j = 4 To i
     deltasq = deltasq + (inarr(j, 1) - avarr(i, 1)) * (inarr(j, 1) - avarr(i, 1))
   Next j

   stdarr(i, 1) = Sqr(deltasq / (i - 3))

 Next i
Range(Cells(1, 11), Cells(lastrow, 11)) = avarr ' write all of column K for averages
Range(Cells(1, 12), Cells(lastrow, 12)) = stdarr ' write all of column L for stdev
 
End Sub

 

[GeeWhiz7]

I can't seem to work exactly what you formulae are doing but here is some simple code to work out the average and the standard deviation for values in D4 to d23:

Sub test()
lastrow = 23
inarr = Range(Cells(1, 4), Cells(lastrow, 4)) ' pick up all of column D
avarr = Range(Cells(1, 11), Cells(lastrow, 11)) ' pick up all of column K for averages
stdarr = Range(Cells(1, 12), Cells(lastrow, 12)) ' pick up all of column L for stdev
sumr = 0
For i = 4 To lastrow
   sumr = sumr + inarr(i, 1)
   avarr(i, 1) = sumr / (i - 3)
   deltasq = 0
   For j = 4 To i
     deltasq = deltasq + (inarr(j, 1) - avarr(i, 1)) * (inarr(j, 1) - avarr(i, 1))
   Next j

   stdarr(i, 1) = Sqr(deltasq / (i - 3))

 Next i
Range(Cells(1, 11), Cells(lastrow, 11)) = avarr ' write all of column K for averages
Range(Cells(1, 12), Cells(lastrow, 12)) = stdarr ' write all of column L for stdev
 
End Sub

Hi offthelip, I think I can make this work for me. Thank you for posting this code.

Let me try it out in the morning and come back to here. One question, perhaps a dumb one...from a computation speed point if view, is it better to continue to work with one large array if it is getting bigger and bigger as long as it isn't bumping up against memory resources or is it better to work with smaller ones and occasionally write them out to a range?

 

[offthelip]

It is almost invariably best to minimise the number of times you read or write to/from the worksheet, so that means one huge array is best.

 

[GeeWhiz7]

It is almost invariably best to minimise the number of times you read or write to/from the worksheet, so that means one huge array is best.

Have been reading up on multi dimensional arrays so will try to do in one large array. Thanks for that advice and for the code for average and standard deviation. They both work nicely so I will keep building.