Calculate values by Calendar month

[Thorin]

I have weekly figures, but need to calculate them by calendar month.

Example :-

Week 1 = Week ending 3rd Nov 2007 Value 1940
Week 2 = Week ending 10th Nov 2007 Value 2221
Week 3 = Week ending 17th Nov 2007 Value 2123
Week 4 = Week ending 24th Nov 2007 Value 1905
Week 5 = Week ending 1st Dec 2007 Value 2134

I am trying to establish a formula which will calulate Novembers value (or indeed any month), this will take the weeks that span the month ends and calculate a day value.

My hope is that I type in the month in one cell and the formula to calculate the value will give me the months value only.

What would be the best formula to achive this ?

Many thanks

 

[Jon von der Heyden]

How exactly is the data laid out? I.e., is 3rd November 2007 housed in it's own cell? Lets say for purpose of this example that week ending date is housed in column B and amount is housed in column D.

=SUMPRODUCT(--(TEXT($B$2:$B$100,"mmm yy")="Nov 07"),$D$2:$D$100)

This will return the value associated with November.

Regards
Jon

 

[Thorin]

Jon,

Yes the dates are in their own cells, by 3rd Nov is a week ending date, and only 3 days for that week belong to November.

 

[Jon von der Heyden]

I don't know what the nature of the values are. How do you mean to apportion these values? Is calculating the number of days between weekending dates and using this as a means of apportionment appropriate?

 

[Jon von der Heyden]

As an example of what I mean by my previous post:

Book1
	A	B	C	D	E	F
1	w/e date	value	Daily proportion	Days Diff	New Period	New Balance
2	15/06/2007	138.00	19.71	-	Jun 07	138.00
3	22/06/2007	110.00	15.71	- 8.00	Jun 07	110.00
4	29/06/2007	131.00	18.71	- 1.00	Jun 07	145.86
5	06/07/2007	104.00	14.86	6.00	Jul 07	89.14
6	13/07/2007	120.00	17.14	- 18.00	Jul 07	120.00
7	20/07/2007	147.00	21.00	- 11.00	Jul 07	147.00
8	27/07/2007	117.00	16.71	- 4.00	Jul 07	174.14
9	03/08/2007	100.00	14.29	3.00	Aug 07	42.86
10	10/08/2007	141.00	20.14	- 21.00	Aug 07	141.00
11	17/08/2007	131.00	18.71	- 14.00	Aug 07	131.00
12	24/08/2007	113.00	16.14	- 7.00	Aug 07	113.00
13	31/08/2007	140.00	20.00	-	Aug 07	140.00
14
15	Jun 07	393.86
16	Jul 07	530.29
17	Aug 07	567.86
Sheet1

But again I'm unsure if this is an appropriate means to apportion the values since I do not know what these values represent.

Regards
Jon

 

[Thorin]

Strickly apportioned by the number of actual days in the week, always a week of seven days.

 

[Jon von der Heyden]

In which case the method I've demonstrated should do the trick. Can you make sense of it or do you want me to explain step by step what I have done?

Regards
Jon

 

[Thorin]

Jon,

Many thanks for this, it make perfect sense to me.

You asked what the values represent, they are tonnes of animal feed supplied to farm an a weekly basis, I was trying to work out how much tonnage is consumed by the chicken crop on farm.

 

[barry houdini]

If you have week ending dates in A2:A11 and corresponding tonnage figures in B2:B11 then if you put the first date of the month of interest, e.g. 1/11/2007 in D2 this formula will give the total for the month

=SUM(IF(A2:A11>=D2,IF(A2:A11< D2+38-DAY(D2+31),IF(DAY(A2:A11) >6,7,IF(A2:A11-D2>9,7-DAY(A2:A11),DAY(A2:A11)))))*B2:B11)/7

confirmed with CTRL+SHIFT+ENTER