# Creating dynamic forecast values, each month when new data is added

1. ## Creating dynamic forecast values, each month when new data is added

Hello,

I'm wondering if anyone can help. I have a simple data file (which is an example of my actual data I work with), whereby I have dates, actual sales, and forecast sales.

I have historic, actual, monthly sales data between Jan 2022- May 2024 (column B), and I have calculated in column C of the attached workbook, the forecast values for June 2024 - Dec 2024.

However, the formulas in column C reference the fixed range of the actual data I have (e.g. the forecast figure for December still only looks at Jan 2022- May 2024), however, the actual data will update each month.

What I would like to be able to do is each month, reference the latest actual data I have in my forecasts, without having to manually update the formulas (i.e. drag the ranges down one cell for the date and sales ranges in column A + B) - but I can't figure out the best way to go about this.

Does anyone have any advice or pointers on this? I feel like I may be overlooking something very obvious with this.

Thanks,

2. ## Re: Creating dynamic forecast values, each month when new data is added

Try this in C37:

=IF(B37>0,"",FORECAST.LINEAR(\$A37,IF(\$B\$2:B32>0,\$B\$2:B32),\$A\$2:A32))

3. ## Re: Creating dynamic forecast values, each month when new data is added

Hi Zbor,

thank you for your reply! Sorry, I should have said, I would be looking to replace the formulas that start in cell C31, with a formula that I can drag down (in this case up to cell C37, but the spreadsheet would continue to grow with time).

The values that I have in C31:C37, are all using the current known values (i.e. actual sales up to May 2024) - which is fine for the forecast value of June 2024.

However, next month when I get to cell C32 (i.e. July 2024), by next month I will have had the actual sales value for June added to the sheet, and so the forecast value for July, will then be able to include the actual June sales value -- but the issue I have is that currently, I would have to manually update the formula in C32 to include the June value, and this would be a process I'd have to remember to do each month.

So I guess in effect, I'm looking to replace the formulas in column C that will forecast future sales, only using actual sales values - and not including where there are any 0's or blanks in column B.

4. ## Re: Creating dynamic forecast values, each month when new data is added

Cell C31 formula , Drag down
Formula:
`Please Login or Register  to view this content.`

5. ## Re: Creating dynamic forecast values, each month when new data is added

Add an IF statement in front of your formula in C31 and copy down:
=IF(B31>0,"",FORECAST.LINEAR(\$A31,\$B\$2:B30,\$A\$2:A30))

6. ## Re: Creating dynamic forecast values, each month when new data is added

wk9128 - thanks for your reply. However, I can see that you formula locks the range with the current actual sales, which is the same I think as what I had, and means when the formula is dragged down, it wouldn't then reference actual sales for future months when they are added?

josephteh - thanks for your reply as well. I tried this, but I think it is still referencing / including the sales which are 0 / blank, whereas, I'm trying to only look at the values where there is an actual sales value. I tried to think if there was a way to edit your formula to only look at the values in column B where a value is not ) or blank, but wasn't sure how to adjust this.

7. ## Re: Creating dynamic forecast values, each month when new data is added

Try this in C2, =IF(B2>0,"",FORECAST.LINEAR(\$A2,B\$1:INDEX(B:B,COUNTIF(\$B\$1:\$B1,">0")+1),A\$1:INDEX(A:A,COUNTIF(\$B\$1:\$B1,">0")+1))), copy down.

8. ## Re: Creating dynamic forecast values, each month when new data is added

josephteh - this worked !!!! This is exactly what I needed, thank you so much !!

I'm trying to figure out how exactly it works. If you wouldn't mind, would you mind explaining what the INDEX part is doing, although with the "+1" element?

9. ## Re: Creating dynamic forecast values, each month when new data is added

This part of formula "=B\$1:INDEX(B:B,COUNTIF(\$B\$1:\$B30,">0")+1)" in cell C31 will evaluate (using Formulas > Evaluate Formula) to "=\$B1:\$B30".

10. ## Re: Creating dynamic forecast values, each month when new data is added

That's great, thanks josephteh

11. ## Re: Creating dynamic forecast values, each month when new data is added

You are welcome, thanks for the Rep and for marking the thread "Solved"!

There are currently 1 users browsing this thread. (0 members and 1 guests)