Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula based on variable range
I am using Excel 2003.
Cells A1 through X1 contain dates 01-Jan-2008, 01-Feb-2008 ... 01-Dec-2009. They are formatted Jan-2008, Feb-2008 ... Dec. 2009. The point is that the day does not matter. Cells A2 through X2 contain numbers corresponding to the dates (actually, the month and year represented by the date). Maybe something like 2, 1.5 ... 3. I would like to specify a month/year and see the total for the cells in the second row for that month\year and the 11 preceding it. For instance, July 2009 is the end of a twelve-month period August 2008 through July 2009. These values are in cells H1:S1. I want to specify July-2009 (by typing it into a cell (say C1), or selecting from month and year drop-down lists, or whatever works best) and see the total for the numbers in cells H2:S2. I realize the date range represented by cells H1:S1 is 01-Aug-2008 through 01- Jul-2009 going by the actual cell values, but that is not of consequence here. There is one number value for the entire month (in the cell directly below the one containing the date value). -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula based on variable range
Bruce,
If you are certain that the date will have at least 12 columns of data to sum =SUM(OFFSET($A$2,0,MATCH(C1,1:1)-12,1,12)) Otherwise, you need to make sure that you don't try to include the non-existent cells to the left of column A: =SUM(OFFSET($A$2,0,MATCH(D5,1:1)-MIN(MATCH(D5,1:1),12),1,MIN(MATCH(D5,1:1),12))) Just enter the desired end date into C1. HTH, Bernie MS Excel MVP "BruceM via OfficeKB.com" <u54429@uwe wrote in message news:9f41476f35c7b@uwe... I am using Excel 2003. Cells A1 through X1 contain dates 01-Jan-2008, 01-Feb-2008 ... 01-Dec-2009. They are formatted Jan-2008, Feb-2008 ... Dec. 2009. The point is that the day does not matter. Cells A2 through X2 contain numbers corresponding to the dates (actually, the month and year represented by the date). Maybe something like 2, 1.5 ... 3. I would like to specify a month/year and see the total for the cells in the second row for that month\year and the 11 preceding it. For instance, July 2009 is the end of a twelve-month period August 2008 through July 2009. These values are in cells H1:S1. I want to specify July-2009 (by typing it into a cell (say C1), or selecting from month and year drop-down lists, or whatever works best) and see the total for the numbers in cells H2:S2. I realize the date range represented by cells H1:S1 is 01-Aug-2008 through 01- Jul-2009 going by the actual cell values, but that is not of consequence here. There is one number value for the entire month (in the cell directly below the one containing the date value). -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula based on variable range
Ooops, all the D5s should have been C1s
=SUM(OFFSET($A$2,0,MATCH(C1,1:1)-MIN(MATCH(C1,1:1),12),1,MIN(MATCH(C1,1:1),12))) HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Bruce, If you are certain that the date will have at least 12 columns of data to sum =SUM(OFFSET($A$2,0,MATCH(C1,1:1)-12,1,12)) Otherwise, you need to make sure that you don't try to include the non-existent cells to the left of column A: =SUM(OFFSET($A$2,0,MATCH(D5,1:1)-MIN(MATCH(D5,1:1),12),1,MIN(MATCH(D5,1:1),12))) Just enter the desired end date into C1. HTH, Bernie MS Excel MVP "BruceM via OfficeKB.com" <u54429@uwe wrote in message news:9f41476f35c7b@uwe... I am using Excel 2003. Cells A1 through X1 contain dates 01-Jan-2008, 01-Feb-2008 ... 01-Dec-2009. They are formatted Jan-2008, Feb-2008 ... Dec. 2009. The point is that the day does not matter. Cells A2 through X2 contain numbers corresponding to the dates (actually, the month and year represented by the date). Maybe something like 2, 1.5 ... 3. I would like to specify a month/year and see the total for the cells in the second row for that month\year and the 11 preceding it. For instance, July 2009 is the end of a twelve-month period August 2008 through July 2009. These values are in cells H1:S1. I want to specify July-2009 (by typing it into a cell (say C1), or selecting from month and year drop-down lists, or whatever works best) and see the total for the numbers in cells H2:S2. I realize the date range represented by cells H1:S1 is 01-Aug-2008 through 01- Jul-2009 going by the actual cell values, but that is not of consequence here. There is one number value for the entire month (in the cell directly below the one containing the date value). -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula based on variable range
Thanks! I gave a somewhat simplified example, but I did some experiments and
can see how MATCH and OFFSET will get me to where I need to go. The problem was I didn't know about the existence of those functions. I did have some problems getting MATCH to match a date value unless I referred to a specific cell, or used the DATE or DATEVALUE function (probably some others, too). What I'm actually doing is having the user select the month from one drop-down box and the year from another, then combining those values using the Date function, with a 1 added for the day. As I said, I think this puts me on track, but now other things need to be done before I get back to this project, so I may not be able to work out the details right away. Thanks again for pointing me in what seems to be the right direction. Bernie Deitrick wrote: Ooops, all the D5s should have been C1s =SUM(OFFSET($A$2,0,MATCH(C1,1:1)-MIN(MATCH(C1,1:1),12),1,MIN(MATCH(C1,1:1),12))) HTH, Bernie MS Excel MVP Bruce, [quoted text clipped - 32 lines] There is one number value for the entire month (in the cell directly below the one containing the date value). -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting a range of rows based on a variable; syntax error | Excel Discussion (Misc queries) | |||
dynamic summed range based on a variable | Excel Worksheet Functions | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
How can I assign a range starting cell based on a variable locati. | Excel Discussion (Misc queries) | |||
How can I assign a range starting cell based on a variable locati. | Excel Worksheet Functions |