Split yearly figures to months using a distribution curve

luisen

New Member
Joined
Oct 20, 2017
Messages
2
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:

PeriodStep (1/12)yYear%Monthly sales
11,08679.29524%79.987
21,17842.21525%99.171
31,25993.89126%117.031
41,331.135.77327%133.738
51,421.269.05127%149.432
61,501.394.70928%164.228
71,581.513.57229%178.224
81,671.626.336210%191.502
91,751.733.597210%204.132
101,831.835.867211%216.174
111,921.933.591211%227.681
122,002.027.155212%238.699
132,082.116.89837%208.372
142,172.203.12237%216.859
152,252.286.09138%225.026
162,332.366.04238%232.896
172,422.443.18738%240.489
182,502.517.71738%247.825
192,582.589.80338%254.921
202,672.659.60039%261.791
212,752.727.24939%268.450
222,832.792.87839%274.910
232,922.856.60539%281.183
243,002.918.536310%287.279
253,082.978.77048%265.192
263,173.037.39848%270.412
273,253.094.50348%275.496
283,333.150.16248%280.451
293,423.204.44748%285.284
303,503.257.42448%290.000
313,583.309.15348%294.605
323,673.359.69449%299.105
333,753.409.09949%303.503
343,833.457.41849%307.805
353,923.504.69749%312.014
364,003.550.98149%316.135

<tbody>
</tbody>


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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,180
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,148,337
Messages
5,746,170
Members
423,998
Latest member
eakenila

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
Top