You have three ways to calculate an average. Remember the surprise when you learned statistics for the first time, and discovered there are three different ways to calculate “average.” These are mean, median, a, and mode. The average function in Crystal Reports is actually a statistical “Mean.” You also have Median and Mode available as summary functions.

The problem with averages is that a wayward value that much higher or lower than the other values will distort the average.

Mean is simply the sum of the values divided by the number of items. This is the most common “average” you will use. A conditional running total to exclude wayward values is one approach. For example, you might want to calculate average (mean) length of stay in a hospital ward, but exclude any zero values (day patients) or any long term patients (value over 30 days).

Median will find the middle value. Take the lowest and highest value and find the midpoint between them. This works well for values that are evenly distributed. However where values are bunched around a value, it will produce a distorted picture of what the average is.

Mode is simply the most common value. This can be useful where your data are unevenly spread. Sometimes it can be interesting to display all three averages and see which one is more useful. Try this on one of your statistical reports.

Weighted average

You can also calculate a weighted average. You need to decide why you need a weighted average rather than one of the other averages.

Sometimes, we use a view or stored procedure to summarize some detail data to use in our report. Each summary can represent a different number of records, so an average of those records is not accurate.

A weighted average asks for two fields (the field itself and a weight factor). To calculate the average, it totals the field multiplied by the weight factor, and the weighting factor. Dividing the multiplied value by the sum of the weight factor will calculate the correct average for that field. You could do all this by hand, but the summary field is much easier to use.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.