Measure of how concentrated or scattered values are

[bartvana]

This is more a mathematical/statistical question than an Excel question, but I do want to solve it in Excel so if anyone knows of an Excel, M or DAX formula or approach, I'd be very interested.

I'd like to have a number that represents how scattered or concentrated my values are on a timeline.

See below example (and attached workbook) of 4 different charts, where X might be years for instance. The total sum of the values is always the same, but the distribution over the X-axis is different.

Intuitively speaking I can see that Y1 and Y3 are "concentrated" and Y2 and Y4 "scattered", if you see what I mean. So I'd like my resulting factor to be high for Y1 and Y3, and low for Y2 and Y4 (or vice versa). Moreover, since Y1 and Y3 are equally "concentrated" (but over different years), they should have the same resulting number.

Any help would be greatly appreciated!

[#2 · Accepted solution]

For those interested and for future reference, I posted this question on the FreeMathHelp forum where a very helpful Dr. Peterson put me on the right track: Measure of how "concentrated" or "scattered" values are. Basically, you treat the timeline data as a frequency table (which in a certain sense it is actually) and then apply statistical measures like InterQuartile Range (IQR), standard deviation, and/or others.

Which then leads to the next problem, namely how to get this done in Excel, discussed here: Percentiles from frequency table, also solved (using Power Query).