Spliting Monthly quantity to daily quantity

Hi all,

I now have a worksheet for each sales team which listed their target by products and by month in a horizontal layout

Product Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15
A 146 360 92 260 45 481 308 51 106 453 417 175
B 254 277 225 193 284 396 20 40 491 242 341 250
C 9 131 470 371 388 218 119 274 108 344 64 407
D 183 30 169 211 190 79 364 197 351 398 161 205
E 95 366 259 433 67 384 40 74 414 34 98 326
F 446 337 418 459 423 125 126 191 398 375 404 191
G 245 463 206 158 465 148 286 7 48 415 122 183
H 12 382 94 137 271 334 282 20 7 75 400 329

As we need to compare it with daily sales, so I want to have it convert to daily quantity (working days only) and put in a database format for easy comparison. What I did was to do the calculation and insert 365 columns for each team. I am hoping that someone could help to develop something more efficient.

Thank you
Cecilia

Hello

I'm trying to understand you problem.

You have monthly sales figures (below)

sales_monthly.png

And in order to compare them with daily sales you want these to be converted to daily sales or in a format that you can compare to daily sales?
As you only have end of month figures to compare with daily figures, is the below 'layout' the kind of thing you're looking for? Considering working days only. The blanks would be filled in with your daily figures?

If not, you will need to provide an example of what you're trying to achieve


Montly_daily_sales.png

Thank you so much for your reply!!

What im trying to achieve here is
Product Day Quantity
A 01/01/2015
A 02/01/2015
A 03/01/2015
A 04/01/2015
A 05/01/2015
A 06/01/2015
A 07/01/2015
A 08/01/2015
A 09/01/2015
A 10/01/2015
A 11/01/2015
A 12/01/2015
A 13/01/2015
A 14/01/2015
A 15/01/2015
A 16/01/2015
A 17/01/2015
A 18/01/2015
A 19/01/2015
A 20/01/2015
A 21/01/2015
A 22/01/2015
A 23/01/2015
A 24/01/2015
A 25/01/2015
Can you please show me how to insert table in this forum? It could be easier
Thanks
Cecilia

I'm still trying to understand your problem. Can you answer the below questions for me.

• Are you trying to display monthly data as daily data?
• Is the below what you want to achieve? If I take the Monthly figure and place it into the daily figure slots?

Here is the month of January Month_jan.png

Here is the month of January placed in each daily slot.
Daily_from_montly.png

I want to have them posted vertically
Product Date Quantity
A 01/01/2016
A 02/01/2016
A 03/01/2016
A 04/01/2016 7.3
A 05/01/2016 7.3
A 06/01/2016 7.3
A 07/01/2016 7.3
A 08/01/2016 7.3
A 09/01/2016
A 10/01/2016
A 11/01/2016 7.3
A 12/01/2016 7.3
A 13/01/2016 7.3
A 14/01/2016 7.3
A 15/01/2016 7.3
A 16/01/2016
A 17/01/2016
A 18/01/2016 7.3
A 19/01/2016 7.3
A 20/01/2016 7.3
A 21/01/2016 7.3
A 22/01/2016 7.3
A 23/01/2016
A 24/01/2016
A 25/01/2016 7.3
A 26/01/2016 7.3
A 27/01/2016 7.3
A 28/01/2016 7.3
A 29/01/2016 7.3
A 30/01/2016
A 31/01/2016
B 01/01/2016
B 02/01/2016
B 03/01/2016
B 04/01/2016 12.7
B 05/01/2016 12.7
B 06/01/2016 12.7
B 07/01/2016 12.7
B 08/01/2016 12.7
B 09/01/2016
B 10/01/2016
B 11/01/2016 12.7
B 12/01/2016 12.7
B 13/01/2016 12.7
B 14/01/2016 12.7
B 15/01/2016 12.7
B 16/01/2016
B 17/01/2016
B 18/01/2016 12.7
B 19/01/2016 12.7
B 20/01/2016 12.7
B 21/01/2016 12.7
B 22/01/2016 12.7
B 23/01/2016
B 24/01/2016
B 25/01/2016 12.7
B 26/01/2016 12.7
B 27/01/2016 12.7
B 28/01/2016 12.7
B 29/01/2016 12.7
B 30/01/2016
B 31/01/2016

This is the method I have always used to upload files... (This was provided to myself from FDibbins)

click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Thanks Tonnie!!!

I got a little lost in this one, but it works. You will need to transpose the data via "Paste Special" as it begins with the data in the "sales" worksheet. I wasn't sure what your data and workbook looks like so I've left this part up to you.


See attached.

sales.xlsm

Please Login or Register  to view this content.

Thank you so much Danny,

This is very similar to what I need, but I need it with some calculation as well.

Can you please see my attachment?
1) I hope to divide the monthly figures to Daily figures depends on working days (Is it possible to have it to Mon to Sat and exclude public holiday)
2) I have multiple teams which listed on different pages. Any chance can consolidate into one page? I can do this manually.

Thank you so much for your kind help. This would save my life. Last time when I do the conversion I spent all day doing copy and paste.

Thanks a lot!!!

Cecilia

1) I hope to divide the monthly figures to Daily figures depends on working days (Is it possible to have it to Mon to Sat and exclude public holiday)

Monthly figures are spread out across all working days in the spreadsheet I sent you. When you say you want monthly figures divided to daily figures are you saying that you want the monthly figure say 100 divided by the working days in that month 100 divided by 22 = 4.545 for example? Can you provide an example of the type of calculation you are trying to achieve?

Also... Which country and state are you in? Public holidays will vary so I'll need to know Country and state.
I'm not sure what I can do with public holidays but I'm going to give it a go anyway.

Going from Monday to Saturday won't be a problem. So you just want to cut out Sundays and public holidays?

Originally Posted by danny2000

Monthly figures are spread out across all working days in the spreadsheet I sent you. When you say you want monthly figures divided to daily figures are you saying that you want the monthly figure say 100 divided by the working days in that month 100 divided by 22 = 4.545 for example? Thats correct!!

Can you provide an example of the type of calculation you are trying to achieve? In my attachment above I have put down the layout and formula as an example for what i want to achieve.

Also... Which country and state are you in? Public holidays will vary so I'll need to know Country and state.
I'm not sure what I can do with public holidays but I'm going to give it a go anyway.

Going from Monday to Saturday won't be a problem. So you just want to cut out Sundays and public holidays?Im in Hong Kong, is it possible for me to type in the days of Public holiday so it could be changed in the future. If its too much trouble I can leave it as it is. I believe my boss would be happy enough to have it split by day in a click ^^

Thank you!!!

Thank you very much

Hi all experts,

Can anyone help with this problem?

Thank you so much
Cecilia

Cecilia

There is still a problem with the information you have provided.
In your first example you showed me the below.

Please Login or Register  to view this content.

Above, you have provided the year though in the spreadsheet you sent me you have not. The year is
important if you want to use this in the future. Otherwise how will public holidays be determined?

So.. for example, in your spreadsheet, you can see that you have only given me months. Will these dates
actually reflect which year you are referring to? See the image below.

Please Login or Register  to view this content.

Will you be constructing your spreadsheet to do this?

Please Login or Register  to view this content.

Hi Danny,

Thank you so much for your help.

With my excel workbook example, I forgot to put the year in the header. Yours suggestion is exactly what its looks like with my working file.

Thank you so much!!
Cecilia

Thank you so much Danny!!!

This is amazing I need to try this out to my real data.

I really cant thank you enough. You have saved thousands of hours of my time.

Thank you!!
Cecilia