Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Daily Info to Weekly
I am trying to "pull" certain information from a Daily Table into a Weekly
Table. For example, in the two tables below, I am trying to pull the "Open" figure from the Daily Table into a Weekly Table, based on the criteria that as the week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will automatically pull the Open figure (i.e. 515) into the relevant cell. Daily Info Weekly Info WeekNum Date Open WeekNum Date Open 35 29/08/08 558 35 26/08/08 515 35 28/08/08 526.5 34 18/08/08 610 35 27/08/08 524.5 33 11/08/08 603 35 26/08/08 515 32 04/08/08 538.5 34 22/08/08 522 34 21/08/08 540 34 20/08/08 560 34 19/08/08 576 34 18/08/08 610 33 15/08/08 608 33 14/08/08 581 33 13/08/08 602 33 12/08/08 630 33 11/08/08 603 32 08/08/08 598.5 32 07/08/08 607 32 06/08/08 624.5 32 05/08/08 540.5 32 04/08/08 538.5 This really as me stumped!! Any help would be appreciated. Kind regards, Gavin. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Daily Info to Weekly
My other suggestion worked but adding a helper column with the week num
makes it easy! If D1 = weeknum = 35 Enter this formula in E1 and copy across to F1: =LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19) Select both E1 and F1 and copy down as needed. Format E1:En as Date -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... I am trying to "pull" certain information from a Daily Table into a Weekly Table. For example, in the two tables below, I am trying to pull the "Open" figure from the Daily Table into a Weekly Table, based on the criteria that as the week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will automatically pull the Open figure (i.e. 515) into the relevant cell. Daily Info Weekly Info WeekNum Date Open WeekNum Date Open 35 29/08/08 558 35 26/08/08 515 35 28/08/08 526.5 34 18/08/08 610 35 27/08/08 524.5 33 11/08/08 603 35 26/08/08 515 32 04/08/08 538.5 34 22/08/08 522 34 21/08/08 540 34 20/08/08 560 34 19/08/08 576 34 18/08/08 610 33 15/08/08 608 33 14/08/08 581 33 13/08/08 602 33 12/08/08 630 33 11/08/08 603 32 08/08/08 598.5 32 07/08/08 607 32 06/08/08 624.5 32 05/08/08 540.5 32 04/08/08 538.5 This really as me stumped!! Any help would be appreciated. Kind regards, Gavin. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Daily Info to Weekly
Biff,
I have to say that I am very impressed. I am still not sure how the formula works, but work it does!! I have one further "wrinkle" that I would like to add, if I may: The data I am looking at is over a number of years and, therefore, there are instances wher the WeekNum will be the same for different years. How do I get the formula to pull up the data relevant to the particular year only? Many, many thanks, Gavin. "T. Valko" wrote: My other suggestion worked but adding a helper column with the week num makes it easy! If D1 = weeknum = 35 Enter this formula in E1 and copy across to F1: =LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19) Select both E1 and F1 and copy down as needed. Format E1:En as Date -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... I am trying to "pull" certain information from a Daily Table into a Weekly Table. For example, in the two tables below, I am trying to pull the "Open" figure from the Daily Table into a Weekly Table, based on the criteria that as the week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will automatically pull the Open figure (i.e. 515) into the relevant cell. Daily Info Weekly Info WeekNum Date Open WeekNum Date Open 35 29/08/08 558 35 26/08/08 515 35 28/08/08 526.5 34 18/08/08 610 35 27/08/08 524.5 33 11/08/08 603 35 26/08/08 515 32 04/08/08 538.5 34 22/08/08 522 34 21/08/08 540 34 20/08/08 560 34 19/08/08 576 34 18/08/08 610 33 15/08/08 608 33 14/08/08 581 33 13/08/08 602 33 12/08/08 630 33 11/08/08 603 32 08/08/08 598.5 32 07/08/08 607 32 06/08/08 624.5 32 05/08/08 540.5 32 04/08/08 538.5 This really as me stumped!! Any help would be appreciated. Kind regards, Gavin. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Daily Info to Weekly
For the year 2008:
=LOOKUP(2,1/(($A$1:$A$19=$D1)*(YEAR($B$1:$B$19)=2008)),B$1:B$1 9) -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... Biff, I have to say that I am very impressed. I am still not sure how the formula works, but work it does!! I have one further "wrinkle" that I would like to add, if I may: The data I am looking at is over a number of years and, therefore, there are instances wher the WeekNum will be the same for different years. How do I get the formula to pull up the data relevant to the particular year only? Many, many thanks, Gavin. "T. Valko" wrote: My other suggestion worked but adding a helper column with the week num makes it easy! If D1 = weeknum = 35 Enter this formula in E1 and copy across to F1: =LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19) Select both E1 and F1 and copy down as needed. Format E1:En as Date -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... I am trying to "pull" certain information from a Daily Table into a Weekly Table. For example, in the two tables below, I am trying to pull the "Open" figure from the Daily Table into a Weekly Table, based on the criteria that as the week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will automatically pull the Open figure (i.e. 515) into the relevant cell. Daily Info Weekly Info WeekNum Date Open WeekNum Date Open 35 29/08/08 558 35 26/08/08 515 35 28/08/08 526.5 34 18/08/08 610 35 27/08/08 524.5 33 11/08/08 603 35 26/08/08 515 32 04/08/08 538.5 34 22/08/08 522 34 21/08/08 540 34 20/08/08 560 34 19/08/08 576 34 18/08/08 610 33 15/08/08 608 33 14/08/08 581 33 13/08/08 602 33 12/08/08 630 33 11/08/08 603 32 08/08/08 598.5 32 07/08/08 607 32 06/08/08 624.5 32 05/08/08 540.5 32 04/08/08 538.5 This really as me stumped!! Any help would be appreciated. Kind regards, Gavin. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Daily Info to Weekly
Biff,
Apologies for the delay in thanking you. If I were to try and get the "Close" rather than the "Open" in a similar set of data (i.e. it would be the final day in the particular WeekNum, as opposed to the first), how might I do that? Kind regards, Gavin. "T. Valko" wrote: For the year 2008: =LOOKUP(2,1/(($A$1:$A$19=$D1)*(YEAR($B$1:$B$19)=2008)),B$1:B$1 9) -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... Biff, I have to say that I am very impressed. I am still not sure how the formula works, but work it does!! I have one further "wrinkle" that I would like to add, if I may: The data I am looking at is over a number of years and, therefore, there are instances wher the WeekNum will be the same for different years. How do I get the formula to pull up the data relevant to the particular year only? Many, many thanks, Gavin. "T. Valko" wrote: My other suggestion worked but adding a helper column with the week num makes it easy! If D1 = weeknum = 35 Enter this formula in E1 and copy across to F1: =LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19) Select both E1 and F1 and copy down as needed. Format E1:En as Date -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... I am trying to "pull" certain information from a Daily Table into a Weekly Table. For example, in the two tables below, I am trying to pull the "Open" figure from the Daily Table into a Weekly Table, based on the criteria that as the week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will automatically pull the Open figure (i.e. 515) into the relevant cell. Daily Info Weekly Info WeekNum Date Open WeekNum Date Open 35 29/08/08 558 35 26/08/08 515 35 28/08/08 526.5 34 18/08/08 610 35 27/08/08 524.5 33 11/08/08 603 35 26/08/08 515 32 04/08/08 538.5 34 22/08/08 522 34 21/08/08 540 34 20/08/08 560 34 19/08/08 576 34 18/08/08 610 33 15/08/08 608 33 14/08/08 581 33 13/08/08 602 33 12/08/08 630 33 11/08/08 603 32 08/08/08 598.5 32 07/08/08 607 32 06/08/08 624.5 32 05/08/08 540.5 32 04/08/08 538.5 This really as me stumped!! Any help would be appreciated. Kind regards, Gavin. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Daily Info to Weekly
Based on the same table...
Try this array formula** entered in E2: =INDEX(B$2:B$20,MATCH(1,($A$2:$A$20=$D2)*(YEAR($B$ 2:$B$20)=2008),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Copy across to F2 then down as needed. Format E2:En as Date -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... Biff, Apologies for the delay in thanking you. If I were to try and get the "Close" rather than the "Open" in a similar set of data (i.e. it would be the final day in the particular WeekNum, as opposed to the first), how might I do that? Kind regards, Gavin. "T. Valko" wrote: For the year 2008: =LOOKUP(2,1/(($A$1:$A$19=$D1)*(YEAR($B$1:$B$19)=2008)),B$1:B$1 9) -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... Biff, I have to say that I am very impressed. I am still not sure how the formula works, but work it does!! I have one further "wrinkle" that I would like to add, if I may: The data I am looking at is over a number of years and, therefore, there are instances wher the WeekNum will be the same for different years. How do I get the formula to pull up the data relevant to the particular year only? Many, many thanks, Gavin. "T. Valko" wrote: My other suggestion worked but adding a helper column with the week num makes it easy! If D1 = weeknum = 35 Enter this formula in E1 and copy across to F1: =LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19) Select both E1 and F1 and copy down as needed. Format E1:En as Date -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... I am trying to "pull" certain information from a Daily Table into a Weekly Table. For example, in the two tables below, I am trying to pull the "Open" figure from the Daily Table into a Weekly Table, based on the criteria that as the week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will automatically pull the Open figure (i.e. 515) into the relevant cell. Daily Info Weekly Info WeekNum Date Open WeekNum Date Open 35 29/08/08 558 35 26/08/08 515 35 28/08/08 526.5 34 18/08/08 610 35 27/08/08 524.5 33 11/08/08 603 35 26/08/08 515 32 04/08/08 538.5 34 22/08/08 522 34 21/08/08 540 34 20/08/08 560 34 19/08/08 576 34 18/08/08 610 33 15/08/08 608 33 14/08/08 581 33 13/08/08 602 33 12/08/08 630 33 11/08/08 603 32 08/08/08 598.5 32 07/08/08 607 32 06/08/08 624.5 32 05/08/08 540.5 32 04/08/08 538.5 This really as me stumped!! Any help would be appreciated. Kind regards, Gavin. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting daily data into weekly, monthly and yearly data | Excel Discussion (Misc queries) | |||
Macros for Daily, Weekly and Monthly Reports. | Excel Discussion (Misc queries) | |||
Weekly bar charts assumes i mean daily bar chart | Charts and Charting in Excel | |||
formatting daily,weekly and monthly schedules | New Users to Excel | |||
formatting daily,weekly and monthly schedules | New Users to Excel |