Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
I think I'm in need of some type of index & match formula. I have column
headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
If I understand what you're asking for correctly, I think we can do this
another way (not sure if INDEX/MATCH would work anyhow). If I understand you, you want a formula on row 3 (or somewhere) that will total the values in columns AN, AT, AZ, BF, BL, BR, BX, CD, CJ, CP, CU and DA based on the month and day of the month. For row 3 in those columns, the day of the month must be the 3rd or after, and the month must be less than the current month (using TODAY()). So on the 5th of April, the formulas should return results for the 3rd, 4th and 5th of Jan, Feb and March. We can use a relatively simple formula repeated with minor modifications to get the answer. These formulas are assumed to go in the same row (3 through 15) that you are interested in. Here is the basic format of the formula for the values in row 3 (3rd of the month) =IF(AND(MONTH(AN$1)<MONTH(TODAY()),(DAY(TODAY())= ROW())),AN3,0) to modify that to get the value for Feb, column AT all we do is change the two instances of AN in it: =IF(AND(MONTH(AT$1)<MONTH(TODAY()),(DAY(TODAY())= ROW())),AT3,0) we can repeat this logic all the way through November, but December is a special case since there is no month #13. for December (column DA) the formula would look like this: =IF(AND(MONTH(DA$1)=MONTH(TODAY()),(DAY(TODAY())= ROW())),DA3,0) What we do is simply add the results of these tests together in a single formula (looks long and ugly, but examine it and you'll see it is just the basic formula repeated 11 times plus the December formula and adding the results of each together: =IF(AND(MONTH(AN$1)<MONTH(TODAY()),(DAY(TODAY())= ROW())),AN3,0)+IF(AND(MONTH(AT$1)<MONTH(TODAY()),( DAY(TODAY())=ROW())),AT3,0)+IF(AND(MONTH(AZ$1)<MO NTH(TODAY()),(DAY(TODAY())=ROW())),AZ3,0)+IF(AND( MONTH(BF$1)<MONTH(TODAY()),(DAY(TODAY())=ROW())), BF3,0)+IF(AND(MONTH(BL$1)<MONTH(TODAY()),(DAY(TODA Y())=ROW())),BL3,0)+IF(AND(MONTH(BR$1)<MONTH(TODA Y()),(DAY(TODAY())=ROW())),BR3,0)+IF(AND(MONTH(BX $1)<MONTH(TODAY()),(DAY(TODAY())=ROW())),BX3,0)+I F(AND(MONTH(CD$1)<MONTH(TODAY()),(DAY(TODAY())=RO W())),CD3,0)+IF(AND(MONTH(CJ$1)<MONTH(TODAY()),(DA Y(TODAY())=ROW())),CJ3,0)+IF(AND(MONTH(CP$1)<MONT H(TODAY()),(DAY(TODAY())=ROW())),CP3,0)+IF(AND(MO NTH(CU$1)<MONTH(TODAY()),(DAY(TODAY())=ROW())),CU 3,0)+IF(AND(MONTH(DA$1)=MONTH(TODAY()),(DAY(TODAY( ))=ROW())),DA3,0) you can actually put that into a cell in row 3 and then fill it down to row 15 (and beyond) and it will work. Since you only go to row 15, we don't have to worry about months with <31 or <30 days, all months have at least 12 days. As written, there may be an effect you don't want - the values in the formulas will always be zero after the current day of the month for all columns. I mean if it is Dec 4, then you'll only see entries in rows 3 and 4 because of the day checks in the formulas. If you need to see all 12 rows for months Jan-Nov with values for Dec added to rows 3 and 4, then I've got some additional work to do. Or maybe what I've done will inspire someone to do some other creative thinking and come up with a simpler formula that deals with the days and offsets between columns better. I actually thought about doing this with OFFSET() and some formula involving the month and day, but haven't delved into that yet. My gut feeling on that at this point is that it is a 6 of 1/half-dozen of the other kind of deal, with a formula using OFFSET() looking even uglier. "SJT" wrote: I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
Try this:
Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
Damn, that's sweet - but take a look at it a little close, I'm coming up
short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
Let me clarify:
I filled up rows 3-15 in each of the columns with values 1 through 12, and the total I'm getting when using a test date of 1/4/2008 is only 10, not the expected 12. "JLatham" wrote: Damn, that's sweet - but take a look at it a little close, I'm coming up short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
I interpreted the post differently than you.
I read it to mean there is a single date for each month (the first of each month) spaced evenly every 6th cell starting from column AN and the OP is basically looking for a YTD sum. Biff "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Damn, that's sweet - but take a look at it a little close, I'm coming up short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
First: Apology - the reason I was getting 10 vs 12 was that between October
and November I only had 4 empty columns, not 5, so that threw November/December out of "view" of your excellent formula. I also don't think that the way you interpreted it will affect things at all since he is depending on TODAY() rather than some typed in date. I think your solution is the one he's looking for. You get a HELPFUL from me - I'd thought about using SUMPRODUCT() also, but hadn't gotten past the offsets between columns and had not even thought about MOD to get there. Definitely helpful in my learning curve. "T. Valko" wrote: I interpreted the post differently than you. I read it to mean there is a single date for each month (the first of each month) spaced evenly every 6th cell starting from column AN and the OP is basically looking for a YTD sum. Biff "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Damn, that's sweet - but take a look at it a little close, I'm coming up short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
Thank you so much for all of your help. Will give it a try and let you know
if I need any additional assistance. Thanks so much again. "JLatham" wrote: First: Apology - the reason I was getting 10 vs 12 was that between October and November I only had 4 empty columns, not 5, so that threw November/December out of "view" of your excellent formula. I also don't think that the way you interpreted it will affect things at all since he is depending on TODAY() rather than some typed in date. I think your solution is the one he's looking for. You get a HELPFUL from me - I'd thought about using SUMPRODUCT() also, but hadn't gotten past the offsets between columns and had not even thought about MOD to get there. Definitely helpful in my learning curve. "T. Valko" wrote: I interpreted the post differently than you. I read it to mean there is a single date for each month (the first of each month) spaced evenly every 6th cell starting from column AN and the OP is basically looking for a YTD sum. Biff "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Damn, that's sweet - but take a look at it a little close, I'm coming up short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
Thank you so much for all of your assistance. BTW what does the "--" before
and after the MOD function do? Thanks again. Really appreciate it. "T. Valko" wrote: I interpreted the post differently than you. I read it to mean there is a single date for each month (the first of each month) spaced evenly every 6th cell starting from column AN and the OP is basically looking for a YTD sum. Biff "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Damn, that's sweet - but take a look at it a little close, I'm coming up short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
These expressions will return arrays of either TRUE or FALSE
(MOD(COLUMN(AN3:IV3),6)=4) (AN$1:IV$1<=TODAY()) The double unary "--" coerces those logical values to numeric values: --TRUE = 1 --FALSE = 0 See these for more info: http://mcgimpsey.com/excel/formulae/doubleneg.html http://xldynamic.com/source/xld.SUMPRODUCT.html Biff "SJT" wrote in message ... Thank you so much for all of your assistance. BTW what does the "--" before and after the MOD function do? Thanks again. Really appreciate it. "T. Valko" wrote: I interpreted the post differently than you. I read it to mean there is a single date for each month (the first of each month) spaced evenly every 6th cell starting from column AN and the OP is basically looking for a YTD sum. Biff "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Damn, that's sweet - but take a look at it a little close, I'm coming up short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
That's great. You have been incredibly helpful. One last thing. Why does
the MOD function get set to "=4"? Promise not to bother you w/ this formula again. "T. Valko" wrote: These expressions will return arrays of either TRUE or FALSE (MOD(COLUMN(AN3:IV3),6)=4) (AN$1:IV$1<=TODAY()) The double unary "--" coerces those logical values to numeric values: --TRUE = 1 --FALSE = 0 See these for more info: http://mcgimpsey.com/excel/formulae/doubleneg.html http://xldynamic.com/source/xld.SUMPRODUCT.html Biff "SJT" wrote in message ... Thank you so much for all of your assistance. BTW what does the "--" before and after the MOD function do? Thanks again. Really appreciate it. "T. Valko" wrote: I interpreted the post differently than you. I read it to mean there is a single date for each month (the first of each month) spaced evenly every 6th cell starting from column AN and the OP is basically looking for a YTD sum. Biff "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Damn, that's sweet - but take a look at it a little close, I'm coming up short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
Why does the MOD function get set to "=4"?
We need to find a criteria that is common to the specific columns where your data is located. Column AN = column number 40 Column AT = column number 46 Column AZ = column number 52 This pattern of every 6th cell repeats to the end of your range. What is common about those columns is that when you divide the column number by 6 the remainder is 4: =MOD(COLUMN(AN1),6) = 4 =MOD(COLUMN(AT1),6) = 4 =MOD(COLUMN(AZ1),6) = 4 The formula limits the calculation to only those columns where the mod of the column number equals 4. Biff "SJT" wrote in message ... That's great. You have been incredibly helpful. One last thing. Why does the MOD function get set to "=4"? Promise not to bother you w/ this formula again. "T. Valko" wrote: These expressions will return arrays of either TRUE or FALSE (MOD(COLUMN(AN3:IV3),6)=4) (AN$1:IV$1<=TODAY()) The double unary "--" coerces those logical values to numeric values: --TRUE = 1 --FALSE = 0 See these for more info: http://mcgimpsey.com/excel/formulae/doubleneg.html http://xldynamic.com/source/xld.SUMPRODUCT.html Biff "SJT" wrote in message ... Thank you so much for all of your assistance. BTW what does the "--" before and after the MOD function do? Thanks again. Really appreciate it. "T. Valko" wrote: I interpreted the post differently than you. I read it to mean there is a single date for each month (the first of each month) spaced evenly every 6th cell starting from column AN and the OP is basically looking for a YTD sum. Biff "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Damn, that's sweet - but take a look at it a little close, I'm coming up short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & Match
We need to find a criteria that is common to the specific columns where
your data is located. Actually, that should read: We need to find a criteria that is common and unique to the specific columns where your data is located. For example, if we tried using a divisor of 2 the remainder would be 0. But this criteria includes columns that are not the specific columns of interest. This criteria would include columns: 40,42,44,46,48,50,52 So, using a divisor of 6 sets the mod equal to 4 and this criteria is both common and unique to only those columns of interest. Biff "T. Valko" wrote in message ... Why does the MOD function get set to "=4"? We need to find a criteria that is common to the specific columns where your data is located. Column AN = column number 40 Column AT = column number 46 Column AZ = column number 52 This pattern of every 6th cell repeats to the end of your range. What is common about those columns is that when you divide the column number by 6 the remainder is 4: =MOD(COLUMN(AN1),6) = 4 =MOD(COLUMN(AT1),6) = 4 =MOD(COLUMN(AZ1),6) = 4 The formula limits the calculation to only those columns where the mod of the column number equals 4. Biff "SJT" wrote in message ... That's great. You have been incredibly helpful. One last thing. Why does the MOD function get set to "=4"? Promise not to bother you w/ this formula again. "T. Valko" wrote: These expressions will return arrays of either TRUE or FALSE (MOD(COLUMN(AN3:IV3),6)=4) (AN$1:IV$1<=TODAY()) The double unary "--" coerces those logical values to numeric values: --TRUE = 1 --FALSE = 0 See these for more info: http://mcgimpsey.com/excel/formulae/doubleneg.html http://xldynamic.com/source/xld.SUMPRODUCT.html Biff "SJT" wrote in message ... Thank you so much for all of your assistance. BTW what does the "--" before and after the MOD function do? Thanks again. Really appreciate it. "T. Valko" wrote: I interpreted the post differently than you. I read it to mean there is a single date for each month (the first of each month) spaced evenly every 6th cell starting from column AN and the OP is basically looking for a YTD sum. Biff "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Damn, that's sweet - but take a look at it a little close, I'm coming up short 2 months. I filled each of the columns from 3 to 15 with increasing values starting at 1, so for 12 months with date after 3rd, I'd expect to get a total of 12 for that row, but I'm only getting 12. I put your formula into both columns AJ and AL with the same results: 10 instead of the expected 12. To test on out into the future I changed the formula to point to a cell with a manually entered date instead of using TODAY() just to test this kind of thing. For my test date I used 1/4/2008 and just cannot get it to come up with anything but 10 for those. But yours takes care of the whole situation much better than my ugly beastie in general - specifically the values in rows corresponding to days after current day of the month. "T. Valko" wrote: Try this: Row 1 = date headers =SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3) Adjust for the end of the range Copy down as needed Biff "SJT" wrote in message ... I think I'm in need of some type of index & match formula. I have column headings that are dates and below them are a series of numbers. I would like a formula that adds the numbers below the column headings based on today's date. For example, in column AN I have a column heading of 1/1/07 and below the column heading I have numerical data in rows 3 -15. In column AT I have the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07. This continues in the same pattern for each month of the year and below each column heading is data in rows 3 - 15. I would like to add the cumulative total of each row for each column depending on the date. Since today is 3/3, for example I would like to add the total of Cells AT3 and AN3 in one cell and then have a similar formula for each of the rows 4-15. On 4/1 this formula would add to the previous totals the data in Column AZ rows 3 - 15. Would appreciate any help you could provide. Thank you in advance for your assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
trying index match | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions | |||
Match and Index | Excel Worksheet Functions |