Introduction

4

In this guide, you will learn how to calculate cell values using various functions in Microsoft Excel 2019.

Excel formulas allow users to perform calculations on large amounts of data in a very short time and simplified manner. For an example, what if a teacher wants to calculate the average grade scored by thousands of students in a subject? It would take him hours to calculate the average using a calculator, but through Excel, a simple drag of cells containing grades and a simple formula can do the work in a few seconds. Excel formulas play an important role in the lives of students, auditors, business professionals, an many others.

There are various functions present in the Excel 2019 version to calculate cell values:

- SUM
- PRODUCT
- AVERAGE
- COUNT & COUNTA
- IF
- MAX & MIN
- TRIM
- DEC2BIN

Let's walk through each of these functions.

The `SUM`

function is used to add values. It can be used to add individual values, cell references or ranges, or a mix of all three.

The `SUM`

function has the following syntax:

`1`

`=SUM( number1, [number2], ... )`

The arguments used in the function are the numbers that need to be added. As mentioned above, arguments can be individual values, cell references or ranges. To illustrate this, let's consider the example given below:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 103 | 53 | 21 | 3423 | =SUM(B1:E1) | ? |

2 | 122 | 25 | 51 | 321 | =SUM(B2, C2, D2, E2) | ? |

3 | 88 | 50 | 15 | 12 | =SUM(88, 50, 15, 12) | ? |

4 | 62 | 57 | 17 | 343 | =SUM(B4, D4) | ? |

5 | 15 | 51 | 11 | 87 | =SUM(B2:E2, B5:E5) | ? |

The result column will be updated as given below:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 103 | 53 | 21 | 3423 | =SUM(B1:E1) | 3600 |

2 | 122 | 25 | 51 | 321 | =SUM(B2, C2, D2, E2) | 519 |

3 | 88 | 50 | 15 | 12 | =SUM(88, 50, 15, 12) | 165 |

4 | 62 | 57 | 17 | 343 | =SUM(B4, D4) | 79 |

5 | 15 | 51 | 11 | 87 | =SUM(B2:E2, B5:E5) | 683 |

The `PRODUCT`

function multiplies all the numbers given as arguments and returns the product.

The `PRODUCT`

function has the following syntax:

`1`

`=PRODUCT(number1, [number2], ...)`

The arguments used in the function are the numbers that need to be multiplied. The `PRODUCT`

function is useful when you need to multiply many cells together. To illustrate this, let's consider the example given below:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =PRODUCT(B1:E1) | ? |

2 | 22 | 2 | 5 | 31 | =PRODUCT(B2:E2, 3) | ? |

3 | 8 | 50 | 5 | 2 | =8 50 5 * 2 | ? |

4 | 6 | 7 | 17 | 3 | =PRODUCT(C4, E4) | ? |

5 | 15 | 5 | 11 | 8 | =PRODUCT(B2:E2, B5:E5) | ? |

The result column will be updated as given below:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =PRODUCT(B1:E1) | 2457 |

2 | 22 | 2 | 5 | 31 | =PRODUCT(B2:E2, 3) | 20460 |

3 | 8 | 50 | 5 | 2 | =8 50 5 * 2 | 4000 |

4 | 6 | 7 | 17 | 3 | =PRODUCT(C4, E4) | 21 |

5 | 15 | 5 | 11 | 8 | =PRODUCT(B2:E2, B5:E5) | 45012000 |

The `AVERAGE`

function returns the average (arithmetic mean) of the arguments.

The `AVERAGE`

function has the following syntax:

`1`

`=AVERAGE(number1, [number2], ...)`

Arguments present in the function can be numbers or names, ranges, or cell references that contain numbers. To illustrate this, let's consider the example given below:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =AVERAGE(B1:E1) | ? |

2 | 22 | 2 | 5 | 31 | =AVERAGE(B2:E2, 3) | ? |

3 | 6 | 7 | 17 | 3 | =PRODUCT(C3:E3) | ? |

4 | 15 | 5 | 11 | 8 | =PRODUCT(B2:B4) | ? |

The result column will be updated as given below:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =AVERAGE(B1:E1) | 10 |

2 | 22 | 2 | 5 | 31 | =AVERAGE(B2:E2, 3) | 12.6 |

3 | 6 | 7 | 17 | 3 | =PRODUCT(C3:E3) | 9 |

4 | 15 | 5 | 11 | 8 | =PRODUCT(B2:B4) | 14 |

The `COUNT`

function is used to get the number of entries in a number field that is in a range or array of numbers, whereas the `COUNTA`

function counts the number of cells that are not empty in a range.

The `COUNT`

function has the following syntax:

`1`

`=COUNT(value1, [value2], ...)`

The `COUNTA`

function has the following syntax:

`1`

`=COUNTA(value1, [value2], ...)`

Let's consider the example given below to understand the use of `COUNT`

and `COUNTA`

functions better:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =COUNT(B1:E1) | ? |

2 | 22 | 2 | 5 | 31 | =COUNTA(B2:E2) | ? |

3 | 6 | 7 | NAME | =COUNT(B3:E3) | ? | |

4 | TOM | 5 | 11 | =PRODUCT(B4:E4) | ? |

The result column will be updated as given below:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =COUNT(B1:E1) | 4 |

2 | 22 | 2 | 5 | 31 | =COUNTA(B2:E2) | 4 |

3 | 6 | 7 | NAME | =COUNT(B3:E3) | 2 | |

4 | TOM | 5 | 11 | =COUNTA(B4:E4) | 3 |

The `IF`

function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if that condition is TRUE or FALSE.

The `IF`

function has the following syntax:

`1`

`=IF(logical_test, [value_if_true], [value_if_false])`

The `logical_test`

argument used in the function is any value or expression that can be evaluated to TRUE or FALSE. Let's consider the example given below to understand this function:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =IF(B1 > D1, C1, FALSE) | ? |

2 | 22 | 2 | 5 | 31 | =IF(NOT(B2 = E2), TRUE, FALSE) | ? |

3 | 8 | 50 | 5 | 2 | =IF(B3 > 20, C1, D1) | ? |

4 | 6 | 7 | 17 | 3 | =IF(C4 = E4, TRUE, FALSE) | ? |

The result column will be updated as given below:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =IF(B1 > D1, C1, FALSE) | FALSE |

2 | 22 | 2 | 5 | 31 | =IF(NOT(B2 = E2), TRUE, FALSE) | TRUE |

3 | 8 | 50 | 5 | 2 | =IF(B3 > 20, C1, D1) | 5 |

4 | 6 | 7 | 17 | 3 | =IF(C4 = E4, TRUE, FALSE) | FALSE |

The `MAX`

function is used to return the largest value in a set of values, whereas the `MIN`

function returns the smallest number in a set of values.

The `MAX`

function has the following syntax:

`1`

`=MAX(number1, [number2], ...)`

The `MIN`

function has the following syntax:

`1`

`=MIN(number1, [number2], ...)`

Let's consider the example given below to understand the use of both of the functions:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =MAX(B1:E1) | ? |

2 | 22 | 2 | 5 | 31 | =MIN(B1:B4) | ? |

3 | 6 | 7 | 17 | 3 | =MAX(B1:E4) | ? |

4 | 15 | 5 | 11 | 8 | =MIN(B1:E4) | ? |

The result column will be updated as given below:

A | B | C | D | E | F | G |
---|---|---|---|---|---|---|

Sr. Number | Value1 | Value2 | Value3 | Value4 | Formula | Result |

1 | 13 | 3 | 21 | 3 | =MAX(B1:E1) | 21 |

2 | 22 | 2 | 5 | 31 | =MIN(B1:B4) | 6 |

3 | 6 | 7 | 17 | 3 | =MAX(B1:E4) | 31 |

4 | 15 | 5 | 11 | 8 | =MIN(B1:E4) | 2 |

The `TRIM`

function removes all spaces from text except for single spaces between words.

The `TRIM`

function has the following syntax:

`1`

`=TRIM(text)`

The `TRIM`

function can be used on text that you have received from another application that may have irregular spacing. Let's consider the example given below to understand the functionality better:

A | B | C | D |
---|---|---|---|

Sr. Number | TEXT | FORMULA | RESULT |

1 | APPLE | =TRIM(" APPLE ") | ? |

2 | I AM A DOCTOR | =TRIM(" I AM A DOCTOR ") | ? |

3 | GRADES | =TRIM("GRADES") | ? |

The result column will be updated as given below:

A | B | C | D |
---|---|---|---|

Sr. Number | TEXT | FORMULA | RESULT |

1 | APPLE | =TRIM(" APPLE ") | "APPLE" |

2 | I AM A DOCTOR | =TRIM(" I AM A DOCTOR ") | "I AM A DOCTOR" |

3 | GRADES | =TRIM("GRADES") | "GRADES" |

The `DEC2BIN`

function is used to convert a decimal number to binary.

The `DEC2BIN`

function has the following syntax:

`1`

`=DEC2BIN(number, [places])`

We can convert a **Decimal** number To **Binary/Octal/Hex** With formulas or vice versa with a slight modification to the formula. Let's consider an example below to illustrate this:

A | B | C | D | E |
---|---|---|---|---|

Sr. Number | DECIMAL | BINARY | HEX | OCTAL |

1 | 4 | =DEC2BIN(B1) | =DEC2HEX(B1) | =DEC2OCT(B1) |

2 | 120 | =DEC2BIN(B2) | =DEC2HEX(B2) | =DEC2OCT(B2) |

2 | 356 | =DEC2BIN(B3) | =DEC2HEX(B3) | =DEC2OCT(B3) |

After applying the formula, the result will be updated as shown below:

A | B | C | D | E |
---|---|---|---|---|

Sr. Number | DECIMAL | BINARY | HEX | OCTAL |

1 | 4 | 100 | 4 | 4 |

2 | 120 | 1111000 | 170 | 78 |

2 | 356 | 101100100 | 544 | 164 |

Similarly, we can now convert **Binary** to **Decimal/Octal/Hex**, **Hex** to **Decimal/Octal/Binary** and **Octal** to **Decimal/Binary/Hex**.

You can use the syntax below to convert **Binary** to **Decimal**:

`1`

`=BIN2DEC(number, [places])`

Syntax for **Binary** to **Octal**:

`1`

`=BIN2OCT(number, [places])`

Syntax for **Binary** to **Hex**:

`1`

`=BIN2HEX(number, [places])`

You can use the syntax below to convert **Hex** to **Decimal**:

`1`

`=HEX2DEC(number, [places])`

Syntax for **Hex** to **Octal**:

`1`

`=HEX2OCT(number, [places])`

Syntax for **Hex** to **Binary**:

`1`

`=HEX2BIN(number, [places])`

You can use the syntax below to convert **Octal** to **Decimal**:

`1`

`=OCT2DEC(number, [places])`

Syntax for **Octal** to **Binary**:

`1`

`=OCT2BIN(number, [places])`

Syntax for **Octal** to **Hex**:

`1`

`=OCT2HEX(number, [places])`

In this guide, you have learned a few of the formulas that calculate cell values, but there are hundreds more Excel formulas you can explore in Excel to make your work a lot easier. You may also like to look at the following guides on Excel topics:

4