Calculating Cell Values with Formulas
Introduction
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
This guide will walk through each of these functions.
SUM Function
The SUM function is used to add values from multiple cells. Here's the syntax:
1=SUM(n1, n2, n3, ...)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) | ? |
After applying the formula, 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 |
PRODUCT Function
As the name suggests, all the numbers passed inside the PRODUCT function gets multiplied. Here's the syntax:
1=PRODUCT(n1, n2, n3, ...)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) | ? |
After applying the formula, 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 |
AVERAGE Function
If you want to calculate the arithmetic mean of given numbers, use the AVERAGE function. Here's the syntax:
1=AVERAGE(n1, n2, n3, ...)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) | ? |
After applying the formula, 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 |
COUNT & COUNTA Function
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," according to Excel's documentation.
The COUNT and COUNTA functions have the following syntax:
1=COUNT(n1, n2, n3, ...)
2=COUNTA(n1, n2, n3, ...)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) | ? |
After applying the formula, 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 |
IF Function
When you have a logical condition that can either be true or false, use the IF function. The IF function has the following syntax:
1=IF(condition, response_on_true, response_on_false)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) | ? |
After applying the formula, 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 |
MAX & MIN Function
You can rely on the MAX and the MIN functions to return the maximum and minimum value from a range of values respectively. Here's the syntax for both the functions:
1=MAX(n1, n2, n3, ...)
2=MIN(n1, n2, n3, ...)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) | ? |
After applying the formula, 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 |
TRIM Function
The TRIM function "removes all spaces from text except for single spaces between words," according to Excel's documentation.
The TRIM function has the following syntax:
1=TRIM(text_with_unwanted_spaces)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") | ? |
After applying the function, 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" |
DEC2BIN Function
The DEC2BIN function is used "to convert a decimal number to binary," according to Excel's documentation.
The DEC2BIN function has the following syntax:
1=DEC2BIN(num, char_required)You can convert a Decimal number to either Binary/Octal/Hex with formulas available in Excel and also vice versa with a slight modification to the formula. 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 formulas, 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, you can convert Binary to Decimal/Octal/Hex, Hex to Decimal/Octal/Binary and Octal to Decimal/Binary/Hex.
Conclusion
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: