Maths Stats Assistance

[voniom]

Hello, please can any of your clever people help me out with where I am going wrong with the attached speadsheet.

I am trying to use standard deviation techniques to compare the quality of products.

Hope to hear, thank you in advance!

[AliGW]

Could you just please check that your forum profile is up-to-date? Thanks.

[voniom]

Oh, how do I do that please.....?

[FlameRetired]

It's the data listed under your name in each of your posts.

If you are not familiar with how to update your profile find the dark blue menu at the top of this page that says

Forum Actions

. Click that and 'Edit Profile'.

[voniom]

Thank you for that, I have updated what I can, are you able to confirm I am up to date now please?

[bebo021999]

Could you tell more about your worksheet? What and where are your expected results>?

[voniom]

Hi, the expected results are over in the far right columns, I am hoping someone who understands how these equations for 'mean' and 'standard deviation' would be able to tell me where I was going wrong with my formulas?!?

[davsth]

Working out measures from a summary table will usually give different answers, which are less accurate, as you are basing the results on the midpoints of each range and not the actual values. However they are usually close.


Although your formula are over compicated =SUM(J6/F6) is the same as =j6/f6 they are correct for the mean 43.8 is close to 43.95

the SD formula is wrong it should be =SQRT((K6-(F6*E8*E8))/F6)
2.42 is close to 2.97

likewise the second mean is correctly calcuated but sd should be
=SQRT((K14-(F14*E16*E16))/F14)

which is 2.45 close to 2.47

[AliGW]

Thank you for that, I have updated what I can, are you able to confirm I am up to date now please?

Yes, thanks.

[voniom]

Hi, thank you for your clear explanation, this helps a lot thank you!
Would you mind telling me how I could get the accurate answers not using a table.
How would you use a 'frequency' figure in this instance? It would nice to be accurate!

[voniom]

Fabulous, thank you, stay safe!

[AliGW]

You, too. No TT this year, I guess?

[davsth]

To get the accurate figures you always calculate from the raw data, then you just need the average and stdev functions, looking at all the data.

Any grouping of data to anything but unique values means you will get a less accurate figure

if the raw data was 1, 2, 2, 4, 5, 6, 6, 8, 9
You would get a mean of 4.77 and sd of 2.77


in a table of

Value Frequency
1 1
2 2
4 1
5 1
6 2
8 1
9 1


You would get exactly the same results, but once you start summarising to midpoints, you lose detail
midpoint freq
1-2 1.5 3
3-4 3.5 1
5-6 5.5 3
7-8 7.5 1
9-10 9.5 1

average is now 4.61
sd is now 2.84

[voniom]

Sorry only just seen that you messaged me again, yeah all off this year :-( roll on next year :-)