Excel Formulas and Functions
1. SUM
The SUM() function, as the name suggests, gives the total of the
selected range of cell values. It performs the mathematical operation which is
addition. Here’s an example of it below:
Sum "=SUM(C2:C4)"
Fig: Sum function in Excel
As you can see above, to find the total amount of sales for
every unit, we had to simply type in the function “=SUM(C2:C4)”. This
automatically adds up 300, 385, and 480. The result is stored in C5.
2. AVERAGE
The AVERAGE() function focuses on calculating the average of the
selected range of cell values. As seen from the below example, to find the avg
of the total sales, you have to simply type in:
AVERAGE =AVERAGE(C2, C3, C4)
Fig: Average function in Excel
It automatically calculates the average, and you can store the
result in your desired location.
3. COUNT
The function COUNT() counts the total number of cells
in a range that contains a number. It does not include the cell, which is
blank, and the ones that hold data in any other format apart from
numeric.
COUNT =COUNT(C1:C4)
Fig: Microsoft Excel Function - Count
As seen above, here, we are counting from C1 to C4, ideally four
cells. But since the COUNT function takes only the cells with numerical values
into consideration, the answer is 3 as the cell containing “Total Sales” is
omitted here.
If you are required to count all the cells with numerical
values, text, and any other data format, you must use the function ‘COUNTA()’.
However, COUNTA() does not count any blank cells.
To count the number of blank cells present in a range of cells,
COUNTBLANK() is used.
4. SUBTOTAL
Moving ahead, let’s now understand how the subtotal function
works. The SUBTOTAL() function returns the subtotal in a database. Depending on
what you want, you can select either average, count, sum, min, max, min, and
others. Let’s have a look at two such examples.
Fig: Subtotal function in Excel
In the example above, we have performed the subtotal calculation
on cells ranging from A2 to A4. As you can see, the function used is
SUBTOTAL =SUBTOTAL(1, A2: A4)
In the subtotal list “1” refers to average. Hence, the above
function will give the average of A2: A4 and the answer to it is 11, which is
stored in C5. Similarly,
“=SUBTOTAL(4, A2: A4)”
This selects the cell with the maximum value from A2 to A4,
which is 12. Incorporating “4” in the function provides the maximum
result.
5. MODULUS
The MOD() function works on returning the remainder when a
particular number is divided by a divisor. Let’s now have a look at the
examples below for better understanding.
·
In
the first example, we have divided 10 by 3. The remainder is calculated using
the function
MODULUS =MOD(A2,3)
·
The
result is stored in B2. We can also directly type “=MOD(10,3)” as it will give
the same answer.
Fig: Modulus function in Excel
·
Similarly,
here, we have divided 12 by 4. The remainder is 0 is, which is stored in
B3.
Fig: Modulus function in Excel
6. POWER
The function “Power()” returns the result of a number raised to
a certain power. Let’s have a look at the examples shown below:
Fig: Power function in Excel
As you can see above, to find the power of 10 stored in A2
raised to 3, we have to type:
Power =POWER (A2,3)
This is how power function works in Excel.
7. CEILING
Next, we have the ceiling function. The CEILING() function
rounds a number up to its nearest multiple of significance.
Fig: Ceiling function in Excel
The nearest highest multiple of 5 for 35.316 is 40.
8. FLOOR
Contrary to the Ceiling function, the floor function rounds a
number down to the nearest multiple of significance.
Fig: Floor function in Excel
The nearest lowest multiple of 5 for 35.316 is 35.
9. CONCATENATE
This function merges or joins several text strings into one text
string. Given below are the different ways to perform this function.
·
In
this example, we have operated with the syntax:
CONCATENATE =CONCATENATE(A25, " ", B25)
Fig: Concatenate function in Excel
·
In
this example, we have operated with the syntax:
"=CONCATENATE(A27&"
"&B27)"
Fig: Concatenate function in Excel
Those were the two ways to implement the concatenation operation
in Excel.
10. LEN
The function LEN() returns the total number of characters in a
string. So, it will count the overall characters, including spaces and special
characters. Given below is an example of the Len function.
Fig: Len function in Excel
