Averages

[KirbyCTB]

I have a column of numbers that I add to nearly every day.

The whole column will represent the entire year, which I break into
months with a simple colored bar.

I need a continually updated average of those numbers for the current
month each time I enter a new value. The range would be A1:A7 on day
1, but by day 10 could be A1:A13

At the end of the month, I need to shift the updating average to the
next month and leave the "closed months" alone.

How do I set this up, and what is it called in "Excel-speak" so I can
look it up with the proper terminology in the future? Thx.

 

[T. Valko]

Are there dates associated with these numbers?

It sounds like what you want is an average for the current month. But, if
there are no dates to go by .................

Biff

 

[Guest]

Let's say your data is in column B from B1 thru B366, being built day-by-day.

In column A record the month ( 1 for January, etc.) then:

=SUMPRODUCT(--(A1:A366=1),--(B1:B366))/COUNTIF(A1:A366,1)
for Jan

=SUMPRODUCT(--(A1:A366=2),--(B1:B366))/COUNTIF(A1:A366,2)
for Feb

This will "grow" the averages. If you try to go too far into the future,
you will get zero divide errors.

 

[Guest]

Have you tried the Average A function?

 

[KirbyCTB]

Are there dates associated with these numbers?

It sounds like what you want is an average for the current month. But, if
there are no dates to go by .................

Biff










- Show quoted text -

This spreadsheet is about 27 columns wide and contains a variety of
data. What I need to do is isolate one value for each of my patients,
and average it whenever I add new patients in that month. All I care
about is that value for this purpose -- it doesn't need to link to any
of the other data, except that once I have entered my patients for the
month, I can switch this continuous update average to the next month
-- lower down on the same spreadsheet. The numbers that will be
averaged are all contiguous in one column -- spaces (rows) occur
between months.

 

[T. Valko]

??????

Biff

This spreadsheet is about 27 columns wide and contains a variety of
data. What I need to do is isolate one value for each of my patients,
and average it whenever I add new patients in that month. All I care
about is that value for this purpose -- it doesn't need to link to any
of the other data, except that once I have entered my patients for the
month, I can switch this continuous update average to the next month
-- lower down on the same spreadsheet. The numbers that will be
averaged are all contiguous in one column -- spaces (rows) occur
between months.