Crystal Reports: Calculating Averages

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). See: Calculating Means in Crystal Reports

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.

Now, averages are very nice and everybody likes them. But they are a bit of a Trojan horse. Let them in where they don't belong, and your business could get slaughtered.

The average is probably the most abused, misapplied, misinterpreted, and misleading of all statistics. Just to illustrate, suppose you had two people in a room.

1. Person One has unpaid medical bills of $2.6 million and owes another million in back taxes. He lives in a tent and can't find a job.
2. The other person is Bill Gates.

If you look at their average net worth, Person One has no money problems. Get the point?

When you're doing statistics, you'll need to apply some statistical methodology that normally is done only by actual statisticians. It's been said that statistics lie, but the actual problem is the statistics are not properly arrived at.

Be very cautious about having your report show "average" anything. You need to look at the population being averaged and determine which outliers exist that can skew the average. The same is true for any other metric that includes an entire population. For example, mean and median.

Some people think it's OK to present the misleading statistic if you also provide a qualifying statement. It's not OK. First of all, the statistic still makes a miscommunication. Secondly, you are sending the message that you are either too lazy or too incompetent to provide a correct statistic. When in doubt, leave it out.

But what if a manager insists on having an average or other statistic that can't be properly validated? In that case, you can use the Bill Gates example to explain the issue. The solution may require contracting an actual statistician to develop a formula your report can use to provide a meaningful and reliable statistic.

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.