Weighted Standard Deviation and Quartiles in excel

问题:

I've got a dataset with aggregated data and total values per category. Example:

Now I want to create a boxplot to visualize the insights of this dataset and not just the (weighted) averages. I've found some formula's for weighted standard deviation, but nothing I have been able to translate to an excel formula. Is there a default formula that does this in excel? If not, some suggestions on how to handle this? And how about the quartiles? Or is the best way to somehow expand the dataset to reflect the frequencies?

回答1:

The formula for the quartiles:

{=PERCENTILE(IF(COLUMN(INDIRECT("1:"&MAX(Data!N3:N15)))<=Data!N3:N15,Data!M3:M15,""),0.25)}

(confirm with Ctrl+Shift+Enter / Command+Enter )

With Column N containing the weights and M the values, giving the 1st quartile.

Now I just need the median, if possible at all without expanding the data... ;)