Solved Excel - formula to add column to range

February 26, 2017 at 21:31:34
Specs: Windows 64
I have a forecast spreadsheet that currently needs manual input to add appropriate numbered monthly columns based on a project time frame. ie. 12 month project requires 12 monthly columns.

Ideally what I would like is to have project start and finish date cells which are linked to a formula or macro which will spread the forecast across the correct number of monthly columns.

Is this possible?

See More: Excel - formula to add column to range

Report •

February 27, 2017 at 00:34:57
✔ Best Answer
If I am understanding this correctly, what you are looking for is to spread your forecase spend evening between your forecast dates?

so if you forecasted £100 spend
between periods 01/01/2017 and 01/05/2017

which is 5 months

then your £100 over 5 would give you a monthly spend of £20

So this is pretty straight forward IF your are always going to spread using an EVEN curve, ie, your spread will always be the same for each period, rather than a front loaded or back loaded curve.

Something like this will work, however please note this has been crudely put together as an example ONLY, there is much room for improvement, I just wanted to prove a concept.

for this to work you need the following

           A              B          C             
1       Forecast     Start Date    End Date 
2       £100.00      01/01/2017  01/05/2017

Then run the code and it will create the spread across D E F G H

Dim NoMonths As Long

Sub SpreadForecaste()
    NoMonths = DateDiff("m", Range("B2"), Range("C2"))
    For i = 4 To (NoMonths * 2)
        Cells(2, i).Value = Range("A2") / (NoMonths + 1)
    Next i

End Sub

Report •
Related Solutions

Ask Question