MS Excel - Lesson 5: Excel formulas and functions

The formula in Excel is a program that performs calculations on data tables. These formulas perform very precise operations such as adding, multiplying, or comparing values ​​in worksheets.

Components of an Excel formula

The formula always starts with an equal sign ( = ), behind it can be numbers, operators and Excel functions.

Examples of some simple formulas in Excel:

1. = 2 * 5 + 8 (This formula is 2 times 5 then add 8)
2. = A2 * A3-A5 (Value of cell A2 times the value of cell A3 and subtracts the value in cell A5)
3. = SUM (B1: B10) (Use the SUM calculation function to calculate the total value of cells B1 to B10)
4. = TODAY () (Use TODAY function to return the current date value)
5. = UPPER ("hello") (UPPER function used to convert lowercase text to uppercase, the result here will convert "hello" to "HELLO")
6. = IF (A1> 0) (IF condition function to check if cell A1 contains any value greater than 0)

The basic parts of an Excel formula:

Excel alcohol formulas can include one or all of the following components: functions, references, operators and constants .

See the example below:

1. Function: PI () function returns the value of pi: 3,142 .
2. Reference: A2 returns the value in cell A2
3. Constants: Value or text entered directly into the formula, in this example is 2
4. Operator: Operator * (asterisk) to multiply numbers and ^ (caps) to raise a number to some power, here is 2

Enter Excel formulas

- Put the cursor in the cell to enter the formula and display the result.

- Type ' = '

- Enter the expression you want to calculate, for example: A5 + E5. With this formula, it will add the value of cell A5 to E5.

- After entering the formula, press Enter. The result of the calculation will be displayed in the newly entered cell

- If the formula fails, an error message will display starting with a #

Enter the reference domain with the cursor

- Put the cursor in the cell to display the formula result

- Enter the formula to calculate on a value column or domain. For example, enter the formula = E2 + E5, which means the value of cell E2 adds the value of cell E5.

- Using the arrow keys, move the cursor from the first reference cell (in this case, E2). The formula will check and the current value will be included in the formula

- Type permission +

- Using the arrow keys, move the cursor to the second reference cell, (in this case, E5). If you calculate in a data area, hold down the Shift key and move the arrow keys to all cells to be counted.

- After selecting all the cells for the formula, press Enter to get results

Basic Excel functions

Functions are special commands used in formulas to perform calculations.

Reference: These are the most basic functions in Excel that you need to understand

Enter functions into spreadsheets

- Select the box to display the formula results

- Insert the sign ( = ) before the formula. The toolbar of the formula displays

- Type a function name (eg SUM function), then open parentheses ' ( ', (because many arguments depend on the function), and end with ' ) '. For example, type SUM (E2: E5).

- Press the Enter key. (If there is no error in the formula, the result of the function will be displayed. If you click on that cell, the function will display in the formula bar).

Use the AutoSum function

- Functions can be accessed via the AutoSum icon on the Standard toolbar. Some functions will be displayed when clicking on the numbness next to the AutoSum icon.

- For example, the current cell is located below the list of values, AutoSum will display a total function with a list of consecutive arguments on it.

- Function Sum : Calculates the total list of arguments

- Average function: The function calculates the average value of the list of arguments

- Function Count : Count values ​​in the list of arguments

- Max : Returns the largest value in the list of arguments

- Min : Returns the smallest value in the list of arguments

Example : Using the Average function

Enter the values ​​shown below from B1 to B5, select cell B6 to enter the formula

- Click the down arrow next to the AutoSum icon on the Standard toolbar and select the Average function from that menu.

- If the area is selected correctly, press the Enter key. If the selection is not correct, select (click or drag) the area you want with the mouse and press the Enter key

- Results are shown in cell B6. You can use the above method to apply to other functions like Sum, Max, .

Excel formulas and functions are a special feature in Microsoft Excel. It is really important and you can master it.