# Split yearly figures to months using a distribution curve

#### luisen

##### New Member
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:

This is the data using the logarithmic distribution:

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

#### luisen

##### New Member
Hi Kenneth,

Thanks a lot for your help

I continued to search and search, and I found a paid solution:

It is provided by SRS1Software and it is not very expensive. I will try it and let you know

Best

Luis

Replies
2
Views
57
Replies
9
Views
80
Replies
0
Views
60
Replies
3
Views
105
Replies
1
Views
95

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.

### Which adblocker are you using?

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

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