Statistics

Excel has a powerful set of tools to perform statistical analysis, but they apply only to ungrouped data. As an example, the AVERAGE function calculates a simple average of ungrouped numbers, but calculating a weighted average requires a different approach.

Excel has a wide range of statistical functions which can help the users of a report get a good sense of the data. These functions can calculate central points of different kind (an average, a median), show how far individual values are dispersed overall from the mean (standard deviation, average deviation), tell whether your array of number is shifted above or below the mean (skewness) and other characteristics. These parameters can help make conclusions as to how stable the numbers are likely to be going forward, estimate the most probable range of numbers in your forecasts, make conclusions on reliability of the data for planning purposes, assess the likelihood of numbers going up or down in the next periods. The drawback of Excel native statistical functions is that is that they are designed to work only with ungrouped raw data. If you work for e.g. an automotive company (as I have some years ago) and have a list of 100 cars that your company sold at various prices, Excel can quickly give you the full price statistics for the cars you sold. But in many analytical situations you will have a list of different car specifications your company is going to offer (which differ by the level of equipment, bodystyle, engine size, type of transmission) with selling prices and volumes (or percentage mixes) you expect to sell. Excel built-in statistical functions are not suited for such type of data. They can only deal with ungrouped, unclustered numbers. Numbers can be grouped in two ways. You can have a list of your car specifications and a number of each model you sold next to it. Or you can have the weight of every model expressed as a percentage of your total sales. I have developed the attached spreadsheet with formulas which replicate the functionality of all Excel statistical functions for both types of ungrouped data. In some cases formulas for grouped and ungrouped data are quite similar; in other cases I have done them completely differently - either because I had to or to show you another possible approach.