Counting Averages, (last group added twice)

[DanSheath]

Hi There

I'm just getting to grips with formulas and have the following problem

These are my two data columns

Form1 - 10
Form1 - 20
Form1 - 15
Form2 - 6
Form2 - 8
Form2 - 10
Form3 - 14
Form3 - 16
Form3 - 12

From this I want to get the average of each form and the grand total of all the averages......ie

Form1 - 15 (group footer)
Form2 - 8 (group footer)
Form3 - 14 (group footer)

Total - 37 (report footer)

I group by form and create a running total field called #Average which I place in the group footer, this averages up the values for each form.....so far so good.

I stumble when it comes to totalling up the average values.
I create this formula

shared numbervar TotalAverage;
TotalAverage := TotalAverage + {#Average};

I place it in the group footer and it does exactly what I want it to do. I can see #Average being summed at each group....until i place it in the Report Footer and it adds the average of the last value twice. So instead of getting 37 I get 51

Is there a way I can stop it from adding the last group twice.... or am I doing this entirely wrong

Any help would be hugely appreciated

Dan

 

[GJParker]

Try

shared numbervar TotalAverage;

if not onlastrecord then
TotalAverage := TotalAverage + {#Average};

BTW you don't need to use a running total to calculate the averages at group level, you could use a summary field in either the group header or footer, your Total Average formula would then look like

shared numbervar TotalAverage;

if not onlastrecord then
TotalAverage := TotalAverage + Average ({MyTable}, {MyField})

HTH




Gary Parker
Systems Support Analyst
Manchester, England

 

[M8tt]

It sounds like you have just placed the formula that performs the calculation in the Group Footer as well as the Report Footer.

Try creating a new formula simply stating:

shared numbervar TotalAverage;

Putting this in your Report Footer will then give you the last value calculated without incrementing your total again.

 

[DanSheath]

Thanks guys, that really helped me out, I used M8tt's and it worked fine.

Probably due to my own inexperience, I had difficulty with the first solution. I could only get the Average function to take field name not my table name as well. When I left it with just the field name, it gave me the average for the whole report, not for each group.

Thanks again

Dan