While plotting graphs, users often need to find out or predict an unknown value relative to the present values. This allows them to forecast a result based on the existing data. In this article, we will show how to find unknown value on Excel graph.
Download Practice Workbook
You can download the practice workbook from here.
4 Ways to Find Unknown Value on Excel Graph
In this article, we will discuss 4 effective ways to find an unknown value in an Excel graph. Firstly, we will use the TREND function. Secondly, we will use the Trendline equation. Then, we will opt for the FORECAST function to find the unknown value. Finally, we will use the GROWTH function to find the unknown value.
1. Using TREND Function
The TREND function returns values that have a linear trend. In this method, we will use the function to find the unknown Y value based on a known X value.
Steps:
- Firstly, select the D10 cell and write the following formula,
=TREND(D5:D9,C5:C9,C10)- Hit Enter.
- Consequently, we will have our unknown value and can plot it.
Read More: How to Get Y Equation on Excel Graph (6 Ways)
2. Applying Trendline Equation
A trendline shows the changeling trend of data. This line will either have linear or nonlinear characteristics. We will mark out the trendline equation in this case and then find the unknown value.
Steps:
- Firstly, select the cells containing the known X and Y
- In our case, the cells are in the range (C5:D9).
- Then, go to the Insert tab in the ribbon.
- From there, choose the Insert Scatter(X,Y) or Bubble Chart command.
- Finally, from the drop-down select a scatter plot.
- Consequently, a chart will be plotted.
- Then, click the “Plus” sign to the right of the chart.
- Add a trendline by checking the Trendline box.
- As a result, Excel will add a trendline to the plot.
- After that, select any of the data points.
- Then, from there go to the Trendline Options.
- Finally, check the “Display Equation on chart” box.
- As a result, the equation of the chart will be shown on the screen.
- Then, replicate the trendline formula in the D10 cell as follow,
=2*C10+200- Finally, hit Enter.
- Consequently, we will have an unknown value.
Read More: How to Graph Two Equations in Excel (with Easy Steps)
3. Applying FORECAST Function
The FORECAST function utilizes current values to calculate or forecast a future value. For a specific x-value, the future value is a y-value. The future value is projected using linear regression using the known x-values and y-values for the current values.
Steps:
- To begin with, select the D10 cell and write the following formula,
=FORECAST(C10,D5:D9,C5:C9)- Then, hit the Enter button.
- As a result, we will have the unknown value and can plot it on the graph.
Read More: How to Display Equation of a Line in Excel Graph (2 Easy Ways)
4. Using GROWTH Function
The GROWTH function uses data that is already available to calculate expected exponential growth. By leveraging pre-existing x-values and y-values, the GROWTH function returns the y-values for a set of new x-values that you specify.
Steps:
- To start with, choose the D10 cell and write the following formula,
=GROWTH(D5:D9,C5:C9,C10)- Then, press the Enter button.
- As a result, we will get the unknown value and can plot it on the graph.
How to Interpolate in Excel
Data interpolation is a technique for creating a simple function from a discrete data collection that passes through the specified data points. Finding the data points between the given data points is made easier by doing this.
Steps:
- To start with, choose the G5 cell and write the following formula,
=D6+(G4-C6)*(D7-D6)/(C7-C6)- Then, hit the Enter button.
- As a result, we will get the interpolated value.
Read More: How to Find the Equation of a Line in Excel (With Quick Steps)
Conclusion
In this article, we have talked about 4 effective ways to find unknown value in an Excel graph. These methods will allow users to properly predict a y-value with a specific x-value. If you find it useful, please let us know in the comment section below and share any recommendations and thoughts regarding this or any other content of ours. Thank you for your time. Follow ExcelDemy.com for more articles like this.
