COMPLEX offset formula: perform calculation based on dynamic cell range

[ironfelix717]

Hi,

I have a complex problem here that I need solved with a formula. I believe an offset formula can be used here. But I simply lack the ability to write a proper formula to achieve the solution.

I don't want VBA...

I have a column of raw data. The user selects a group size of whole numbers. The data is grouped consecutively by the group size selected. Thus, 50 raw data samples, user selects group size of 5, 10 groups are created.

Ok, so i have a formula written that calculates the AVERAGE within each group and spits out the numbers. This is dynamic. Great.

What I need is a formula that takes each raw data point, is subtracted from its respective group's mean. Therefore, 50 raw data points = 50 calculations.The issue with doing this is I need it to be dynamic. Thus if the user selects, say "6" for a group size, the calculations change, because the group size changes. Note: group sizes are equivalent obviously.

Please see the attached spreadsheet, which shows manually calculated results.

Also, here is an example:

RAW: GROUP SIZE = 2
1
3
4
5
6
2
5
6
8
3

AVERAGES:
GROUP 1 = 2
GROUP 2 = 4.5
GROUP 3 = 4
GROUP 4 = 5.5
GROUP 5 = 5.5

RESULT:
1 - 2 = -1
3 - 2 = 1
4 - 4.5 = -.5
5 - 4.5 = .5
6 - 4 = 2
2 - 4 = -2
5 - 5.5 = -.5
6 ....
8.....
3..... so on an so fourth


DOWNLOAD EXAMPLE: https://www.dropbox.com/s/bi6aggnysf7h9af/Dynamic Cell Reference Computation.xlsx?dl=0

Thank you!

 

[AlphaFrog]

Try this...

=C7-INDEX($I$7:$I$57, INT((ROW(A1)+$F$6-1)/$F$6))

 

[Aladin Akyurek]

In I7 enter and copy down:

=IFERROR(AVERAGE(OFFSET($C$7,((ROWS($I$7:I7)-1)*$F$6+1)-1,0,$F$6)),"")

In E7 enter and copy down:

=($C7-INDEX($I$7:$I$57, INT((ROW($E7)-ROW($E$7)+1+$F$6-1)/$F$6)))^2

 

[ironfelix717]

In I7 enter and copy down:

=IFERROR(AVERAGE(OFFSET($C$7,((ROWS($I$7:I7)-1)*$F$6+1)-1,0,$F$6)),"")

In E7 enter and copy down:

=($C7-INDEX($I$7:$I$57, INT((ROW($E7)-ROW($E$7)+1+$F$6-1)/$F$6)))^2

Worked amazing! Thank you very much!

 

[Aladin Akyurek]

Worked amazing! Thank you very much!

You are welcome.