COMPLEX offset formula: perform calculation based on dynamic cell range

ironfelix717

New Member
Joined
Jul 8, 2016
Messages
14
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!
 

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
Joined
Sep 2, 2009
Messages
16,400
Try this...

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top