Formula needed to calculate and auto-update Standard Deviation

[gko_87]

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