Subtracting the Average with Array Formulas

[IlanK]

Hello Excel friends!

Here's my issue.

I have two rows:

	A	B	C	D
1	1	2	1	2
2	5	4	3	0

<tbody>
</tbody>

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:

1	2	1	2		Row 1
5	4	3	0		Row 2
3	3	2	1		Average
1.33	0.33	0.50	1.00		(Row 1 - Average)^2/average
			3.166667		Value I want

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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
1	1	2	1	2
2	5	4	3	0
3
4				3.166667

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D4 =SUMPRODUCT((A1:D1-((A1:D1+A2:D2)/2))^2/((A1:D1+A2:D2)/2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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