YTD Dynamic Trend formula

[salc325]

Hi All,

could someone please help me figure out how to make a dynamic trend formula in a following way:

I calculated YTD trend as but would like the Trend formula to automatically take into account the following month once I update the source table with September Actuals so that the trend for all future periods is calculated based on the all available actuals​

(Cell V2 will automatically be "Actual" and I guess Trend formula should reference to it?)​

​

Here is the current Trend formula in line 6:​

=+TREND($B$4:$U$4,$B$1:$U$1,B1)​

​

Would Offset instead of $U$4 do the trick?​

​

​

Sorry I can't upload the sheet due to computer installation restrictions but will appreciate any help!​

Thanks!​

 

[stroffso]

Could you not just do an if statement saying if(V2="Actual",TREND($B$4:$U$4,$B$1:$U$1,B1),"") ?