# COMPLEX offset formula: perform calculation based on dynamic cell range

#### ironfelix717

##### New Member
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.

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

Thank you!

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### AlphaFrog

##### MrExcel MVP
Try this...

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

Last edited:

##### MrExcel MVP
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

##### New Member
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!

Replies
1
Views
55
Replies
5
Views
168
Replies
7
Views
117
Replies
0
Views
69
Replies
1
Views
51

1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back