Average the difference of 2 columns w/out a 3rd column

[scrandal]

I have two columns:

A B

1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1

I need a formula that takes the difference of the two columns (1-10, 2-9, etc.) and then finds the average for all of the differences. I don't want to have a third column with the differences and then take the average of those. Any help would be much appreciated.

Thanks,
Steve

 

[Domenic]

The only way you can get those results is if the formula for D3 is confirmed with just ENTER and not CONTROL+SHIFT+ENTER. In this case, your formula would be equivalent to the following...

=SUM((0.5*(A3))+(0.25*(B3))+(0.25*(C3)))

But if I understand you correctly, this is what you'd like...

=SUM(A1:A3)*.5+SUM(B1:B3)*.25+SUM(C1:C3)*.25

Is this correct?

 

[scrandal]

I have worked on the problem a little this morning and I think I am going in the right direction, but I have a found another snag. In the formula below, the percentages I require are listed as {0.5,0.25,0.25}. I tried to replace those numbers with cell references, but without success. I need to be able to allow the user to change the percentages, without modifying the actual formula. Any ideas?

=AVERAGE(MMULT(SUBTOTAL(9,OFFSET(A1:C10,ROW(INDIRECT("1:"&ROWS(A1:C10)-2))-1,{0,1,2},3,1))*{0.5,0.25,0.25},{1;1;1}))

 

[Domenic]

Let a horizontal range of cells, let's say E1:G1, contain .5, .25, and .25, and then replace...

{0.5,0.25,0.25}

with

E1:G1

Hope this helps!

 

[scrandal]

Domenic, once again thank you for all of the help. To further my understanding of the formula you gave me, I do have a few more questions. I have highlighted the sections I don't really understand in red. When you get a chance could you explain their effect on the formula. Thanks

=AVERAGE(MMULT(SUBTOTAL(9,OFFSET(A1:C10,ROW(INDIRECT("1:"&ROWS(A1:C10)-2))-1,{0,1,2},3,1))*{0.5,0.25,0.25},{1;1;1}))

 

[Domenic]

Basically, {0,1,2} provides the column offsets from the starting reference, A1:C10. Because we have an array of numbers as the column offset, an array of references is returned.

Here, SUBTOTAL(9,OFFSET(.....)) returns an 8 row by 3 column array of values. Since we want each row summed, we exploit the way MMULT works. This can be achieved by multiplying the 8 row by 3 column array with a 3 row array of 1's. The 3 row array of 1's is provided by {1;1;1}. The result is an 8 row array, which is in turn passed to AVERAGE. To learn how matrix multiplcation works, have a look here...

Hope this helps!

 

[scrandal]

I am trying to get the value in F29 to match the value in E29. Does anybody have any idea what I am doing wrong?

SD Test 11_13.xls
	A	B	C	D	E	F
1		95%	5%	0%
2		SeriesA	SeriesB	SeriesC
3	Dec-45
4	Jan-46	1.88%	-0.34%	0.09%
5	Feb-46	2.03%	0.17%	0.09%
6	Mar-46	0.60%	-0.03%	0.10%
7	Apr-46	4.95%	-0.02%	0.09%
8	May-46	5.09%	0.19%	0.10%
9	Jun-46	-5.57%	-0.10%	0.10%
10	Jul-46	1.33%	0.50%	0.10%
11	Aug-46	4.32%	-0.04%	0.10%
12	Sep-46	5.94%	-0.33%	0.10%
13	Oct-46	0.85%	-0.33%	0.12%
14	Nov-46	1.85%	0.14%	0.11%
15	Dec-46	5.12%	-0.03%	0.11%	10.325%
16	Jan-47	6.29%	0.16%	0.13%	10.953%
17	Feb-47	1.63%	0.12%	0.10%	10.986%
18	Mar-47	-1.60%	-1.23%	0.11%	11.605%
19	Apr-47	5.03%	0.39%	0.13%	11.626%
20	May-47	-2.96%	-0.05%	0.12%	12.363%
21	Jun-47	-2.32%	0.24%	0.12%	10.714%
22	Jul-47	7.13%	0.66%	0.13%	11.684%
23	Aug-47	4.85%	0.52%	0.13%	11.778%
24	Sep-47	0.13%	-0.56%	0.12%	11.483%
25	Oct-47	-1.06%	0.30%	0.16%	11.830%
26	Nov-47	0.93%	0.05%	0.11%	11.874%
27	Dec-47	4.23%	-0.62%	0.12%	11.660%
28
29				Average	11.452%	11.0861%
30					LongHand	Formula
Sheet1

 

[scrandal]

I am really stumped on this one. Does anyone at least have any ideas?

 

[Domenic]

Unfortuntately, I'm not aware of a formula which will return the desired result. However, while I'm not a statistician, I wonder whether you're actually looking for something like the following...

=STDEV(MMULT(SUBTOTAL(9,OFFSET($B$4:$D$27,ROW(INDIRECT("1:"&ROWS($B$4:$D$27)-11))-1,{0,1,2},12,1))*($B$1:$D$1),{1;1;1}))

This would be the equivalent to...

E15, copied down:

=($B$1*(SUM(B4:B15)))+($C$1*(SUM(C4:C15)))+($D$1*(SUM(D4:D15)))

...with the standard deviation taken from those range of cells...

=STDEV(E15:E27)

Any chance this what you're looking for?

 

[scrandal]

Thanks for the quick response, but it isn't exactly what I am lookinbg for. I need to take the AVERAGE of the STDEV's of the cells. Also starting in E15, the formula should not read SUM(B4:B15). I need to take the percentage (B1) times each cell in the the range B4:B15 individually. That's why in my example, the formulas in E15:E27 are set as an array. I am sorry to be such a pain on this, but I feel like I am very close to finding a solution to my issue. Any ideas?