Change monthly formulas to get weekly data, heeelp

[Jarke]

Hello!

I have made alot of formulas that gives me some valuable data in months from daily transactions, and i also want these formulsas to summarize in weekly data. Some exemples on some of my formulas is;

Result on month =SUMPRODUCT((MONTH(Logg!$C$24:$BN$24)=C75)*(YEAR(Logg!$C$24:$BN$24)=2016)*(Logg!$C$60:$BN$60))

Ave gain on capital =SUMPRODUCT((MONTH(Logg!$C$24:$BN$24)=C75)*(YEAR(Logg!$C$24:$BN$24)=2016)*(Logg!$C$61:$BN$61>0)*Logg!$C$61:$BN$61)/SUMPRODUCT((MONTH(Logg!$C$24:$BN$24)=C75)*(YEAR(Logg!$C$24:$BN$24)=2016)*(Logg!$C$61:$BN$61>0))


So, how will i retype the formulas to only take data from weeks? Can i just change the MONTH in each to something? And then also change =C75 to instead a cell that says for example 32 (for week 32)?

I hope you have som great solution to this, looking forward to it!

Thanks and best regards

 

[Jarke]

More info;


[TABLE="width: 618"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]2016 [/TD]
[TD]x[/TD]
[TD]y [/TD]
[TD]z[/TD]
[TD]u [/TD]
[/TR]
[TR]
[TD]Augusti[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Oktober[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1703"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Here is how my monthly tabel is layed up, and each cell is filled with a formula, i also want to create a weekly tabel. And as i have the formulas, can i rewrite these as mentioned above? On another sheet i have my information, transactions, date, result, capital after result etc.. The monthly columms work. And for example August i want my dates to categorieze into weeks 31,32,33,34,35 depending on when the transaction happened, automatically. [/TD]
[/TR]
</tbody>[/TABLE]

Like if i had transactions at, 2/8, 2/8, 4/8, 5/8, 5/8 i want all these to go into week 31, just as they now do into months.

How do i do this?

Any help is most welcomed!

First step would be, how to sum dates in weeks! The formula criteria i could probably add later.

If you need more info, tell me!

Thanks

 

[AhoyNC]

See if this example helps.
You would need to add a row for week number.
There are 2 average formulas below. If you have Excel 2010 or later you could use the AVERAGEIFS function.
The formula in E65 below can be used in any ver. However it is an array function and must be entered with

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
23		Week #	49	53	1	2	6	6	6	8	10	10	11	11
24		Date	12/3/2015	12/31/2015	1/1/2016	1/5/2016	2/2/2016	2/3/2016	2/5/2016	2/16/2016	3/2/2016	3/5/2016	3/6/2016	3/7/2016
25
60			1	2	3	4	5	-6	7	15	9	12	20	11
61
62
63		Week #	6
64		Sum>0	12
65		Average	6		6
CTRL-SHIFT-ENTER.

 

[Jarke]

Saving me again friend! Thanks alot! Guess i haft to live with an extra row with information about which week.. But it does the trick! Notes; when making the C23, i had to add -1 to get the right week, it showed one week +1... Weird..

Don't understand how you got average 6 tho.. Should be 2? "(5-6+7=6) / 3 = 2" or you mean average on 2016?

Still working to get C65 working, gotta recode some, but will probably eventually work. Got excel 2010 btw

Got alot of work to do now, changing monthly formulas to weekly, yikes :D

Thanks again, best regards

 

[AhoyNC]

I got the average of 6 since before you only wanted values greater than 0.
Change formula in C65 to

=AVERAGEIFS($C$60:$N$60,$C$24:$N$24,">="&DATE(2016,1,1),$C$24:$N$24,"<="&DATE(2016,12,31),$C$23:$N$23,$C$63)

to get 2.
The WEEKNUM() function will not take a range, so that is why I added the helper column of week numbers.

 

[Jarke]

I see! Thanks again, great work