Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks and days sorting out
Hi there I hope someone could help me
I've got a column of dates in column A and the sales for each date in column B What I need to do is that I have to classify these daily sales figures into weekly sales figures downloaded onto my "Sales!" sheet Then I've created a new sheet "Sheet1!"and put the to and from date in column A and B and the week number in column C For example Date From Date To: Week Number: 01/01/2006 07/01/2006 1 08/01/2006 15/01/2006 2 I would like to do a lookup in my downloaded daily sales figures and I've put a formula: If(Sales!A1<Sheet1!B2,if(Sales!A1Sheet!A1,vlookup (Sales A1,Sheet!A:C,3,0),"Checking") However it doesnt work, can somebody please help me and tell me what have I done wrong and how should I put it right please? Thanks a lot Vivi |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks and days sorting out
Hi Vivi,
You can calculate the week a date is in by using the following: =ROUNDUP(( [1] - [2] )/7,0) whe 1-Your date 2-The numerical value for the 01/01 of the year you are calculating (38718 for 2006) If you are aiming to report on weekly sales then this should do you just fine. HTH Thanks, Simon vivi wrote: Hi there I hope someone could help me I've got a column of dates in column A and the sales for each date in column B What I need to do is that I have to classify these daily sales figures into weekly sales figures downloaded onto my "Sales!" sheet Then I've created a new sheet "Sheet1!"and put the to and from date in column A and B and the week number in column C For example Date From Date To: Week Number: 01/01/2006 07/01/2006 1 08/01/2006 15/01/2006 2 I would like to do a lookup in my downloaded daily sales figures and I've put a formula: If(Sales!A1<Sheet1!B2,if(Sales!A1Sheet!A1,vlooku p(Sales A1,Sheet!A:C,3,0),"Checking") However it doesnt work, can somebody please help me and tell me what have I done wrong and how should I put it right please? Thanks a lot Vivi -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200610/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks and days sorting out
=SUMPRODUCT(--(Sales!A1:A1000=A2),--(Sales!A1:A1000<=B2),Sales!B1:B1000)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vivi" wrote in message ... Hi there I hope someone could help me I've got a column of dates in column A and the sales for each date in column B What I need to do is that I have to classify these daily sales figures into weekly sales figures downloaded onto my "Sales!" sheet Then I've created a new sheet "Sheet1!"and put the to and from date in column A and B and the week number in column C For example Date From Date To: Week Number: 01/01/2006 07/01/2006 1 08/01/2006 15/01/2006 2 I would like to do a lookup in my downloaded daily sales figures and I've put a formula: If(Sales!A1<Sheet1!B2,if(Sales!A1Sheet!A1,vlookup (Sales A1,Sheet!A:C,3,0),"Checking") However it doesnt work, can somebody please help me and tell me what have I done wrong and how should I put it right please? Thanks a lot Vivi |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks and days sorting out
Hi
thanks for your post and your suggestions but unfortunately I've tried it but it doesn't work for me, maybe I've haven't explained myself correctly The first sheet contained a daily sales figure from 01/01/06 to 30/09/06 E.G. 01/01/06 £500 02/01/06 £600 03/01/06 £400 04/01/06 £600 05/01/06 £700 06/01/06 £500 07/01/06 £400 What I want to do is that from my lookup table in the 2nd Sheet, I have this figures: To From Week No. 31/12/2005 06/01/2006 1 07/01/2006 13/01/2006 2 14/01/2006 20/01/2006 3 21/01/2006 27/01/2006 4 28/01/2006 03/02/2006 5 So on my first sheet I would like to have a column to show me which number of week does a day falls into e.g. Date: £: Week no. 01/01/06 £500 1 02/01/06 £600 1 03/01/06 £400 1 04/01/06 £600 1 05/01/06 £700 1 06/01/06 £500 1 07/01/06 £400 1 08/01/06 £500 2 I've used your formula but I could onlu get an answer to the first cell but then all the others are incorrectly showing zeros, is there other formulas that I can use? Thanks a lot Vivi "Bob Phillips" wrote: =SUMPRODUCT(--(Sales!A1:A1000=A2),--(Sales!A1:A1000<=B2),Sales!B1:B1000) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vivi" wrote in message ... Hi there I hope someone could help me I've got a column of dates in column A and the sales for each date in column B What I need to do is that I have to classify these daily sales figures into weekly sales figures downloaded onto my "Sales!" sheet Then I've created a new sheet "Sheet1!"and put the to and from date in column A and B and the week number in column C For example Date From Date To: Week Number: 01/01/2006 07/01/2006 1 08/01/2006 15/01/2006 2 I would like to do a lookup in my downloaded daily sales figures and I've put a formula: If(Sales!A1<Sheet1!B2,if(Sales!A1Sheet!A1,vlookup (Sales A1,Sheet!A:C,3,0),"Checking") However it doesnt work, can somebody please help me and tell me what have I done wrong and how should I put it right please? Thanks a lot Vivi |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks and days sorting out
=VLOOKUP(Sheet2!A1,Sheet3!$A$2:$C$30,3,TRUE)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vivi" wrote in message ... Hi thanks for your post and your suggestions but unfortunately I've tried it but it doesn't work for me, maybe I've haven't explained myself correctly The first sheet contained a daily sales figure from 01/01/06 to 30/09/06 E.G. 01/01/06 £500 02/01/06 £600 03/01/06 £400 04/01/06 £600 05/01/06 £700 06/01/06 £500 07/01/06 £400 What I want to do is that from my lookup table in the 2nd Sheet, I have this figures: To From Week No. 31/12/2005 06/01/2006 1 07/01/2006 13/01/2006 2 14/01/2006 20/01/2006 3 21/01/2006 27/01/2006 4 28/01/2006 03/02/2006 5 So on my first sheet I would like to have a column to show me which number of week does a day falls into e.g. Date: £: Week no. 01/01/06 £500 1 02/01/06 £600 1 03/01/06 £400 1 04/01/06 £600 1 05/01/06 £700 1 06/01/06 £500 1 07/01/06 £400 1 08/01/06 £500 2 I've used your formula but I could onlu get an answer to the first cell but then all the others are incorrectly showing zeros, is there other formulas that I can use? Thanks a lot Vivi "Bob Phillips" wrote: =SUMPRODUCT(--(Sales!A1:A1000=A2),--(Sales!A1:A1000<=B2),Sales!B1:B1000) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vivi" wrote in message ... Hi there I hope someone could help me I've got a column of dates in column A and the sales for each date in column B What I need to do is that I have to classify these daily sales figures into weekly sales figures downloaded onto my "Sales!" sheet Then I've created a new sheet "Sheet1!"and put the to and from date in column A and B and the week number in column C For example Date From Date To: Week Number: 01/01/2006 07/01/2006 1 08/01/2006 15/01/2006 2 I would like to do a lookup in my downloaded daily sales figures and I've put a formula: If(Sales!A1<Sheet1!B2,if(Sales!A1Sheet!A1,vlookup (Sales A1,Sheet!A:C,3,0),"Checking") However it doesnt work, can somebody please help me and tell me what have I done wrong and how should I put it right please? Thanks a lot Vivi |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks and days sorting out
Hi,
Ok...try this. On sheet one paste the following in a spare column (change A1 to the cell which containts the date): =ROUNDUP((A1-38717)/7,0) Then, on sheet 2 paste the same and change A1 to the START date of the week in your list. then in a spare column on sheet2 paste the following (change "Sheet1!B1:B10" to the cells which contain the calculated week numbers on the first sheet , "sheet2!b1" to the cell which contains week number on the second sheet, "Sheet1!C1:C10" to the dails sales figures on the first sheet"): =SUMIF(Sheet1!$B$1:$B$10,"="&Sheet2!B1,Sheet1!$C$1 :$C$10) A more efficient way, however, would be to calculate the week on sheet 1 the create a pivot table for the sum by week calculation. Thanks, Simon vivi wrote: Hi thanks for your post and your suggestions but unfortunately I've tried it but it doesn't work for me, maybe I've haven't explained myself correctly The first sheet contained a daily sales figure from 01/01/06 to 30/09/06 E.G. 01/01/06 £500 02/01/06 £600 03/01/06 £400 04/01/06 £600 05/01/06 £700 06/01/06 £500 07/01/06 £400 What I want to do is that from my lookup table in the 2nd Sheet, I have this figures: To From Week No. 31/12/2005 06/01/2006 1 07/01/2006 13/01/2006 2 14/01/2006 20/01/2006 3 21/01/2006 27/01/2006 4 28/01/2006 03/02/2006 5 So on my first sheet I would like to have a column to show me which number of week does a day falls into e.g. Date: £: Week no. 01/01/06 £500 1 02/01/06 £600 1 03/01/06 £400 1 04/01/06 £600 1 05/01/06 £700 1 06/01/06 £500 1 07/01/06 £400 1 08/01/06 £500 2 I've used your formula but I could onlu get an answer to the first cell but then all the others are incorrectly showing zeros, is there other formulas that I can use? Thanks a lot Vivi =SUMPRODUCT(--(Sales!A1:A1000=A2),--(Sales!A1:A1000<=B2),Sales!B1:B1000) [quoted text clipped - 27 lines] Vivi -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
minutes seconds days weeks years | Excel Discussion (Misc queries) | |||
Calculate Number of Months Weeks and Days Between Two Dates | Excel Worksheet Functions | |||
How to calculate in weeks and days? | Excel Discussion (Misc queries) | |||
A number of days into weeks and days | Excel Worksheet Functions | |||
How do I sort by date (not days, weeks, months) in Excel 2000? | Excel Discussion (Misc queries) |