Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
12MMT - How ?
With your data in Sheet1 starting from A1 and the start date you want in
Sheet2 A1 put: =DATE(YEAR(A1),MONTH(A1)+12,DAY(A1)) In B1 Then use the formula: =SUMPRODUCT((Sheet1!A2:A106=A1)*(Sheet1!A2:A106<B 1)*Sheet1!C2:C106) Adjust to suit your needs. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rob L" wrote in message u... I have a table of data Month Hours Jan 06 2005 Feb 06 1750 Mar 06 3520 .... Mar 07 2300 I have a table on a separate sheet. I want to be able to put (say) Mar 07 in cell A1, and have the 12 month moving total (total from Mar 07 to April 06) in cell A2. If I change A1 to Feb 07, then the 12MMT range is from Feb 07 to Mar 06, and I want the total to change to reflect this. Can someone help with a formula please Thanks, Rob L |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
12MMT - How ?
Sumproduct will work just fine. When you enter the "search" date and you
enter it as Feb 07 the true underlying value of the cell is Feb 7 2007 even though you may have it formatted as mmm yy. However, this shouldn't be problem since your data dates are entered as 1 mmm yy. Ambiguous dates are a bad idea and can cause unexpected problems. Feb 07 is an ambiguous date. For the 12 month sum: A1 = Feb 07 =SUMPRODUCT(--(Sheet1!A1:A100DATE(YEAR(A1)-1,MONTH(A1),1)),--(Sheet1!A1:A100<=A1),Sheet1!B1:B100) For the 12 month average: I don't know how much data you have or when it starts or if you might have empty cells for certain months but assuming there is always enough data and no empty cells all you have to do is divide the sum by 12. However, I wouldn't trust all that so here's an average formula: =SUMPRODUCT(--(Sheet1!A1:A100DATE(YEAR(A1)-1,MONTH(A1),1)),--(Sheet1!A1:A100<=A1),Sheet1!B1:B100)/SUMPRODUCT(--(Sheet1!A1:A100DATE(YEAR(A1)-1,MONTH(A1),1)),--(Sheet1!A1:A100<=A1)) -- Biff Microsoft Excel MVP "Rob L" wrote in message u... I'm not sure that SUMPRODUCT is what I want (I've tried your formula without it doing what I wanted). I actually have a large sheet with various columns of data (hours, Employee Numbers, Lost Time Injuries etc). Column 1 contains months (01 mm yy formatted as mmm yy), in sequence. I need to be able to fill a table on Sheet 2 with various sums of the data, based on a month that I put in cell A1 on Sheet 2. So if I put Feb 07 in A1, I need the sum of the hours, employee numbers, and LTI's for the period Mar 06 - Feb 07, to go into cells B2, B3, and B4. If I change A1 to Jun 07, then the sums change to the sums for Jul 06 - Jun 07. The formula that I use to get the "Start Month" is =EOMonth(A1,-12)+1 (which works OK), but how do I get the Total (or Average) of the cells between these two dates, for the required column ? My thoughts turn towards an Array formula, but I'm not that proficient with them.... Thanks for any help. Rob L "Sandy Mann" wrote in message ... With your data in Sheet1 starting from A1 and the start date you want in Sheet2 A1 put: =DATE(YEAR(A1),MONTH(A1)+12,DAY(A1)) In B1 Then use the formula: =SUMPRODUCT((Sheet1!A2:A106=A1)*(Sheet1!A2:A106<B 1)*Sheet1!C2:C106) Adjust to suit your needs. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rob L" wrote in message u... I have a table of data Month Hours Jan 06 2005 Feb 06 1750 Mar 06 3520 .... Mar 07 2300 I have a table on a separate sheet. I want to be able to put (say) Mar 07 in cell A1, and have the 12 month moving total (total from Mar 07 to April 06) in cell A2. If I change A1 to Feb 07, then the 12MMT range is from Feb 07 to Mar 06, and I want the total to change to reflect this. Can someone help with a formula please Thanks, Rob L |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
12MMT - How ?
I have a table of data
Month Hours Jan 06 2005 Feb 06 1750 Mar 06 3520 ..... Mar 07 2300 I have a table on a separate sheet. I want to be able to put (say) Mar 07 in cell A1, and have the 12 month moving total (total from Mar 07 to April 06) in cell A2. If I change A1 to Feb 07, then the 12MMT range is from Feb 07 to Mar 06, and I want the total to change to reflect this. Can someone help with a formula please Thanks, Rob L |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
12MMT - How ?
I'm not sure that SUMPRODUCT is what I want (I've tried your formula without
it doing what I wanted). I actually have a large sheet with various columns of data (hours, Employee Numbers, Lost Time Injuries etc). Column 1 contains months (01 mm yy formatted as mmm yy), in sequence. I need to be able to fill a table on Sheet 2 with various sums of the data, based on a month that I put in cell A1 on Sheet 2. So if I put Feb 07 in A1, I need the sum of the hours, employee numbers, and LTI's for the period Mar 06 - Feb 07, to go into cells B2, B3, and B4. If I change A1 to Jun 07, then the sums change to the sums for Jul 06 - Jun 07. The formula that I use to get the "Start Month" is =EOMonth(A1,-12)+1 (which works OK), but how do I get the Total (or Average) of the cells between these two dates, for the required column ? My thoughts turn towards an Array formula, but I'm not that proficient with them.... Thanks for any help. Rob L "Sandy Mann" wrote in message ... With your data in Sheet1 starting from A1 and the start date you want in Sheet2 A1 put: =DATE(YEAR(A1),MONTH(A1)+12,DAY(A1)) In B1 Then use the formula: =SUMPRODUCT((Sheet1!A2:A106=A1)*(Sheet1!A2:A106<B 1)*Sheet1!C2:C106) Adjust to suit your needs. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rob L" wrote in message u... I have a table of data Month Hours Jan 06 2005 Feb 06 1750 Mar 06 3520 .... Mar 07 2300 I have a table on a separate sheet. I want to be able to put (say) Mar 07 in cell A1, and have the 12 month moving total (total from Mar 07 to April 06) in cell A2. If I change A1 to Feb 07, then the 12MMT range is from Feb 07 to Mar 06, and I want the total to change to reflect this. Can someone help with a formula please Thanks, Rob L |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
12MMT - How ?
Thanks Guys - once I got the correct ranges in - worked a treat.
RobL "Rob L" wrote in message u... I'm not sure that SUMPRODUCT is what I want (I've tried your formula without it doing what I wanted). I actually have a large sheet with various columns of data (hours, Employee Numbers, Lost Time Injuries etc). Column 1 contains months (01 mm yy formatted as mmm yy), in sequence. I need to be able to fill a table on Sheet 2 with various sums of the data, based on a month that I put in cell A1 on Sheet 2. So if I put Feb 07 in A1, I need the sum of the hours, employee numbers, and LTI's for the period Mar 06 - Feb 07, to go into cells B2, B3, and B4. If I change A1 to Jun 07, then the sums change to the sums for Jul 06 - Jun 07. The formula that I use to get the "Start Month" is =EOMonth(A1,-12)+1 (which works OK), but how do I get the Total (or Average) of the cells between these two dates, for the required column ? My thoughts turn towards an Array formula, but I'm not that proficient with them.... Thanks for any help. Rob L "Sandy Mann" wrote in message ... With your data in Sheet1 starting from A1 and the start date you want in Sheet2 A1 put: =DATE(YEAR(A1),MONTH(A1)+12,DAY(A1)) In B1 Then use the formula: =SUMPRODUCT((Sheet1!A2:A106=A1)*(Sheet1!A2:A106<B 1)*Sheet1!C2:C106) Adjust to suit your needs. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rob L" wrote in message u... I have a table of data Month Hours Jan 06 2005 Feb 06 1750 Mar 06 3520 .... Mar 07 2300 I have a table on a separate sheet. I want to be able to put (say) Mar 07 in cell A1, and have the 12 month moving total (total from Mar 07 to April 06) in cell A2. If I change A1 to Feb 07, then the 12MMT range is from Feb 07 to Mar 06, and I want the total to change to reflect this. Can someone help with a formula please Thanks, Rob L |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|