Formula needed to calculate and auto-update Standard Deviation

There are 3 tables in the workbook attached. There is a drop down in cell Q1 and cell Q3 and the values in the table below change when the drop-downs change. What would be the formula that would calculate the following for Manchester city (cell “O6”) when the drop downs are altered?

Total, Mean, Median, Mode, Maximum, Minimum, Range (Max - Min), Variance, Standard Deviation

Expected results and cells used to compute them when Q1 = “5” and when Q3 = “Overall” are on the tables to the left:

Note: Empty cells are to be ignored; the reference team is Manchester City which is in cell O3; zeros should not be ignored.


EPL.xlsx

If I'm understanding correctly, you can use the following:

Total in Q24: =SUMIF(P8:P22,O6,Q8:Q22)+SUMIF(S8:S22,O6,R8:R22)
Mean in Q25: =Q24/(COUNTIF(P8:P22,O6)+COUNTIF(S8:S22,O6))
Median in Q26 (Array-Entered with Ctrl+Shift+Enter instead of Enter): =MEDIAN(IF(P8:P22=O6,Q8:Q22),IF(S8:S22=O6,R8:R22))

For Mode, Max, Min, Variance, and St. Dev, use the Median formula but swap out median for the appropriate function. All must be array-entered. See the attached for further details:

Thank you for your response CAntosh. Would you kindly help me understand why you use var.s,stdev.s and mode.sngl instead of simply var,stdev and mode?

Thank you.

Glad I could help! I used mode.sngl instead of mode.mult because you seem to be looking for only a singe number returned, rather than an array. For var. and stdev., I used sample functions rather than population functions because we're only looking at a sample of the total data, so it the sample functions seemed to fit your data the best. The Excel help file can provide a bit more info on the difference. Additionally, here' a brief summary of sample vs. population:


There is only one little difference in the calculation of variance and it is at the very end of it. For both population and sample variance, I calculate the mean, then the deviations from the mean, and then I square all the deviations. I sum all the squared deviations up. So far it was the same for both population and sample variance.

When I calculate population variance, I then divide the sum of squared deviations from the mean by the number of items in the population.

When I calculate sample variance, I divide it by the number of items in the sample less one.

As a result, the calculated sample variance (and therefore also the standard deviation) will be slightly higher than if we would have used the population variance formula. The purpose of this little difference it to get a better and unbiased estimate of the population‘s variance (by dividing by the sample size lowered by one, we compensate for the fact that we are working only with a sample rather than with the whole population).


Source: http://www.macroption.com/population...ard-deviation/

Hope that helps!

I had earlier marked this thread as solved. But kindly I would like to also be able to compute the deviation for a team for all goals scored at home, away and home+away:

For example, if Manchester City has played 5 matches at home ground and 5 matches away:

Home: Deviation for goals scored on home ground in the 5 home matches

Away: Deviation for goals scored while playing away in the 5 away matches


All Matches: Deviation for overall matches i.e. 10 matches (5 Home matches + 10 Home matches)

My attachment is attached below.

Note: Zeros should not be ignored.

Thank you.

Onditi.xlsx

What do you mean by "Deviation"?

Standard deviation.

What I need is the calculation of standard deviation for the goals.

You have marked the thread as solved ...

Would you kindly mark it as unsolved AliGW? I did not see the need to start a new thread since the question is similar to the original, though slightly different.

You can do that yourself. Go to the top of the page and choose thread tools.

Thank you. Never used it before. I always imagined only moderators can undo that. At least I have learnt something here.

You're welcome! You will have used it before to mark your thread as solved.

Ali, would you kindly help me with the problem on post #5 on this thread?

Onditi - if I were able to help with your query, I would have done so already, but I am not a mathematician. My involvement in the thread was as a moderator: I was concerned that you would not get help if you left the thread marked solved. I cannot help you myself with this particular query, I'm afraid, but someone else will. I assume you have already explored the standard deviation function?

You could use array formulae that are based on this one:

=STDEV.S(IF(($D$6:$D$105=$K6)*ROW($E$6:$E$105)>=LARGE(($D$6:$D$105=$K6)*ROW($E$6:$E$105),MIN(5,COUNTIF($D$6:$D$105,$K6))),$E$6:$E$105,""))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Incidentally, I am not your employee or your personal slave. So, I do not like receiving messages like "Still waiting for your reply". Normally I would just COMPLETELY ignore your message and your thread when I get a message like that. On this occasion, I just made you wait for four hours.

You might be able to treat your employees/slaves like that... but you can't do that to me.

Thanks a lot Glen, for your response. I am so grateful.

What if i do not want to tie the standard deviations to the last 5 matches, how would I do it? Now that I am able to use your formula to work the "moving/rolling standard deviations" for the last "n" matches, how would I compute the deviations for all the matches?

To illustrate further: At the end of the session, there will be a total of 190 matches played at home, 190 matches played away,making a total of 380 matches overall. I will continue adding data to my workbook as more matches are played.

a) How would we compute standard deviation for all home matches (190) for a specific team?

b)How would we compute standard deviation for all away matches (190) for a specific team?

c) How would we compute standard deviation for both home and away matches (380 matches) for a specific team?

The workbook in post #5 still applies.

That's sooo much easier. Array formulae, again, like this one:

=STDEV.S(IF($D$6:$D$105=$K6,$E$6:$E$105))

Thank you again Glen. I am much grateful. God Bless.

Glad to have helped. happy to help again... but BE PATIENT!!!

I will be henceforth. And thanks for helping even though I annoyed...

Should we really be using stdev.s even in your last attachment? Should we not be using stdev instead?

It shouldn't make any difference... I was just in a hurry to answer and log off for the night.

It's beer o'clock here in Ireland.