Data layout is here:
This data will extend for a full year in the consumption data column. This is the value I want the model to predict in the future, using the initial 365 rows.
Bank Holiday is a binary variable, showing 1 if the day is a bank holiday (Christmas, New Years Day etc) and 0 if it is not.
Heating/Cooling columns are independent variables, the value of which will be based on Heating/Cooling degree day data, which will vary by external temperature.
Mon-Sun columns are for Day of the Week and binary variables, and will be 0 or 1 depending on if the Day matches the header, so that the model predicts in line with the same day.
Stepchange column is another binary variable, and used to highlight other changes to the site that affect consumption, like closing a few floors in a large office block. This should allow the model to then alter its behaviour based on site occupancy.
I'm trying to create a formula using TREND to go into the model column to predict consumption, so for example first row here would use the 365 rows of data to predict what should've been used on this site. After the initial period, this formula would then estimate future days on an ongoing basis, but only using that initial one year baseline.
I'm having to do all this in Excel rather than an old system I had at a previous place of work had a system that did these calcs, but new place doesn't so having to go the Excel route. Any help would be appreciated!
This data will extend for a full year in the consumption data column. This is the value I want the model to predict in the future, using the initial 365 rows.
Bank Holiday is a binary variable, showing 1 if the day is a bank holiday (Christmas, New Years Day etc) and 0 if it is not.
Heating/Cooling columns are independent variables, the value of which will be based on Heating/Cooling degree day data, which will vary by external temperature.
Mon-Sun columns are for Day of the Week and binary variables, and will be 0 or 1 depending on if the Day matches the header, so that the model predicts in line with the same day.
Stepchange column is another binary variable, and used to highlight other changes to the site that affect consumption, like closing a few floors in a large office block. This should allow the model to then alter its behaviour based on site occupancy.
I'm trying to create a formula using TREND to go into the model column to predict consumption, so for example first row here would use the 365 rows of data to predict what should've been used on this site. After the initial period, this formula would then estimate future days on an ongoing basis, but only using that initial one year baseline.
I'm having to do all this in Excel rather than an old system I had at a previous place of work had a system that did these calcs, but new place doesn't so having to go the Excel route. Any help would be appreciated!
