Subtracting the Average with Array Formulas

[IlanK]

Hello Excel friends!

Here's my issue.

I have two rows:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


With a single formula, it's easy to take A1, subtract the average of A1 and A2, and then square that value. The complexity is that I would like to use a single cell/formula to take the sum of those values for all four columns. I do not want to simply write the formula multiple times with +'s in between since there are (in reality) quite a lot of columns.

Any thoughts?

Thanks,

Ilan

 

[Comfy]

I'm not entirely sure what you're asking.

This?

Excel 2010
	A	B	C	D	E	F	G
1	1	2	1	2	0		1.5
2	5	4	3	0	0		3.0
3
4	-0.5	0.5	-0.5	0.5	0
5	2	1	0	-3	0
Sheet1

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(A1:D1-AVERAGE(A1:D1))
E4	=SUM(A4:D4)
A4	=A1-$G1
A5	=A2-$G2

Column G and Rows 4 & 5 are for validation purposes.

 

[IlanK]

Here is what I am trying to do:

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Row 1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Row 2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1.33[/TD]
[TD="class: xl65, align: right"]0.33[/TD]
[TD="class: xl65, align: right"]0.50[/TD]
[TD="class: xl65, align: right"]1.00[/TD]
[TD][/TD]
[TD]
(Row 1 - Average)^2/average[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.166667[/TD]
[TD][/TD]
[TD]Value I want[/TD]
[/TR]
</tbody>[/TABLE]

 

[Comfy]

This might work???

Excel 2010
	A	B	C	D
1	1	2	1	2
2	5	4	3	0
3
4				3.166667
Sheet1

Cell Formulas
Range	Formula
D4	=SUMPRODUCT((A1:D1-((A1:D1+A2:D2)/2))^2/((A1:D1+A2:D2)/2))

Not 100% confident though.

 

[IlanK]

This might work???

Excel 2010

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.166667[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=SUMPRODUCT((A1:D1-((A1:D1+A2:D2)/2))^2/((A1:D1+A2:D2)/2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Not 100% confident though.

Hmm... I'm confused. Doesn't the sumproduct formula require two arrays?

 

[Comfy]

Hmm... I'm confused. Doesn't the sumproduct formula require two arrays?

I'm probably not the best person to answer this but the formula contains multiple arrays.

The main two being:

(A1:D1-((A1:D1+A2:D2)/2))^2

and

((A1:D1+A2:D2)/2)

But there are arrays within these, for example A1:D1+A2:D2.

 

[IlanK]

I'm probably not the best person to answer this but the formula contains multiple arrays.

The main two being:

(A1:D1-((A1:D1+A2:D2)/2))^2

and

((A1:D1+A2:D2)/2)

But there are arrays within these, for example A1:D1+A2:D2.

Unfortunately this formula does not work as the sumproduct formula must be in the format =sumproduct(array 1 [comma] array 2).

 

[Comfy]

Unfortunately this formula does not work as the sumproduct formula must be in the format =sumproduct(array 1 [comma] array 2).

It doesn't have to be and you are referring to my example.

See Post 4 for the answer.

 

[IlanK]

It doesn't have to be and you are referring to my example.

See Post 4 for the answer.

My mistake, sorry! That works great. Thanks so much!

Ilan