Split yearly figures to months using a distribution curve

L

luisen

New Member
Joined
Oct 20, 2017
Messages
3
Hi all,

I have been learning from this forum for years, and now it is time for my first post. Thanks in advance for your help and for all these years of teaching :)

I need to convert yearly figures to months, following a given trend, and then automate the workbook so it can be done as automatically as possible. The trend is given by the yearly figures, this is an example:

Y1: 500,000
Y2: 2,000,000
Y3: 3,000,000
Y4: 3,500,000

My attempts:
A) Using excel pre-defined trend functions, normally logarithmic had the best fitting
B) Using spline fittings, with a VBA code that tries to replicate what excel does with the "smoothing" of data lines in graphs

With these formulas, I am able to convert from discrete to continuous and interpolate to 1/12 data points

Once I know the functions, I can get the twelve points between years, and the next step is to convert these into months

However, these points represent the accumulated figures, and not the values of the months

How can I convert these to months?

I have tried to add all them up and get the % of their year that they represent, however the monthly trend then shows "steps" that would not happen in real life:

fetch


This is the data using the logarithmic distribution:

[TABLE="width: 458"]
<tbody>[TR]
[TD="align: center"]Period[/TD]
[TD="align: center"]Step (1/12)[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]%[/TD]
[TD="align: center"]Monthly sales[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1,08[/TD]
[TD="align: center"]679.295[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4%[/TD]
[TD="align: center"]79.987[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1,17[/TD]
[TD="align: center"]842.215[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]99.171[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1,25[/TD]
[TD="align: center"]993.891[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6%[/TD]
[TD="align: center"]117.031[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1,33[/TD]
[TD="align: center"]1.135.773[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7%[/TD]
[TD="align: center"]133.738[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1,42[/TD]
[TD="align: center"]1.269.051[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7%[/TD]
[TD="align: center"]149.432[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1,50[/TD]
[TD="align: center"]1.394.709[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]164.228[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]1,58[/TD]
[TD="align: center"]1.513.572[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]178.224[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]1,67[/TD]
[TD="align: center"]1.626.336[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]191.502[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]1,75[/TD]
[TD="align: center"]1.733.597[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]204.132[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]1,83[/TD]
[TD="align: center"]1.835.867[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]11%[/TD]
[TD="align: center"]216.174[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]1,92[/TD]
[TD="align: center"]1.933.591[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]11%[/TD]
[TD="align: center"]227.681[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]2,00[/TD]
[TD="align: center"]2.027.155[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]12%[/TD]
[TD="align: center"]238.699[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]2,08[/TD]
[TD="align: center"]2.116.898[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7%[/TD]
[TD="align: center"]208.372[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]2,17[/TD]
[TD="align: center"]2.203.122[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7%[/TD]
[TD="align: center"]216.859[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]2,25[/TD]
[TD="align: center"]2.286.091[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]225.026[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]2,33[/TD]
[TD="align: center"]2.366.042[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]232.896[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]2,42[/TD]
[TD="align: center"]2.443.187[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]240.489[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]2,50[/TD]
[TD="align: center"]2.517.717[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]247.825[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]2,58[/TD]
[TD="align: center"]2.589.803[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]254.921[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]2,67[/TD]
[TD="align: center"]2.659.600[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]261.791[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]2,75[/TD]
[TD="align: center"]2.727.249[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]268.450[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"]2,83[/TD]
[TD="align: center"]2.792.878[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]274.910[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"]2,92[/TD]
[TD="align: center"]2.856.605[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]281.183[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"]3,00[/TD]
[TD="align: center"]2.918.536[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]287.279[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"]3,08[/TD]
[TD="align: center"]2.978.770[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]265.192[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"]3,17[/TD]
[TD="align: center"]3.037.398[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]270.412[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"]3,25[/TD]
[TD="align: center"]3.094.503[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]275.496[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]3,33[/TD]
[TD="align: center"]3.150.162[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]280.451[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]3,42[/TD]
[TD="align: center"]3.204.447[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]285.284[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]3,50[/TD]
[TD="align: center"]3.257.424[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]290.000[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]3,58[/TD]
[TD="align: center"]3.309.153[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]294.605[/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: center"]3,67[/TD]
[TD="align: center"]3.359.694[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]299.105[/TD]
[/TR]
[TR]
[TD="align: center"]33[/TD]
[TD="align: center"]3,75[/TD]
[TD="align: center"]3.409.099[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]303.503[/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD="align: center"]3,83[/TD]
[TD="align: center"]3.457.418[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]307.805[/TD]
[/TR]
[TR]
[TD="align: center"]35[/TD]
[TD="align: center"]3,92[/TD]
[TD="align: center"]3.504.697[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]312.014[/TD]
[/TR]
[TR]
[TD="align: center"]36[/TD]
[TD="align: center"]4,00[/TD]
[TD="align: center"]3.550.981[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]316.135[/TD]
[/TR]
</tbody>[/TABLE]


Do you have any suggestion about how to convert these sales figures into months so when we plot the months they do not show these steps?

I know it could be done with solver, changing the coefficients of the curve function so that the point in December is close to that of January of the following year, but that changes the value of the months and I never manage to have all Dec-Jan fitting properly between years.

Many thanks for your help, this is driving me crazy!!

NOTE: if you have a completely different approach that you think that can work, please share it, I will adapt any working solution

I posted this question in OzGrid as well, but it is a bit urgent and I haven't got any answers yet:

Best

Luis
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Welcome to the forum! Congratulations for figuring out how to post a pic and add the cross-post link in both forums.

I don't see a file attachment at ozgrid. In this forum, you can attach a link to a simple obfuscated file from free shared sites. e.g. dropbox.com

Not sure about your data. A Cdbl() or CDate() might be needed?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Chart help - rainfall data
Replies
2
Views
313
JV0710
J
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
210
Tetra201
Tetra201
D
  • Question Question
Amalgamating two formulas to achieve the goal
Replies
7
Views
116
davids4500
D
D
  • Solved
List of items within a specific month
Replies
4
Views
236
Fluff
Fluff
R
  • Question Question
Looking for help w Excel formula
Replies
2
Views
129
Dave Patton
D

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top