Excel Formula to Calc Daily Averages?

[Don I]

I'm hoping someone can help me figure out a way to create an Excel
worksheet that will help me to keep track of my daily glucose logs and
give me the average of the last 7 days and for the last 30 days. As
time goes on, I'll add more columns to the excel sheet to represent
add'l dates.

The table below is meant to serve as an example of such an excel sheet
cells A2 and B2 are the cells I'm trying to put these averages in. If
today is 7/16/06, A2 should show the averages of cells D4 through
K10--but exclude from the average any EMPTY cells.

It seems like there should be a way--but I'm just not seeing it.

Thanks.
Don I

NOTE: Regarding the table that I spoke of above, I tried to show a
table to illustrate what I'm asking about, but my reader won't allow me
to do it. The following link shows the table in question--and nothing
else. No images--no banner ads nothing but the table.

www.donireland.com/ExcelFormulasForAveraging.html

 

[Franz Verga]

I'm hoping someone can help me figure out a way to create an Excel
worksheet that will help me to keep track of my daily glucose logs and
give me the average of the last 7 days and for the last 30 days. As
time goes on, I'll add more columns to the excel sheet to represent
add'l dates.

The table below is meant to serve as an example of such an excel sheet
cells A2 and B2 are the cells I'm trying to put these averages in. If
today is 7/16/06, A2 should show the averages of cells D4 through
K10--but exclude from the average any EMPTY cells.

Hi Don,

Strange table...

In Excel we have letters for columns and numbers for rows, but your table
has letters for rows and numbers for columns...

You need just the AVERAGE function: just type (I'll use the references from
the table you linked, but they should not correspond to the real references
in Excel...) in A2:

=AVERAGE(D4:K10)

press Enter, select again A2 and drag the cell till A10. As you can see the
references will change in each cell.

The same is for a 30 days average.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

See if this get you headed in the right direction...

With
Dates entered in B3:AZ3 (in ascending order)
Blood sugar values in B4:AZ11

A1: 7 day average
B1:
=AVERAGE(INDEX(B11:AZ11,1,MATCH(LARGE(B3:AZ3,7),B3:AZ3,0)):INDEX(B4:AZ4,1,MATCH(LARGE(B3:AZ3,1),B3:AZ3,0)))

A2: 30 day average
B2:
=AVERAGE(INDEX(B11:AZ11,1,MATCH(LARGE(B3:AZ3,MIN(COUNT(B3:AZ3),30)),B3:AZ3,0)):INDEX(B4:AZ4,1,MATCH(LARGE(B3:AZ3,1),B3:AZ3,0)))

Note: Since text wrap will impact those formulas, they DO NOT contain any
blanks.

Adjust range references to suit your situation.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Don I]

Thanks I think that'll give me a pretty good start. Just needs a little
tweaking.

 

[Don I]

<<Strange table...

In Excel we have letters for columns and numbers for rows, but your table
has letters for rows and numbers for columns...>>
Yes I realized that after I saw Ron's reply.

The problem with average (yes I saw it before posting and tried it) is that
if one of the cells in the range is blank, it's included in the average as a
ZERO. Also, I only want to include the last 7 or 30 days worth of numbers.

Ron's answer does what I'm looking for--but I found out after the fact that
Docs To Go doesn't seem to support the "LARGE" function which is part of
Ron's solution.

 

[Franz Verga]

<<Strange table...

In Excel we have letters for columns and numbers for rows, but your
table has letters for rows and numbers for columns...>>
Yes I realized that after I saw Ron's reply.

The problem with average (yes I saw it before posting and tried it)
is that if one of the cells in the range is blank, it's included in
the average as a ZERO. Also, I only want to include the last 7 or 30
days worth of numbers.

Are you sure?

AFAIK the AVERAGE function ignores blank cells, logical values and text,
while the AVERAGEA function uses also these values...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy