I need a Monthly Average Function

  • Thread starter Thread starter Cal
  • Start date Start date
C

Cal

I need to be able to calculate the average usage of a
field in a table that is date driven.
The problem I have is, I want to make this as up to date
as possible, which means using partial current months.
How can I get a monthly average of usage based on today's
date in August, which would be only a fractional month.
ie. through 7/31/03 is easy, take total usage divide by
7, but using 8/20/03 is not. I can not divide by eight
because the month is not over.

help
 
Divide by the total months and fractions of a month.
How are you getting 7 or 8 now? If you are using the current date to calculate
the divisor you could try

Divisor: Month(Date()) -1 + (Day(Date())/ Day(DateSerial(Year(Date()),Month(Date())+1,0))

That should give you 8 -1 + (20/31) --->> 7 + 20/31 --->> 7.64516129032

This assumes that you are only interested in calculating from the beginning of
the current year and have only one record for each month.
 
Back
Top