Trying to total a running average?

[craigw79]

Hello, how do I keep a running monthly average in excel? I have a simple
average function, but it includes the months for which I have not entered
data yet. Is there a way to make excel either ignore these cells until I
have entered data or divide by the number of months in which total is greater
than 1000?

 

[joeu2004]

Hello, how do I keep a running monthly average in excel?  I have a simple
average function, but it includes the months for which I have not entered
data yet.  Is there a way to make excel either ignore these cells until I
have entered data

Sure. But the precise answer depends on the design of your
worksheet. Please post some details, if you want further assistance.

In the simplest case, suppose that each month's total appears in
A1:A12, and B1:B12 should contain the average of all months year-to-
date. Then put the following formula into B1 and copy down through
B12:

=if(A1="", "", average($A$1:A1))

If you want the average to appear only next to the last month with
data, put the following formula into B1 and copy down through B12 (and
be sure that A13 is blank):

=if(or(A1="",A2<>""), "", average($A$1:A1))

HTH.

 

[joeu2004]

PS....

put the following formula into B1 and copy down through B12:
=if(A1="", "", average($A$1:A1))
[...or...]
=if(or(A1="",A2<>""), "", average($A$1:A1))

As I said, the details depend on the design of your worksheet. I ass-
u-me-d you might want the running average to appear next to the (last)
monthly total.

If you would like one cell to track the running average, use the
following, assuming again that the monthly totals are in A1:A12:

=if(A1="", "", average(offset(A1:A12, 0, 0, count(A1:A12))))

Hope one of those formulas points you in the right direction, and you
can adapt it to your situation.

 

[craigw79]

Thank you, but I couldn't get it to work. Here are some more details:

The monthly totals are displayed in a row from cells C22
They are summed from data in column b5:b381
I only have values for January and up to current date
All other cells are empty
In cell j6, I am trying to determine a monthly average

This is where the problem occurs. Excel figures a twelve month average
C22, but there is no data for remaining months so it divides by 12. At the
start of a new month, I would like the program to figure a new monthly
average based on the calendar dates.

I hope this is making sense

PS....

put the following formula into B1 and copy down through B12:
=if(A1="", "", average($A$1:A1))
[...or...]
=if(or(A1="",A2<>""), "", average($A$1:A1))

As I said, the details depend on the design of your worksheet. I ass-
u-me-d you might want the running average to appear next to the (last)
monthly total.

If you would like one cell to track the running average, use the
following, assuming again that the monthly totals are in A1:A12:

=if(A1="", "", average(offset(A1:A12, 0, 0, count(A1:A12))))

Hope one of those formulas points you in the right direction, and you
can adapt it to your situation.

 

[joeu2004]

The monthly totals are displayed in a row from cells C22
[....]
In cell j6, I am trying to determine a monthly average

Then put the 3rd formula that I offered into J6, with the following
changes. Specifically:

=if(C2="", "", average(offset(C22, 0, 0, 1, count(C22))))

Note that the OFFSET "height" argument is now 1, and COUNT is the
"width" argument. Read about OFFSET in the Help page.

To understand the formula, first, test it by successively entering
made-up numbers for the months that you do not have -- Feb, Mar, etc
until you are satisfied that it works.

Second, if you have Excel 2003, use Tools>Formula Auditing>Evaluate
Formula to see how the formula is evaluated. It should answer any
lingering questions that you might have. (But feel free to post back
with any unanswered questions.)

PS: It might be prudent to round the average. For example, if the
monthly totals are currency, use ROUND(AVERAGE(....),2), where "..."
means the OFFSET expression above.

 Excel figures a twelve month average C22, but there is
no data for remaining months so it divides by 12.

Ah, C22 would be 14 months. Do you want the average to be limited
to the most recent 12 months, if there is more than 12 months of data?