Running daily average

[WSF]

Access 97

I have a number field on a form for each week day - Mon through Fri
Numbers are whole numbers.
At times any day could have zero qty.

Is there a way to calculate a running daily average (whole number)
through the week, for example

Mon = 12
Tue = 14 R/Avg = 13
Wed = 19 R/Avg = 15
Thu = 23 R/Avg = 17
Fri = 27 R/Avg = 19

Where I get into trouble is if any of the days is zero.
I want the running average for only those days that have more than zero.
So if say Wed was zero, then the running average on Thursday would be
for Mon/Tue/Thu only

TIA
WSF

 

[Jeff Boyce]

Speaking as a recovering statistics instructor, you would be making a big
mistake to leave a day with "0" out of your calculation. Zero is a
meaningful number, indicating "none". It should be included in calculating
an average.

 

[WSF]

Hello Jeff,
Thanks for the response.
Maybe I should put it another way.
If there is zero for Monday, but quantities for Tuesday and Wednesday
etc., I only want the average calculated for Tuesday and Wednesday etc.
I don't want Monday's zero to be included in the calculation.
If there is a quantity for Monday and Wednesday etc. and a zero for
Tuesday, I only want the average for Monday and Wednesday etc..

As the week progresses I would like to display the running average on
any given day for only those preceding days that have a value greater
than zero. Simply, for example, if only Mon, Wed, Fri have values
greater than zero, then on Wed and Thu the running average showing at
close of each of those days would be the equivalent of the sum of Mon
and Wed divided by 2. Likewise, at the end of the week the running
average showing Fri would be the equivalent of the sum of Mon/Wed/Fri
divided by 3.

Where it gets complicated is that the zero days will be random and I am
looking to simplify the task of having to ask a compounding question
going through each day of the week asking about which days were zero
[therefore?], except Monday of course. It is its own running average.

I'm no statistician so I hope you understand my explanation.

The purpose is to plot the progress through the week at the end of each
day for only those active days.

Possible?

Cheers,
WSF

 

[Marshall Barton]

Access 97

I have a number field on a form for each week day - Mon through Fri
Numbers are whole numbers.
At times any day could have zero qty.

Is there a way to calculate a running daily average (whole number)
through the week, for example

Mon = 12
Tue = 14 R/Avg = 13
Wed = 19 R/Avg = 15
Thu = 23 R/Avg = 17
Fri = 27 R/Avg = 19

Where I get into trouble is if any of the days is zero.
I want the running average for only those days that have more than zero.
So if say Wed was zero, then the running average on Thursday would be
for Mon/Tue/Thu only

You never said how you're calculating the running average.
If you're using aggregate functions, then remember that they
ignore Null values. So you could then use Null instead of
zero in the functions:

Avg(IIf(field = 0, Null, field))

Similarly for Count.

 

[Jeff Boyce]

In addition to Marsh's approach with Nulls, you could exclude values from
the query if they were "0".

But there may also be an issue with your data structure. If you wish to use
Access' aggregate (Totals) functions, your data has to be structured in a
relational design.

Does your table look more like an Excel spreadsheet, with one field for each
weekday, or like a relational table, with two fields, one for the date and
one for the amount?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hello Jeff,
Thanks for the response.
Maybe I should put it another way.
If there is zero for Monday, but quantities for Tuesday and Wednesday
etc., I only want the average calculated for Tuesday and Wednesday etc.
I don't want Monday's zero to be included in the calculation.
If there is a quantity for Monday and Wednesday etc. and a zero for
Tuesday, I only want the average for Monday and Wednesday etc..

As the week progresses I would like to display the running average on
any given day for only those preceding days that have a value greater
than zero. Simply, for example, if only Mon, Wed, Fri have values
greater than zero, then on Wed and Thu the running average showing at
close of each of those days would be the equivalent of the sum of Mon
and Wed divided by 2. Likewise, at the end of the week the running
average showing Fri would be the equivalent of the sum of Mon/Wed/Fri
divided by 3.

Where it gets complicated is that the zero days will be random and I am
looking to simplify the task of having to ask a compounding question
going through each day of the week asking about which days were zero
[therefore?], except Monday of course. It is its own running average.

I'm no statistician so I hope you understand my explanation.

The purpose is to plot the progress through the week at the end of each
day for only those active days.

Possible?

Cheers,
WSF

Speaking as a recovering statistics instructor, you would be making a big
mistake to leave a day with "0" out of your calculation. Zero is a
meaningful number, indicating "none". It should be included in calculating
an average.

 

[WSF]

Hello Marshall and Jeff,
I guess I am asking how can I calculate the running averages coz my
attempts have failed.

I have a job record table and need to report the Weekly count of jobs by
WeekNo showing each day of that week. Some days there may be no jobs at all.

I am trying to present it thus:
e.g.
Day> Mon Tue Wed Thu Fri Total
Count> 12 14 19 23 27 95
This is no problem presenting either in the form or a report.
What I would like to do is present below each day a running average per
day for those days that are greater than zero only.

Lets assume these text boxes are named txtMon, txtTue etc and txtTotal.
Below each I have a text box txtAvg2Mon, txtAvg2Tue etc
therefore:
Run/Avg> ? ? ? ? ?

That is where I am stuck.
I have been trying to do it with the txtAvg2### as an unbound
calculated control. Simply taking the sum of each and dividing by the
number of preceding and inclusive day(s) produces the wrong result -
e.g. if Tue count was 14 an Mon was zero I would get 7, instead of 14.

If on Tue I question, if Mon is zero (or Null) then the answer for the
running average is count of Tue - but as the days in the week pass the
permutations available to check zero days grow. Each subsequent day I
would need to ask which preceding days were zero and then divide
txtTotal by the number of days up to and including the current day that
were >0

Never thought it would be so frustrating.
Can the calculation be done in a query? or in a module in code?
While I am attempting to do this in a form using calculated fields, I
guess I could also send it to a report, but would be asking the same
questions.

Thanks,
WSF

 

[Marshall Barton]

Hello Marshall and Jeff,
I guess I am asking how can I calculate the running averages coz my
attempts have failed.

I have a job record table and need to report the Weekly count of jobs by
WeekNo showing each day of that week. Some days there may be no jobs at all.

I am trying to present it thus:
e.g.
Day> Mon Tue Wed Thu Fri Total
Count> 12 14 19 23 27 95
This is no problem presenting either in the form or a report.
What I would like to do is present below each day a running average per
day for those days that are greater than zero only.

Lets assume these text boxes are named txtMon, txtTue etc and txtTotal.
Below each I have a text box txtAvg2Mon, txtAvg2Tue etc
therefore:
Run/Avg> ? ? ? ? ?

That is where I am stuck.
I have been trying to do it with the txtAvg2### as an unbound
calculated control. Simply taking the sum of each and dividing by the
number of preceding and inclusive day(s) produces the wrong result -
e.g. if Tue count was 14 an Mon was zero I would get 7, instead of 14.

If on Tue I question, if Mon is zero (or Null) then the answer for the
running average is count of Tue - but as the days in the week pass the
permutations available to check zero days grow. Each subsequent day I
would need to ask which preceding days were zero and then divide
txtTotal by the number of days up to and including the current day that
were >0

Never thought it would be so frustrating.
Can the calculation be done in a query? or in a module in code?
While I am attempting to do this in a form using calculated fields, I
guess I could also send it to a report, but would be asking the same
questions.

Since, from your description, this appears to be an
un-normalized set of values, you can't use the aggregate
functions.

You could use a cumbersom expression such as:

txtCount text box expression
=IIf(Nz(txtMon,0)>0,1,0)+IIf(Nz(txTue,0)>0,1,0)+...

txtTotal text box expression
=Nz(txtMon,0)+Nz(txTue,0)+...

txtAvg text box
=txtTotal / txtCount