Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding up number of yes, no, maybes in a column | Excel Worksheet Functions | |||
Adding number to each cell in a column | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Running total w/2 columns - Excel | Excel Worksheet Functions |