Statistics

JoeUser2004

Bronze Contributor

Jan 22, 2022

IHStrafalgari .... Since you do not provide even the formulas that you use, there is no way to provide constructive solutions.

For quicker constructive responses, attach an example Excel file. If you cannot attach a file (I'm told some users cannot !), upload the file to a file-sharing website, and post the shared download URL in a response here. I like box.net/files; others like dropbox.com.

-----

IHStrafalgari wrote: ``I'm sure there is no mistake for my manual calculation.``

Perhaps not. Again, we cannot comment constructively because you did not share your (Excel implementation of those?) manual calculations.

In addition to any Excel implementation of the "manual" (mathematical) calculations, it would be helpful if you provide the mathematical methods that you use, in one form or another. If you can find the math methods online, provide the URL(s). Alternatively, scan textbook pages or even take a phone photo, and attach the JPG files or upload them to a file-sharing website.

I cannot imagine any way (*) to use Excel AVERAGE and STDEV (aka STDEV.S) to correctly calculate the mean and std dev of grouped data, in general. So, I'm sure there are mathematical mistakes, at least, in those Excel formulas.

(*) There is a way: replicate the data for each group n[i] times, where n[i] is the frequency of group "i". See my example below. But that is not practical, IMHO.

Even if you estimate the mean of each group, the average of the group means is not equal to the correct mean of the grouped data, unless the number of samples for each group is the same.

-----

IHStrafalgari wrote: ``Is Excel have statistical function/formula built-in it for grouped data?``

Not really. But we can easily implement a mathematical method.

Consider the following example from https://www.statology.org/mean-standard-deviation-grouped-data . See the attached Excel file.