Adding Row Number to Column
 

I have a worksheet with consecutive dates in column A and blood glucose
figures across the page in 5 columns. I wish to be able to enter start and
finish dates and automatically find those dates and calulate the averages
betwen dates. I can find the ROW by using "Match" but don't know how to use
this info to identify the columns I wish to "AVERAGE" or "SUM". All help
appreciated

Adding Row Number to Column
 

I have headings (labels) in row 1 (Date, A,B,C,D,E)
In A2:30 I have dates
In B2:F30 I have numeric values
In H1 I have a start date, in I1 an end date
In J1 I used
=SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1)*B2:F30)/(SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1))*5)
to find the average of the values.
The = and <= means I include both dates.
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Len C" <Len wrote in message
...
I have a worksheet with consecutive dates in column A and blood glucose
figures across the page in 5 columns. I wish to be able to enter start and
finish dates and automatically find those dates and calulate the averages
betwen dates. I can find the ROW by using "Match" but don't know how to
use
this info to identify the columns I wish to "AVERAGE" or "SUM". All help
appreciated

Adding Row Number to Column
 

Thank you Bernard.

I would to take things one stage furtherif possible : by only ever entering
start and end dates in blank cells on a different page I would like excel to
associate each of those dates ( which are anything from two weeks to 6 months
apart) with a ROW and, then using that ROW number start to "SUM" and/or
"AVERAGE" all the information between those rows in the specified columns on
the worksheet. i.e if "MATCH" identifies row 22 ( by date input), how do I
get excel to take this number (22) and add it to a column to genertate a
Cell Reference to use within a SUM/AVERAGE calculation?

This would save me having to do everything manually everytime a report is
needed.



"Bernard Liengme" wrote:

I have headings (labels) in row 1 (Date, A,B,C,D,E)
In A2:30 I have dates
In B2:F30 I have numeric values
In H1 I have a start date, in I1 an end date
In J1 I used
=SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1)*B2:F30)/(SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1))*5)
to find the average of the values.
The = and <= means I include both dates.
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Len C" <Len wrote in message
...
I have a worksheet with consecutive dates in column A and blood glucose
figures across the page in 5 columns. I wish to be able to enter start and
finish dates and automatically find those dates and calulate the averages
betwen dates. I can find the ROW by using "Match" but don't know how to
use
this info to identify the columns I wish to "AVERAGE" or "SUM". All help
appreciated

Adding Row Number to Column
 

With the data set out in the same way as in my first reply:
In H2 I used =MATCH(H1,$A$2:$A$30,0)+1 to locate the row with the start date
and In I2 =MATCH(I1,$A$2:$A$30,0)+1 to locate the row with the stop date.
Note that the +1 is needed since the dates begin in row 2.
In H3 I used =AVERAGE(INDIRECT("B"&H2&":F"&I2)) to find the average
Or, if you want to use just one cell,
=AVERAGE(INDIRECT("B"&MATCH(H1,$A$2:$A$21,0)+1&":F "&MATCH(I1,$A$2:$A$21,0)+1))

However, I am much happier with my first method (SUMPRODUCT).
For one thing, if the data is moved (or rows inserted) I will need to
remember to fix the +1 bit of the formula =MATCH(H1,$A$2:$A$30,0)+1

Tell me why you were not happy with SUMPRODUCT.
Maybe we should continue this chat privately (just remove TRUENORTH.) from
my email address
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"len c" wrote in message
...
Thank you Bernard.

I would to take things one stage furtherif possible : by only ever
entering
start and end dates in blank cells on a different page I would like excel
to
associate each of those dates ( which are anything from two weeks to 6
months
apart) with a ROW and, then using that ROW number start to "SUM" and/or
"AVERAGE" all the information between those rows in the specified columns
on
the worksheet. i.e if "MATCH" identifies row 22 ( by date input), how do
I
get excel to take this number (22) and add it to a column to genertate a
Cell Reference to use within a SUM/AVERAGE calculation?

This would save me having to do everything manually everytime a report is
needed.



"Bernard Liengme" wrote:

I have headings (labels) in row 1 (Date, A,B,C,D,E)
In A2:30 I have dates
In B2:F30 I have numeric values
In H1 I have a start date, in I1 an end date
In J1 I used
=SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1)*B2:F30)/(SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1))*5)
to find the average of the values.
The = and <= means I include both dates.
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Len C" <Len wrote in message
...
I have a worksheet with consecutive dates in column A and blood glucose
figures across the page in 5 columns. I wish to be able to enter start
and
finish dates and automatically find those dates and calulate the
averages
betwen dates. I can find the ROW by using "Match" but don't know how to
use
this info to identify the columns I wish to "AVERAGE" or "SUM". All
help
appreciated

Adding Row Number to Column
 

Did you want separate averages for the B. C, D, E and F columns?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"len c" wrote in message
...
Thank you Bernard.

I would to take things one stage furtherif possible : by only ever
entering
start and end dates in blank cells on a different page I would like excel
to
associate each of those dates ( which are anything from two weeks to 6
months
apart) with a ROW and, then using that ROW number start to "SUM" and/or
"AVERAGE" all the information between those rows in the specified columns
on
the worksheet. i.e if "MATCH" identifies row 22 ( by date input), how do
I
get excel to take this number (22) and add it to a column to genertate a
Cell Reference to use within a SUM/AVERAGE calculation?

This would save me having to do everything manually everytime a report is
needed.



"Bernard Liengme" wrote:

I have headings (labels) in row 1 (Date, A,B,C,D,E)
In A2:30 I have dates
In B2:F30 I have numeric values
In H1 I have a start date, in I1 an end date
In J1 I used
=SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1)*B2:F30)/(SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1))*5)
to find the average of the values.
The = and <= means I include both dates.
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Len C" <Len wrote in message
...
I have a worksheet with consecutive dates in column A and blood glucose
figures across the page in 5 columns. I wish to be able to enter start
and
finish dates and automatically find those dates and calulate the
averages
betwen dates. I can find the ROW by using "Match" but don't know how to
use
this info to identify the columns I wish to "AVERAGE" or "SUM". All
help
appreciated

Adding Row Number to Column
 

Alternative formula
=AVERAGE(INDEX(B2:F30,MATCH(H1,$A$2:$A$30,0),1):IN DEX(B2:F30,MATCH(I1,$A$2:$A$30,0),5))

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"len c" wrote in message
...
Thank you Bernard.

I would to take things one stage furtherif possible : by only ever
entering
start and end dates in blank cells on a different page I would like excel
to
associate each of those dates ( which are anything from two weeks to 6
months
apart) with a ROW and, then using that ROW number start to "SUM" and/or
"AVERAGE" all the information between those rows in the specified columns
on
the worksheet. i.e if "MATCH" identifies row 22 ( by date input), how do
I
get excel to take this number (22) and add it to a column to genertate a
Cell Reference to use within a SUM/AVERAGE calculation?

This would save me having to do everything manually everytime a report is
needed.



"Bernard Liengme" wrote:

I have headings (labels) in row 1 (Date, A,B,C,D,E)
In A2:30 I have dates
In B2:F30 I have numeric values
In H1 I have a start date, in I1 an end date
In J1 I used
=SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1)*B2:F30)/(SUMPRODUCT((A2:A30=H1)*(A2:A30<=I1))*5)
to find the average of the values.
The = and <= means I include both dates.
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Len C" <Len wrote in message
...
I have a worksheet with consecutive dates in column A and blood glucose
figures across the page in 5 columns. I wish to be able to enter start
and
finish dates and automatically find those dates and calulate the
averages
betwen dates. I can find the ROW by using "Match" but don't know how to
use
this info to identify the columns I wish to "AVERAGE" or "SUM". All
help
appreciated