I would like to compute some descriptive statistics about the following table of information. The main thing that I like to describe is the Price component.
Price Qty 493 5 4500 8 2107 14 269 1
The weighted average is straight forward with =sumproduct(Price,Qty)/sum(Qty). However it gets more challenging when I want to calculate the =skew() and =kurt(). In an ideal situation I would have data that isn't aggregated, but unfortunately I only have aggregated data.
Does anybody have a smart idea about how to get the skewness and kurtosis in Excel using the above data?
There are two ways to do this, but I don't think you're going to like the answer, since Excel functions don't work with aggregate data.
First way would be to unroll the data.
Price
493
493
493
493
493
4500
4500 (x 7 more times, one per row)
2107
2107 (x 13 more times, one per row)
269
Skew, kurtosis, and standard deviation will all work great with data unrolled.
Here's another way.
Extending your example:
Price Qty AggPrice
493 5 2465
4500 8 36000
2107 14 29498
269 1 269
AggPrSum =sum(c2:c5)
mean =sum(c2:c5)/sum(b2:b5)
You may then calculate the skew by applying the equation. When you calculate the mean, the rest should become clear. (You can start by adding a new column D which is price minus the mean, the difference cubed.)