3 month and 4 month rolling averages 1

[VBACT]

Hi,

I am looking for an easy way to write a function to calculate 3 month and 4 month rolling averages.

Would appreciate any help

Thanks

 

[PaulBricker]

I'll have to show my ignorance here a little, but what constitutes a 3 or 4 month rolling average. Is it
Totalfor3Months/3
or
Totalfor3Months/NumberofRecordsin3Months.

It will make a difference.

Paul

 

[VBACT]

Totalfor3Months/NumberofRecordsin3Months.

The table that I have set up appends records into fields that list the

1)Number of units sold
2)The month number
3)The year number

The three or 4 month average is calculated by computing the most current last 3 months, or 4 months and dividing by 3 or 4. Once I have defined the recordset and used the 'MoveLast', can I use the move previous(?) to count 3 or 4 records backward. If this does not make sense, please let me know.

Thanks for your help.

Thanks

 

[PaulBricker]

I would assume that you could use the MoveLast..MovePrevious properties to accomplish this but I'm a little confused by two things you say. You start by saying
Totalfor3Months/NumberofRecordsin3Months
and then you say
"The three or 4 month average is calculated by computing the most current last 3 months, or 4 months and dividing by 3 or 4
The first on says to me get the SumofUnits for 3 months and divide by 234 transactions which returns $xxx.xx/transaction. The other way says get the SumofUnits for 3 Months and divide by 3 which returns $xxx.xx/month
so I'm not clear which you need. Dividing the SumofUnits by 3 would certainly be easier than trying to get a count of transactions to divide by and it would make using the MoveLast..MovePrevious..MovePrevious easier to deal with. Let me know which way you want to go and I'll do what I can to assist.

Paul

 

[VBACT]

Paul,

Thanks....discussion always helps. The 3,4 and 6 month rolling sum and averages can be computed by using the .MovePrevious after using the .MoveLast. The function computes an int3Sum, int4Sum, and int6Sum in a Do Loop and then calculates an average using .Recordcount.

Thanks for the discussion. Let me know if you need to see the code. I'd be happy to e-mail it to you.