Getting Correct Average / Standard Deviation

[Former Member]

I'm using Crystal Reports 2008.

I'm trying to average columns of numbers.  I want to average them at the bottoms of the columns. The answers come through as strings, so I've created formula fields for each one in WhileReadingRecords -   tonumber({field})  -  in order to change each answer to a number, so they can be averaged.

However, the average for each column is not correct. Looking at it in Details, it's adding a 0.00 in the first line and does not include the last record. 

How do I fix this so that the columns average out correctly?

The Standard Deviations are also not working, either using the Summary or using Running Totals.

Thanks again.

AJ

[JWiseman]

hi AJ,

please insert a screenshot of your report in Preview so that the formula field is visible as well as the section names.

-jamie

[Former Member]

Here's an image of what I've got going on.   When I have a lot of samples, the average and SD LOOK okay, but when there's only 5 or 6, it's obvious not all of the samples are being included...

[JWiseman]

sorry but i can't see the section names. perhaps take a screen shot of the design mode.

also don't forget that you've got the decimal places formatted to show 1. this means you're always going to have what looks like a rounding error in your totals. the average is going to do an average of the numbers without rounding.

[Former Member]

Thanks Jamie,  Here's a simpler report, with both the Design and the Preview shown.  As you can see, the average is off a little.  It's basically adding the first two in the column and dividing by three:

[JWiseman]

what's most likely happening is that your displayag field is not a group level field...i.e. there is more than value for that field for group 3. i would recommend using a manual running total instead.

create a new formula and put this on gh3 and just double check that the running total for the displayag is summing properly.

whileprintingrecords;

numbervar c:= c+ 1;

numbervar d:=d + {@DisplayAg};

create a new formula then put this on rfb;

whileprintingrecords;

numbervar c;

numbervar d;

if c <> 0 then d/c;