Uday has attended:
Excel Intermediate course
Trend function in excel
Hello,
I am trying to generate a spreadsheet which will extrapolate trends for revenue by industry sectors. I have columns of the various industries, and rows of financial years, with data of total billing per year for each sector.
I am using the TREND function to extrapolate data from the previous years to estimate revenue for 2024, 2025 and 2026.
What I am struggling with is understanding whether I should have the constant in the formula as "TRUE" or "FALSE". Both return different results, and I am unclear on which result I need. Largely because I am struggling to understand (in plain English) what the difference between "b" being treated normally and "b" being "0" is, or what its implications are for my calculations.
Please could someone explain this to me?
Thank you,
Uday
RE: Trend function in excel
Hi Uday,
Thank you for the forum question.
The trend equation is y = mx + b, where m is the slope, x is the variable and b is the y interceptor.
Please imaging that you have added a trendline to a chart and that you are looking at a year of data. Imaging that your trendline starts from 10 in January and ends 65 in December. b is the value the trendline will cross the y axis on the chart (period 0).
Let us imaging that we have started a new business in January and we are starting from zero sales before January. If we calculate the trend and the false is used in the trend function, the trend function will return the result starting from zero sales to the end sales in December. If we want to know how far we have got from zero to end month.
If we want to see how far we have got from January to December we should use True in the trend function.
You can also use the FORECAST.LINEAR function.
I hope this makes sense. I have attached an Excel file to visualise this.
We have some amazing forecast and trend tools in Excel, which can give us some very accurate estimations. The trend function assume that the world is linear, but is the world linear?
Check our Excel Forecast & Data Analysis course.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
trend.xlsx
