Complex calculated field in pivot table (forecast)

**jeremystern** · 01-18-2019, 11:47 AM

Hi,

I have a pivot table with the following info:
Month 2016 2017 2018
Jan 40 17 93
Feb 45 35 63
etc.

I want to make a calculated field to add a forecast column. The formula would be the standard forecast.ets formula and needs 2019 as the target date, the values are the row data (40, 17, 93 in this sample), the timeline would be the years (2016, 2017, 2018). I don't know how to do this with a calculated field or even if it can be done. I tried doing it outside the pivot table but got a #div/0! error because of the dates (I think). The added column would look like:

2019 ETS
107 and so on for each row

Any help would be greatly appreciated

Thanks so much!

Jeremy

**JeteMc** · 01-27-2019, 11:53 PM

Hello jeremystern and Welcome to Excel Forum.
This proposal employs a formula that is outside of the pivot table as I don't believe that you can use a calculated field to forecast values.
Note that I don't know the function or mathematical process used to come up with a value of 107 in post #1.
In the attached the formula used to forecast monthly values for 2019 is: =FORECAST.LINEAR(4,G4:I4,{1,2,3})
A variation of the above formula will allow forecasting over multiple years: =FORECAST.LINEAR(COLUMN(D:D),$G12:I12,COLUMN($A:C))
For future reference you are more likely to get faster and more accurate answers if you provide a sample of the raw data, pivot table and anticipated results, along with explanation of the same, in a spreadsheet. To upload a spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.