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,

Try this in C37:

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

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.

Cell C31 formula , Drag down

Formula:

Please Login or Register  to view this content.

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))

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.

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.

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?

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".

That's great, thanks josephteh

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