A lot of the time, there are differences between different pieces of data received from a client/ data team – one being when different pieces roll up to different levels in a nested structure, for eg. weekly vs monthly vs yearly. Is that problem? Let’s take a look..
Taking the example here – let’s assume we have Monthly data for spends for campaigns while we’re working at a weekly level with Sales. Thus, we’re forced to split Monthly figures into the Weekly format to be used for any purposes – from reporting to modeling. It is often required to accurately split the monthly into weekly, so as not to double count or misrepresent the spends in the weekly format. One of the biggest problems – overlapping days between the week and months.
For eg. from the image below – the week of 27th Jan 2014 contains days from Jan ’14 as well as Feb ’14. In order to accurately get the spends for this week, we need to factor Jan & Feb spends weighted by number of days.
Here’s a neat trick that solves the problem with one formula – that’s right, just ONE formula!
And you get this –
Credits to this guy – “AlphaFrog”. His post (http://www.mrexcel.com/forum/excel-questions/627101-converting-data-monthly-weekly.html) provided the basis for this post. Below is a quick snapshot from his page showing the flow of logic.