Excel Formula/VBA to compute X-Bar-Bar based on varying subgroup size

[ironfelix717]

Hi,

I need to figure out how to take a table of data and compute the X-Bar-Bar value from the data based on any subgroup size . The user would select the subgroup size, then the formula/code would calculate X-Bar-Bar from the subgroup size. That value would then be stored somewhere of my choice.

For those who aren't up to speed with SPC/Stats... I have a table of data. I need to group that data (note: data is in consecutive-time order), based on a number determined by the user (maybe 4?). Calculate the mean of each group in the list. And then calculate the MEAN of those means for each group.

Any help is appreciated. My creative mind for solving this problem has hit a wall. I believe it can be solved with a formula quite easily.


Thanks

 

[ironfelix717]

All,

here is a photo example to clear up any confusion.

Screen Shot 2017 08 12 at 10 10 50 AM — imgbb.com

 

[MARK858]

I have a table of data

Where is the data i.e. column letters, row numbers (not in your image)

based on a number determined by the user (maybe 4?).

Which cell (Again Column letters/row numbers are not in your image)

Where are the results going?


Better stlll see the links in my signature for some links to some add-ins/methods to upload some usable screenshots (please don't post images like png's or JPegs as they are not usable i.e. can't be copied into a spreadsheet).

With the MrExcel addin please use the link below as the recent updates have caused some issues with the original version.
It is still work in progress but unless there are conflicts with other addins it does work.

New version

For how to install see the link below...

MrExcel HTML Maker Update

 

[MickG]

Assumed:- Data in Column "B" starting row B2 , Input Number in "C1", Results in Msgbox.

[COLOR=navy]Sub[/COLOR] MG12Aug45
[COLOR=navy]Dim[/COLOR] Lst [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant
Lst = Range("B" & Rows.Count).End(xlUp).Row
[COLOR=navy]If[/COLOR] (Lst - 1) / [c1] = Int((Lst - 1) / [c1]) [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] n = 2 To Lst [COLOR=navy]Step[/COLOR] [c1]
        nStr = nStr & IIf(nStr = "", Application.Average(Range("B" & n) _
        .Resize([c1])), "+" & Application.Average(Range("B" & n).Resize([c1])))
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] If
MsgBox "Avg/Avg " & Evaluate(nStr) / [c1]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[ironfelix717]

Assumed:- Data in Column "B" starting row B2 , Input Number in "C1", Results in Msgbox.

[COLOR=navy]Sub[/COLOR] MG12Aug45
[COLOR=navy]Dim[/COLOR] Lst [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant
Lst = Range("B" & Rows.Count).End(xlUp).Row
[COLOR=navy]If[/COLOR] (Lst - 1) / [c1] = Int((Lst - 1) / [c1]) [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] n = 2 To Lst [COLOR=navy]Step[/COLOR] [c1]
        nStr = nStr & IIf(nStr = "", Application.Average(Range("B" & n) _
        .Resize([c1])), "+" & Application.Average(Range("B" & n).Resize([c1])))
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] If
MsgBox "Avg/Avg " & Evaluate(nStr) / [c1]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Mick,

Getting a runtime error 6 "overflow" with your code.

Can you fix this for me please? Thanks for the help. Much appreciated.

 

[ironfelix717]

All your questions are very insignificant to the problem. You can assume all the columns and cells, they are arbitrary. Additionally, the "results" are only one result. The X-BarBar is computed as one number and will change depending on the input subgroup size.

 

[MickG]

Have tried it on the data you posted.
Is your data in column "B" starting "B2".
Is the input number in cell "C1".
Like this:-

	A	B	C
1	#	Length	4
2	1	2.5
3	2	2.1
4	3	2
5	4	4
6	5	2.6
7	6	2.2
8	7	2.6
9	8	2.6
10	9	2.4
11	10	2.5
12	11	2.3
13	12	2.3
14	13	2.1
15	14	2.2
16	15	2.5
17	16	2.7

<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>

 

[MARK858]

All your questions are very insignificant to the problem. You can assume all the columns and cells, they are arbitrary. Additionally, the "results" are only one result.

Of course the ranges are not insignificant to the problem, how do you expect us to write code and produce formulas without them and what happens if we guess it and you can't work out the amendments needed? I take it you would expect us to rewrite them?


I think I will leave MickG to help you with your questions as I am sure that he will give you an adequate solution as I am not going to assume the ranges.

 

[ironfelix717]

Of course the ranges are not insignificant to the problem, how do you expect us to write code and produce formulas without them and what happens if we guess it and you can't work out the amendments needed? I take it you would expect us to rewrite them?

I'm not looking for you to write my spreadsheet. I am looking for a solution, which may be code. If it is code, I can easily change the range values within it. All that is relevant to the problem is that I have an arbitrary table of data and I need to preform a certain function with the data. Literally this information is incredibly insignificant to my question.

 

[MARK858]

See post number 7