Average problem in report

[[email protected]]

I have a table with a sex field(M,F) and a date of birth field. I want to have a summary report field that gives the average age of each gender. I am also using "Sex" as the group field. I created the field using this formula: average(age(Date_Of_Birth),grp-"sex). The average age of the females is 138 in the report and males 1. This seems unlikely. What am I doing wrong? When I leave out grp-"sex, both are reported as "32." This also seems unlikely. Thanks. Ed

[Ohlen Cartmell]

RE: Average problem in report

I would approach this a little differently.

Create a calculated field called agefemale and define it as if(sex="F",age,0)

and another called agemale which would be
if(sex="M",age,O)

Also, create a field called females and define it as
if(sex-"F",1,0)

and another called males defined as
if(sex="M",1,0)

Now drag each of these fields to the the section of the report where you want averages and when the genie asks what you want click on total. This will give you the total of the male and female ages and the count of each male and female. Alpha will write the expression needed to create each of these fields as well as give them a default name. Now, find the fields in the calculated field list and create 2 more fields that divide the total ages by the total count for males and females. This should give you the averages you're after. I'm not sure, but I don't believe it's necessary to display the fields to calculate them so after creating them, you can remove the totals from the report and they will still calculate. Don't delete them from the calculated fields list. Let me know if this works out.

[urbanski]

RE: Average problem in report

Ohlen should be correct.
What you need to do is count the number of males and females and then total the ages and divide by the number of each.
He is also correct in that you do not need to show the calculations on the report. In fact, in design mode you can click on report -- calculated fields and define all the calculations there and then drag them in from the drop down list. Regards
Jim