Days to Weeks

[losincog]

Hello

Have a cash flow (of sorts) in days, lets call it "days" (called Ccy Daily F'Cast in the snip below). I now want to do a weekly one on a new tab, linking in data from the "days".

"days"


days.JPG

"weeks"

weeks.JPG

What would be the best way of pulling in the data from "days" into it's corresponding week range in the "weeks" tab? So for the week beginning the 9th December, pulling 9th-15 December from the "days" tab

I did the manual way as per C3 and had a google at trying to move C3 formula by 7 (for 7 days) via OFFSET and the like but clearly beyond my capabilities!

Thanks

[harrisonland]

Hi there - welcome to the forum. Please can you attach a sample workbook so we can take a look? It's much easier to do that, and means we can re-post your sample with a proposed solution, too.

If you look at the yellow banner at the top of the page, it explains how!

Tim

[losincog]

Hi there - welcome to the forum. Please can you attach a sample workbook so we can take a look? It's much easier to do that, and means we can re-post your sample with a proposed solution, too.

If you look at the yellow banner at the top of the page, it explains how!

Tim

Thanks, that's definitely a better idea!

Adjusted it slightly. A multi-currency cash flow workbook. I want to create a weekly view ("weeks") from the data in "days". I guess next step would be a monthly view but baby steps

[harrisonland]

Right... just had a look. Simply put this in "Weeks!D3" and copy across and down.

Please Login or Register  to view this content.

This adjusts the offset from the cells on the "Days" worksheet by counting the number of columns between the first week and subsequent weeks and multiplying that by 5 (working days).

This will obviously only work if there are always 5 days listed per week.

Monthly figures could be a bit trickier because not all months have the same number of days. You can use this

Please Login or Register  to view this content.

to determine how many days in the month referenced in C2; if you had columns for Saturdays and Sundays (with "0" figures in there) you could then use the number of days for that month to do your offset thingie, but that's going to get complicated.

One other observation... as you add data your sheet is going to get wider and wider with more and more columns. Intuitively, one would expect it to get longer with data added at the bottom; that way it might be easier to keep an overview. Just sayin!

HTH
Tim

[protonLeah]

Insert row new row 3 on each sheet(could be hidden) and use:

Please Login or Register  to view this content.

copied across.

On Weeks sheet, calculate sums with array* formula:

Please Login or Register  to view this content.

*...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[losincog]

Thanks both! Two different ways of doing it but does the job

Regarding a "months" tab, as noted by Harrisonland it will be tricky to make it pull in data from the "weeks" tab. How about if I had a new row (in each tab?) with a CHOOSE formula like this:
=CHOOSE(MONTH(C2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") and doing a sum(if) similar to pull in the value for the range?

[harrisonland]

TBH, I think ProtonLeah's idea is the way forward with this. For a monthly summary, you'd just add another (hidden if you wish) "helper" row on the "days" worksheet and a "months" ws (analagous to the week one).

Please Login or Register  to view this content.

would index the month for each day in the list and you could construct another array formula based on the one in post #5 to do the calculations.

That would keep it much tidier and easier to fathom what's going on later when you've forgotten how you did it.

Tim

[Phuocam]

Maybe like this:

=SUMIFS(days!$C3:$V3,days!$C$2:$V$2,">="&C$2,days!$C$2:$V$2,"<="&C$2+6)