A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available inExcel. This chapter teaches you how to enter a formula, how to edit a formula, how to display formulas instead of their results, in which order calculations occur, how to insert a function, how to copy a formula, and how to refer to a named range.
1. To edit a formula, click the formula bar and change the formula.
2. Press Enter.
Result:
1. To display the formulas instead of their results, press CTRL + (`). You can find this key above the tab key.
Result:
First, Excel performs multiplication (A1 * A2). Next, Excel adds the value of cell A3 to this result.
Another example,
First, Excel calculates the part in parentheses (A2+A3). Next, it multiplies this result by the value of cell A1.
To insert a function, execute the following steps.
1. Select the cell that you want to contain the function.
2. Click the Insert Function button.
The 'Insert Function' dialog box appears.
3. Search for a function or select a function from a category. For example, choose COUNTIF from the Statistical category.
4. Click on OK.
The 'Function Arguments' dialog box appears.
5. Click the button next to the Range textbox and select the range with address A1:C2.
6. Enter the criterion. For example, type >5.
7. Click on OK.
Result. Excel counts the number of cells that are higher than 5.
1. Enter the formula shown earlier.
2. Select cell A4, right click and then click on Copy (or press CTRL + c).
3. Select cell B4 and click on Paste under the 'Paste Options:' group (or press CTRL + v).
4. Instead of copying the formula to cell B4, you can also drag the formula to cell B4. Click on the lower right corner of cell A4 and drag it across to cell B4. This is much easier and gives the exact same result!
Result:
The formula in cell B4 automatically references the values in Column B. In the next chapter, you can learn more about cell references.
1. Enter some numbers in range("A1:A4") and select this range.
2. On the Formulas tab, click on Define Name.
3. Enter a name. For example, numbers and click on OK.
4. There is an even quicker way to name a range. Select the range and simply type the name of the range in the name box.
Note: Don't forget to press Enter after you've typed the name in the name box.
5. Now you can refer to this name in your formulas when you want to use this range. For example, sum the numbers.
Result:
Did you like this free Excel tutorial? Show your appreciation, vote for us.
For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1.
For example, cell A3 below contains the SUM function which calculates the sum of the range with address A1:A2.
Enter a Formula
To enter a formula, execute the following steps.
1. Select the cell that you want to contain the formula.
2. To let Excel know that you want to enter a formula, type an equal sign (=).
3. For example, type the formula shown earlier.
Result:
Note: instead of typing A1 and A2, while typing, simply select cell A1 and cell A2.
4. Change the value of cell A1 to 3.
Result:
Excel automatically recalculates the value of cell A3. This is one of Excel's most powerful features!
Edit a Formula
When you select a cell, Excel shows the value or formula of the cell in the formula bar.1. To edit a formula, click the formula bar and change the formula.
2. Press Enter.
Result:
Display Formulas
By default, Excel displays the results and not the formulas.1. To display the formulas instead of their results, press CTRL + (`). You can find this key above the tab key.
Result:
Operator Precedence
Excel uses a default order in which calculations occur. If a part of the formula is in parentheses, that part will be calculated first. It then performs multiplication or division calculations. Once this is complete, Excel will add and subtract the remainder of your formula. See the example below.First, Excel performs multiplication (A1 * A2). Next, Excel adds the value of cell A3 to this result.
Another example,
First, Excel calculates the part in parentheses (A2+A3). Next, it multiplies this result by the value of cell A1.
Insert a Function
Every function has the same structure. For example, SUM(A1:A4). The name of this function is SUM. The part between the brackets (arguments) means we give Excel a range with address A1:A4 as input. This function adds the values in cells A1, A2, A3 and A4. It's not easy to remember which function and which arguments to use for a specific task. Fortunately, the Insert Function feature in Excel helps you with this.To insert a function, execute the following steps.
1. Select the cell that you want to contain the function.
2. Click the Insert Function button.
The 'Insert Function' dialog box appears.
3. Search for a function or select a function from a category. For example, choose COUNTIF from the Statistical category.
4. Click on OK.
The 'Function Arguments' dialog box appears.
5. Click the button next to the Range textbox and select the range with address A1:C2.
6. Enter the criterion. For example, type >5.
7. Click on OK.
Result. Excel counts the number of cells that are higher than 5.
Copy a Formula
When you copy a formula, Excel adjusts the cell references in the formula to the new location. To understand this, execute the following steps.1. Enter the formula shown earlier.
2. Select cell A4, right click and then click on Copy (or press CTRL + c).
3. Select cell B4 and click on Paste under the 'Paste Options:' group (or press CTRL + v).
4. Instead of copying the formula to cell B4, you can also drag the formula to cell B4. Click on the lower right corner of cell A4 and drag it across to cell B4. This is much easier and gives the exact same result!
Result:
The formula in cell B4 automatically references the values in Column B. In the next chapter, you can learn more about cell references.
Names in Formulas
Instead of using cell references in your formulas, you can also refer to a named range. This way you can make your formulas easier to understand. Execute the following steps to define a name for a range and refer to that name in a formula.1. Enter some numbers in range("A1:A4") and select this range.
2. On the Formulas tab, click on Define Name.
3. Enter a name. For example, numbers and click on OK.
4. There is an even quicker way to name a range. Select the range and simply type the name of the range in the name box.
Note: Don't forget to press Enter after you've typed the name in the name box.
5. Now you can refer to this name in your formulas when you want to use this range. For example, sum the numbers.
Result:
Did you like this free Excel tutorial? Show your appreciation, vote for us.
0 comments:
Post a Comment