Converting Daily Outputs into Weekly Outputs

[MichaelBitton]

I have a sheet of daily outputs for various people. I then compile the weekly sums and averages in a separate sheet.

Doing this for a single week is easy - you can sum C1:G1 to get John's number and then copy/paste it down to the other people.

But when I copy/paste the C11 formula to the next week, the default formula would be the sum of D1:H1 - it would offset 1 day instead of by the 5 days that I want. How can I automate this difference so that I'm not always manually changing the column letters?

A1	B1	C1	D1	E1	F1	G1	H1	I1	J1	K1	L1
A2		01-Dec​	02-Dec​	03-Dec​	04-Dec​	05-Dec​	08-Dec​	09-Dec​	10-Dec​	11-Dec​	12-Dec​
A3		Monday	Tueday	Wednesday	Thursday	Friday	Monday	Tueday	Wednesday	Thursday	Friday
A4	john	5​	6​	10​	20​	1​	4​	14​	6​	15​	12​
A5	dave	2​	7​	15​	20​	0​	9​	12​	35​	0​	23​
A6	michael	3​	0​	12​	0​	22​	31​	0​	17​	0​	21​
A7	alicia	8​	20​	0​	50​	29​	5​	0​	12​	82​	0​
A8		12​	45​	31​	19​	0​	66​	21​	14​	4​	5​
A9
A10		Dec 1 - 5	Dec 8 - 12	Dec 15 - 19
A11	john	42​
A12	dave	44​
A13	michael	37​
A14	alicia	107​

 

[alansidman]

you can do this with power query also called Get and Transform Data depending upon the version of Excel you are using.

Unpivot your data and then convert the date to week number. Once you have the week number you can group by the week and person. If you are willing to upload your sample data using XL2BB so that the formatting is correct, I will create the Mcode for you. If you are unfamiliar with XL2BB, look at my signature.