Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need rolling summary of data
Have data on a spreadsheet:
Shipdate Quantity The data goes back 5 years, and is constantly updated with new shipments being added. Would like to have on another sheet a rolling summary of how many have been shipped within the last 12 months, the previous 12 months to that, etc, up to 5 years. So the summary would look something like this: Present to 12-Mos. 29 13-24 Mos. 55 25-36 Mos. 36 37-48 Mos. 55 49-60 Mos 39 TIA for any ideas on this. Pierre |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need rolling summary of data
something like this where c1 & d1 contain your desired dates =sumproduct((month(a2:a22)=c1-2)*(month(a2:a22)<=d1-2)*b2:b22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Pierre" wrote in message oups.com... Have data on a spreadsheet: Shipdate Quantity The data goes back 5 years, and is constantly updated with new shipments being added. Would like to have on another sheet a rolling summary of how many have been shipped within the last 12 months, the previous 12 months to that, etc, up to 5 years. So the summary would look something like this: Present to 12-Mos. 29 13-24 Mos. 55 25-36 Mos. 36 37-48 Mos. 55 49-60 Mos 39 TIA for any ideas on this. Pierre |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need rolling summary of data
On Oct 22, 11:47 am, "Don Guillett" wrote:
something like this where c1 & d1 contain your desired dates =sumproduct((month(a2:a22)=c1-2)*(month(a2:a22)<=d1-2)*b2:b22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Pierre" wrote in message oups.com... Have data on a spreadsheet: Shipdate Quantity The data goes back 5 years, and is constantly updated with new shipments being added. Would like to have on another sheet a rolling summary of how many have been shipped within the last 12 months, the previous 12 months to that, etc, up to 5 years. So the summary would look something like this: Present to 12-Mos. 29 13-24 Mos. 55 25-36 Mos. 36 37-48 Mos. 55 49-60 Mos 39 TIA for any ideas on this. Pierre- Hide quoted text - - Show quoted text - Don, thanks for your reply. Having a bit of a time though making it work. Col A is ship date. Col B is quantity shipped Looking for a calculation to give the quantity shipped within the last 12 months, lets say in D2. In D3 looking for the quantity shipped from months 13-24, and so forth. The suggestion to place in my desired dates dates in row 1 confuses me a tad. I'll have row labels in column C beginning on row 2 for the first group containing the shipments for the last 12 months. Thanks again for any second look. Pierre |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need rolling summary of data
On Oct 22, 2:02 pm, "Don Guillett" wrote:
How about something like this? =sumproduct((month(a2:a22)month(today())-12)*(month(a2:a22)<=month(today()*)-0)*b2:b22) You could probably incorporate something using row()*12 instead -- Don Guillett Microsoft MVP Excel SalesAid Software "Pierre" wrote in message oups.com... On Oct 22, 11:47 am, "Don Guillett" wrote: something like this where c1 & d1 contain your desired dates =sumproduct((month(a2:a22)=c1-2)*(month(a2:a22)<=d1-2)*b2:b22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Pierre" wrote in message groups.com... Have data on a spreadsheet: Shipdate Quantity The data goes back 5 years, and is constantly updated with new shipments being added. Would like to have on another sheet a rolling summary of how many have been shipped within the last 12 months, the previous 12 months to that, etc, up to 5 years. So the summary would look something like this: Present to 12-Mos. 29 13-24 Mos. 55 25-36 Mos. 36 37-48 Mos. 55 49-60 Mos 39 TIA for any ideas on this. Pierre- Hide quoted text - - Show quoted text - Don, thanks for your reply. Having a bit of a time though making it work. Col A is ship date. Col B is quantity shipped Looking for a calculation to give the quantity shipped within the last 12 months, lets say in D2. In D3 looking for the quantity shipped from months 13-24, and so forth. The suggestion to place in my desired dates dates in row 1 confuses me a tad. I'll have row labels in column C beginning on row 2 for the first group containing the shipments for the last 12 months. Thanks again for any second look. Pierre- Hide quoted text - - Show quoted text - Don, it wants to work, but I'm not sure what it wants to do. What do I place in C1 and D1? It appears that I copy down your formula; I've changed it to absolute references with the exception of C1. Col C has my date/month ranges read as follows: Present to 12 13-24 25-36 37-48 Col D would have the corresponding number of shipments. Thanks again. Pierre |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rolling 12 data | Excel Discussion (Misc queries) | |||
Rolling data | Excel Worksheet Functions | |||
Rolling data table?? | Charts and Charting in Excel | |||
How to Calculate a sum between a rolling data range. | Excel Discussion (Misc queries) | |||
Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions |