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

I

ironfelix717

New Member
Joined
Jul 8, 2016
Messages
14
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 (n). 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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
I have a table of data
Click to expand...
Where is the data i.e. column letters, row numbers (not in your image)
based on a number determined by the user (maybe 4?).
Click to expand...
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
 
Last edited:
Upvote 0
Assumed:- Data in Column "B" starting row B2 , Input Number in "C1", Results in Msgbox.
Code: 
[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
 
Last edited:
Upvote 0
MickG said:
Assumed:- Data in Column "B" starting row B2 , Input Number in "C1", Results in Msgbox.
Code: 
[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
Click to expand...



Mick,

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

Can you fix this for me please? Thanks for the help. Much appreciated.
 
Upvote 0
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.
 
Upvote 0
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:-
[TABLE="width: 164"]
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="class: xl64, width: 27, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] "] [/TD]
[TD="class: xl64, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] "]A[/TD]
[TD="class: xl64, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] "]B[/TD]
[TD="class: xl64, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] "]C[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]1[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]#[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Length[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]2[/TD]
[TD="class: xl66, bgcolor: white, align: right"]1[/TD]
[TD="class: xl66, bgcolor: white, align: right"]2.5[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]3[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]2[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]2.1[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]4[/TD]
[TD="class: xl66, bgcolor: white, align: right"]3[/TD]
[TD="class: xl66, bgcolor: white, align: right"]2[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]5[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]4[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]4[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]6[/TD]
[TD="class: xl66, bgcolor: white, align: right"]5[/TD]
[TD="class: xl66, bgcolor: white, align: right"]2.6[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]7[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]6[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]2.2[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]8[/TD]
[TD="class: xl66, bgcolor: white, align: right"]7[/TD]
[TD="class: xl66, bgcolor: white, align: right"]2.6[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]9[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]8[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]2.6[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]10[/TD]
[TD="class: xl66, bgcolor: white, align: right"]9[/TD]
[TD="class: xl66, bgcolor: white, align: right"]2.4[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]11[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]10[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]2.5[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]12[/TD]
[TD="class: xl66, bgcolor: white, align: right"]11[/TD]
[TD="class: xl66, bgcolor: white, align: right"]2.3[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]13[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]12[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]2.3[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]14[/TD]
[TD="class: xl66, bgcolor: white, align: right"]13[/TD]
[TD="class: xl66, bgcolor: white, align: right"]2.1[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]15[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]14[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]2.2[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]16[/TD]
[TD="class: xl66, bgcolor: white, align: right"]15[/TD]
[TD="class: xl66, bgcolor: white, align: right"]2.5[/TD]
[TD="class: xl66, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAEEF3]#DAEEF3[/URL] , align: right"]17[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]16[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]2.7[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
Click to expand...

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. :outtahere:
 
Upvote 0
MARK858 said:
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? :outtahere:
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Exclude Formula/Calculation based on status
Replies
1
Views
325
awoohaw
awoohaw
D
  • Question Question
Generating Reports in Word Using Data in Excel
Replies
1
Views
338
Dan_W
D
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
315
Mouse7681
M
O
  • Question Question
Excel formula / vba to calculate the Time Taken according to batches of data
Replies
1
Views
740
BigBeachBananas
B

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

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
Back
Top