Growth Formula in excel is an inbuilt formula in excel which works on linear data set, it gives us the value for the growth in exponential trend which matches to the data points which are recognized, this formula takes four arguments two of them being the X and Y axis which are with us and a new X-axis with a constant.
Growth Formula Excel (Table of Contents)
Excel Growth Formula
When you want to estimate or predict the sales growth based on the previous year data how will go about it. Nothing to worry we have a built-in function called GROWTH which will do the job for us.
Let’s take a look at the formula of excel GROWTH function.
- Know Y’s: This is nothing but your sales value or previous data points.
- Know X ’s: This is your year column. Based on this X value we will estimate the GROWTH for new x values.
- New X ’s: For new x value we will estimate the new Y values
- Const: This is a logical statement. TRUE means b will be calculated normally. FALSE means b is equal to 1.
What is GROWTH Formula Do?
As the name itself suggests GROWTH function predicts the exponential growth of the available previous data and says whether the targeted amount is actually achievable or not by using previous data points.
For an example, if you sales data for 2015, 2016, and 2017 as 5000, 6000, and 5500 respectively, based on these two values GROWTH function predicts the exponential growth based on the trend of these available data points.
Here Sales Amount is the Y values and Year is the X values. Based on these X & Y values GROWTH function estimated the revenues for new X values i.e. for New Year.
How to Use GROWTH Formula in Excel?
Below are some of the examples of Growth Formula in Excel.
Assume below are your X values and Y values in two separate columns of the excel sheet.
Based on this data we need to calculate the excel GROWTH formula for new X’s.
Step 1: Open GROWTH function in excel.
Step 2: Select Known Y’s as B2 to B8 range.
Step 3: Select Know X’s as A2 to A8 range.
Step 4: Since we don’t have new x to represent, leave out last two arguments and hit the enter key. We have a new growth rate.
Assume you have sales values from the year 2011 to 2017 in the excel sheet. Based on this sales data we need to estimate the revenue for the year 2018.
Copy and paste the data to excel and follow below steps to estimate the revenue for the year 2018.
Step 1: Open Excel GROWTH function in the B9 cell.
Step 2: Know Y’s are our Revenue column so select from B2 to B8.
Step 3: Known X’s are our year column, so select A2 to A8.
Step 4: New X’s is our New Year estimation. Since we are estimating for the year 2018, select A9 cell as New X’s.
Step 5: Close the bracket and hit enter. We would have new exponential growth revenue in the B9 cell.
So, revenue estimation for the year 2018 (new x’s) is USD 2,12,560.
Now let’s continue the above example data. We have estimated the revenue for the tear 2018. If we need to estimate the revenue for more than one New X’s i.e. for more than one year we need to enter the formula as an array formula, not the regular one.
Step 1: When you select the Know Y’s make them absolute reference by pressing the F4 key.
Step 2: Similarly make the absolute reference for Known X’s as well.
Step 3: For New X’s select all the three-year cells but do not make them absolute reference rather just keep them as a normal reference.
Step 4: We cannot simply close the function, we need to close this as an array function. So Press Ctrl + Shift + Enter keys together. It will insert curly brackets to either end of the formula.
Step 5: Now since it is an array formula copy and paste the growth excel formula doesn’t work. So fill down the formula using Ctrl + D.
Things to Remember
- Apart from Know Y’s all the other arguments are optional in excel GROWTH function.
- GROWTH predicts the new y values based on previous data trend.
- The length of know y and know x should be the same.
- GROWTH formula in excel accepts only positive values, if the values are zero or negative then we will get #NUM! Error.
This has been a guide to Growth Formula in Excel. Here we discuss how to use Growth Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –