VBA Code to calculate Xbar-Bar with varying subgroup size

**ironfelix717** · 08-22-2017, 12:45 PM

Hi,

I am trying to create a spreadsheet to calculate X-Bar-Bar from a column of data DEPENDING on the subgroup size selected by the user. Xbar-bar is a "mean" of a group of "means".

More precisely, this is what I wanted to achieve...

I have a column of data
User selects a whole number from 1 to 10
The data in the columns is grouped by the whole number. (not actually physically grouped, just mathematically for calculation purposes - in consecutive order in the column)
A an average for each group is calculated.
Then finally, the average of THOSE averages is stored in a cell.

See image:
https://ibb.co/eG6yHQ

This will require some VBA code but I am inexperienced and perhaps there is another solution. I can manipulate the code to get it how I want it for the specific columns and such. I just need to be able to do this.

Thanks for any help.

**MrShorty** · 08-22-2017, 01:31 PM

I don't know that it "requires" VBA, but some might prefer to code it in VBA. Do you prefer VBA?

Without VBA, I would probably do something like:
1) Enter 4
2) Enter a column of integers, 1, 2, 3, 4, ... more than I will ever need (assume P25:P50).
3) Use an OFFSET() function to locate the average range for each x-bar (something like OFFSET($M$25,$N$19*P25-$N$19,0,$N$19,1))
4) Put that inside of an AVERAGE() function (in Q25 maybe AVERAGE(OFFSET(...)) Note the mix of relative and absolute references.
5) Put that inside of an IFERROR() function to deal with the error values that indicate "outside of data range". =IFERROR(AVERAGE(...),"outside of data")
6) Copy Q25 down
7) Enter another AVERAGE() function to average the averages (maybe in Q23 =AVERAGE(Q25:Q50))

Help files for all functions: https://support.office.com/en-us/art...6-c6d90033e188

If you don't have a preference for VBA, or if you would prefer a formula based solution but did not believe it would be possible using only worksheet formulas, then maybe that will be good enough.

**ironfelix717** · 08-22-2017, 01:34 PM

MrShorty,

Wow, i didn't think this was possible with a formula. I will try those formulas. I never used OFFSET() or INFERIOR() !

Thanks very much and I will report back if this works!

**CAntosh** · 08-22-2017, 01:46 PM

Are partially sized groups considered groups? For example, if you have 12 rows of data and the N19 value is 5, does that create 2 groups, 3 groups, or 2.4 groups?

**ironfelix717** · 08-22-2017, 02:18 PM

CAntosh,

I should have clarified that. If there is a partial subgroup, As in, we have 18 data points with subgroup size as "4"... There would be 2 points left over. I want EITHER an error "Sample size may not allow for partial subgroup" OR i want the remaining data points to be discarded from any calculation and ignored.

Thanks!

**CAntosh** · 08-22-2017, 04:15 PM

My algebra might be a bit rusty, but if all of the groups are the same size and the chosen group size must divide evenly into the number of entries, then the average of the averages won't change as you modify N19. If there are 12 entries and you go with groups of 4, then you end up with three averages: (a+b+c+d)/4, (e+f+g+h)/4, and (i+j+k+l)/4) that you then average together by summing and dividing by 3, which gives you (a+b+c+d+e+f+g+h+i+j+k+l)/12 as the average of the averages. This total is the same as the basic average of all 12 together. Change the N19 value from 4 to 1,2,3,6, or 12 and it's the same thing. If the size must be an even divisor of the total number of entries, then the x double bar you're looking for is really just the average of the entries. The larger your groups, the smaller the number of groups - proportionally, the differences cancel each other out as you change N19 as long as N19 divides perfectly into the number of entries.

Despite the above findings, I tried using formulas that mimic the complete math. To account for partial entries by ignoring the leftovers, I used the following (in O20 of the attachment):

=(SUM(N25:OFFSET(N24,N19*INT(COUNT(N25:N48)/N19),0))/N19)/INT(COUNT(N25:N48)/N19)

To supply an error message for imperfect divisor sizes, I used the following in P20:

=IF(MOD(COUNT(N25:N48),N19)=0,(SUM(N25:N48)/N19)/(COUNT(N25:N48)/N19),"Sample size may not allow for partial subgroup")

The top formula will prove useful if you're ignoring partial groups at the end, since it will lead to different totals as partial groups are dropped. The latter formula, as described above, could be simplified further by simply using AVERAGE instead of SUM/COUNT in the middle of the formula.

In my attachment, I used 24 sample entries. You can ignore or delete everything to the right of the data. I manually entered some group averages so I could check my data. Feel free to experiment with the attachment - maybe I'm misunderstanding the X double bar? Regardless, hopefully it proves useful: