Equations In Excel

What Do You Mean By Equations In Excel?

Equations in Excel are formulas that execute calculations based on the values provided as input. And an equation starts with the ‘=‘ sign, followed by values and functions, with arithmetic operators in between.

Users can use the Excel equations feature to solve complex mathematical problems and for financial and statistical analyses and data validation.

For example, the table below lists a student’s test scores in six subjects.

And the task is to determine the total aggregate of the student, based on the test scores, using the arithmetic operator ‘+’ and the Excel SUM function. Assume the target cells are B9 and B10.

Then, we can use the arithmetic operator and inbuilt function-based sum equations in Excel cells B9 and B10 to achieve the required output, as shown below.

In the above sum equations in Excel example, the cell B9 formula contains the cell references to the test scores, separated by the arithmetic operator ‘+’. And the equation adds the specified values to give the required total aggregate.

On the other hand, the cell B10 equation uses the SUM(), which accepts the reference to the cell range containing the test scores. And the function adds the input values to return the required total aggregate.

What Do You Mean By Equations In Excel?

The equations in Excel are formulas containing values, cell references to values, and inbuilt functions, separated by arithmetic operators. And they process the calculations on the specified values.
Users can use Excel equations to perform complicated evaluations and data analysis when working with financial and statistical data.
We can enter an equation manually in a cell. And if the equation involves Excel inbuilt functions, we can enter them manually into a cell. Otherwise, we can use the Function Library group in the Formulas tab to enter the function one at a time.

Explanation

Typically, Excel equations start with the ‘=‘ sign and contain the following components:

Values, defined names or Excel cells references to the values
Operators
Excel inbuilt functions

While we can use the values directly in an equation as constants, using defined names and references to cells and ranges containing the values is also valid.

And we can use arithmetic operators, such as ‘+’, ‘–’, ‘*’, ‘/’, and ‘^’, to perform the required calculations using the specified values.

Furthermore, Excel offers inbuilt functions, which are useful for quickly and accurately performing complex calculations. We can use the function as a standalone or a combination of the inbuilt functions, values and operators to create an equation to execute the required calculations.

How To Use Equations In Excel?

The steps for using and solving equations in Excel are as follows:

Choose a target cell where we must enter an equation.
First, enter the Equal To sign, ‘=’.
Enter the required value, cell reference to the value, or the appropriate inbuilt function.
Enter the operator based on the calculation we must perform using the entered value.
Next, enter the remaining values, cell references, or the appropriate inbuilt functions, separated by the applicable operators, to form the required equation.
Press Enter to execute the entered formula and view the equation output.

Examples

Check out the following examples of Excel equations to use the feature effectively.

Example #1

We shall see an example of IF equations in Excel.

The table below lists employees, their designations and monthly salaries.

The requirement is to update the employees’ monthly salaries after the hike in column E cells, with the cells’ data format being Currency in Home → Number Format.

And the conditions are as follows:

Engineers: Add $1,000 to the monthly salary.
Senior Engineers: Add $1,100 to the monthly salary.
Specialists: Add $900 to the monthly salary.

Then we can use IF equations in Excel cells E2:E7 to obtain the required output.

Step 1: Choose cell E2, and enter the ‘=’ sign and the IF Excel function, as shown below.

=IF(C2=”Engineer”,D2+1000,(IF(C2=”Senior Engineer”,D2+1100,(IF(C2=”Specialist”,D2+900,”NA”)))))

Step 2: Press Enter to execute the IF function equation.

Step 3: Enter the IF() in the remaining target cells E3:E7 using the Excel fill handle.

We can enter the formula in each target cell and press Enter to execute them individually. But the fill handle helps in solving equations in Excel cells quickly when we must apply the same formula in consecutive cells, row or column-wise.

The IF equation starts with the ‘=‘ sign, followed by the IF() name and opening parenthesis.

Next, the function accepts three inputs, separated by commas. The first argument is the condition to check to display the output. And the second and the third arguments are the TRUE and FALSE values to show as the function return value.

Finally, we close the parenthesis to complete the equation.

Further, we use the arithmetic operator, ‘+’, in the TRUE and FALSE values to add the required value to the salary figure based on the employee designation.

Example #2

We shall see how to create the regression equations in Excel for the given predictor and response variables.

The below image shows two datasets. While the first set contains one predictor variable (x) and one response variable (y), the second set contains two predictor variables (x1, x2) and one response variable (y).

Then, here is how to use the Excel LINEST function equations in cells D2:E2 and K2:M2 to obtain the regression equations in Excel for the two datasets in cells A15 and G15.

Step 1: Select cells D2:E2, enter the ‘=‘ sign, and start typing the LINEST function name, as shown below.

Excel will list all the functions starting with the entered phrase. Double-click on the LINEST function to enter the function in the chosen cells.

Excel will show the function syntax to help us enter the function arguments.

The LINEST() accepts four arguments as input. The first two arguments are known_ys and known_xs. And they are the range of response or dependent y-values and the predictor or independent x-values in the required regression equation, respectively.

The third and fourth arguments, const and stats, are logical values that indicate how to treat the intercept and whether to show additional statistics.

Furthermore, only the first argument in the LINEST() is mandatory; the remaining are optional.

And then, the function uses the least squares method to evaluate the statistics for a line to obtain a straight line that best fits the source data. And it returns an array that describes the line in terms of slopes and an intercept.

Next, enter the required function arguments.

=LINEST(A2:A11,B2:B11)

Step 2: Press Ctrl + Shift + Enter to execute the LINEST() equation as an array formula.

{=LINEST(A2:A11,B2:B11)}

The first dataset contains only one predictor and one response variable. So, the corresponding regression equation will include one slope and intercept values.

And thus, we apply the LINEST() as an array formula in two cells, D2:E2.

Step 3: Choose cell A15, and using the slope and intercept values obtained using the LINEST() equation, enter the required regression equation.

We can also use the Function Library group in the Formulas tab to enter the Excel inbuilt function in a cell, whether we execute it as a normal equation or array formula.

Step 4: Select cells K2:M2 and select Formulas → More Functions → Statistical → LINEST.

The Function Arguments window opens.

Enter the first two field values, as shown below:

So, the equation in the chosen cells will be:

=LINEST(G2:G11,H2:I11)

Step 5: Press Ctrl + Shift + Enter to execute the equation as an array formula.

{=LINEST(G2:G11,H2:I11)}

The second dataset contains two predictor variables and one response variable. So, the corresponding regression equation will include two slopes and one intercept.

And thus, we apply the LINEST() as an array formula in three cells K2:M2.

Step 6: Choose cell G15, and using the two slope and intercept values obtained using the LINEST() equation, enter the required regression equation.

Thus, we obtained the required regression equations using the Excel array formulas and the LINEST()-based equations. We can now use the two regression equations to predict the y-values using the respective predictor values.

Important Things To Note

All equations in Excel must begin with the ‘=‘ sign.
Use an Excel absolute reference to values when copying a cell’s equation, with the same cell references, into ensuing cells in the same row or column.
Check the Formula Bar to view the equation in the chosen cell, and use the Formulas → Show Formulas option to view the equation inside the chosen cell.

Frequently Asked Questions (FAQs)

1. How to solve quadratic equations in Excel?

We can solve quadratic equations in Excel using the Goal Seek feature in the Data tab.

For example, the following is the given quadratic equation:
x² + 5x + 6 = 56

Now, setting x as -10 and 5 will solve the above equation.

However, here is how to use the Goal Seek feature to solve the equation in Excel.

• Step 1: We shall create a dataset. Column A contains x-values, and column B contains y-values.

Next, let us set the x-value in cell A2 as 0 and enter the left-hand side of the given quadratic equation in cell B2 referencing cell A2.

The equation in cell B2 substitutes the cell A2 value as the x-value to give the y-value.

• Step 2: Select cell B2, and choose Data → What-If Analysis → Goal Seek.

The Goal Seek window will open, where we must update the target cell reference as cell B2, the target y-value as that in the given equation, 56. And the third field will be the x-value cell reference, which we must change to achieve the y-value in cell B2.

Clicking OK in the Goal Seek window will process the quadratic equation calculation to give the x-value in cell A2, resulting in the required y-value in cell B2. And the resulting x-value will be close to the x-value we initially supplied to use the Goal Seek option.

• Step 3: Click OK to close the Goal Seek window and view the output in cells A2:B2.

Thus, we obtained one value of x, 5, which will solve the given quadratic equation to give the required result of 56.

Next, as the other x-value must be negative, we can enter the x-value as -5 in cell A2.

• Step 4: Select cell A2 and enter the value of -5.

The cell B2 formula automatically calculates the y-value based on the specified x-value.

• Step 5: Select cell B2 and choose Data → What-If Analysis → Goal Seek.

The Goal Seek window will open, where we must update the fields as shown below. And click OK.

Finally, clicking OK will give the following output.

Thus, we get the second x-value of -10, which will solve the given quadratic equation to yield 56.

2. How to show equations in Excel?

We can show equations in Excel using the following steps:

1) Select the cells where we must show the equations.
2) Follow the path Formulas → Show Formulas to display the equations in the chosen cells.

3. How to lock equations in Excel?

The steps to lock equations in Excel are as follows:

1) First, we shall choose all the cells in the worksheet and unlock them.
• Press Ctrl + A to choose all the cells in the sheet.
• Press Ctrl + 1 to access the Format Cells window.
• Select the Protection tab in the Format Cells window.
• Uncheck the Locked option.
• Click OK.

2) Choose all the cells containing equations.
• Press Ctrl + A to choose all the cells in the sheet.
• Select Home → Find & Select → Go to Special.
• Select Formulas in the Go To Special window.
• Click OK.

3) Lock the cells containing equations.
• With the cells containing equations selected, press Ctrl + 1.
• The Format Cells window will open, where we must click the Protection tab.
• Check the Locked option.
• Click OK.

4) Protect the spreadsheet.
• Select the Review tab → choose the Protect Sheet option.
• The Protect Sheet window opens, where we must ensure the option, Protect worksheet and contents of the locked cells, is checked.
• Click OK.

Download Template

This article must be helpful to understand the Equations In Excel, with its formula and examples. You can download the template here to use it instantly.

Equations In Excel Download

Equations In Excel