Sum group totals and then average by # of groups

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

This one seems pretty straight froward to me, but I am going wrong
somewhere...

I have a user who has a grouped report indicating expenses on a per month
basis. The details section shows the individual expenses, per date, within a
given month. At the end of each month there is a sum showing the total
expenses for each month. Grouped field name = "sum of amount"

The report footer has a grand total for all the individual amounts in the
details section.

What she would like to do is sum up the per month group totals, and then
have the system figure out the average of the per month group totals. We
would put this figure in the report footer.

How do I get the report to sum all the groups, then divide by that number of
groups to come up with an average expense per month result?

I've tried so many variations, but either get nothing to appear as a result
to my formula, or get a error to display. I must be close, but am, obviously,
missing something.

Thanks for any help with this.
 
Tammy said:
This one seems pretty straight froward to me, but I am going wrong
somewhere...

I have a user who has a grouped report indicating expenses on a per month
basis. The details section shows the individual expenses, per date, within a
given month. At the end of each month there is a sum showing the total
expenses for each month. Grouped field name = "sum of amount"

The report footer has a grand total for all the individual amounts in the
details section.

What she would like to do is sum up the per month group totals, and then
have the system figure out the average of the per month group totals. We
would put this figure in the report footer.

How do I get the report to sum all the groups, then divide by that number of
groups to come up with an average expense per month result?
Click to expand...


Just divide the grand total by the number of months
(groups). You can calculate the number of groups by adding
a text box (named txtGrpCnt) to the group header or footer
section, Set its control source expression to =1 and its
RunningSum property to Over All.

Then the average can be calculated in a report footer text
box by using an expression like:
=txtGrandTotal / txtGrpCnt
 
Fantastic! Thanks so much, Marshall!

Those are two properties I had never worked with before. Very convenient!

Really appreciate you taking the time to answer this post!
 
Back
Top