MS Excel Functions: Frequently Used Mathematical and Text Formulas in Excel

Formula is one of the most important features of Excel, which allow you to perform different kind of operations in its cell content. It helps when you are stuck on data and need to get done your task quickly. Microsoft Excel offers over 300 functions to let you do much more in your spreadsheet once you master them. In this article; you will learn about frequently used formulas in Mathematical and Text function categories.

Formulas: A formula is an expression that calculates the data you have entered into the cell.
Functions: Functions are predefined formulas in Excel to perform long and complex operations.

Mathematical Functions in Excel:-
Mathematical functions in Excel are used to perform common arithmetic operations in cell such as addition, subtraction, average, multiplication, and rounding numbers, etc.

SUM Formula: It is used to add values in the cell.
Syntax: =SUM(Number1, Number2, …..)

Example:

=SUM(A1:A9) – It will return the sum of values in the A column from 4 to 2 and result will be 52.
=SUM(A3,B6,C9) or =SUM(A3+B6+C9) – It will return the sum of values 6, 4 and 2, and result will be 12.
=SUM(A1:A9,B4:B9) – It will return the sum of values in A1 to A9 ( 4 to 2) plus the sum of values in the cell B4 to B9 (4 to 7), result will be 78.

AVERAGE Formula: It returns the average of numbers provided in the cell
Syntax: =AVERAGE(number1,[number2], ... [number_n])

Example:

=AVERAGE(B1,B2,B3) – Will return the average of numbers 10, 12 and 8 and result will be 10.
=AVERAGE(B1:B6) – Will return the average of numbers from  10 to 14 and result will be 12.5.
=AVERAGE(B1:B3,B6) – Will return the average of numbers from 10 to 8 and 14 and result will be 11.

INT Formula: Int formula returns the integer portion of the number.
Syntax: =INT(expression)

Examples:
=INT(4.51) – It will return 4.
=INT(230.98) – It will return 230.
=INT(-4.8) – It will return -5.
=INT(-6.453) – It will return -7.

Note – If the expression is negative number then INT formula will return first negative number that is less than or equal to expression.

ROUND Formula: This formula is used to rounds a number to its nearest specified decimal.
Syntax: =ROUND(number, num_digits)

Example:

=ROUND(4.15, 1) – It will rounds 4.15 to one decimal place and result will be 4.2.
=ROUND(4.1583, 2) – It will rounds 4.1538 to two decimal place and result will be 4.16.

Texts Functions in Microsoft Excel: -
Texts functions in Excel are used to perform various text operations between cells. Some of the mostly used operations are concatenating text values, substituting some texts, repeating a particular text, counting the length of texts, etc.

REPT Formula: This formula is used to repeat a particular text for a given number of times.
Syntax: =REPT(text, number_times)

In above formula text specify the text you want to repeat and number_times specifies the number of times you want to repeat the text.

Example:
=REPT(water, 3) – It will display water 3 times and result will be (waterwaterwater).
=REPT(l, 5) – It will display l for 5 times and result will be lllll).

If number_times is zero then it will return empty text and if it not an integer then it is truncated.

CONCATENATE Formula: This formula is used to combine multiple text strings into one string. It can join up to 255 text strings into one string.
Syntax: =CONCATENATE(text1, text2, text3,.....)

Example:
If you value One in cell A1, value Man in cell b1 and value Army in cell C1 then you can combine these cell values by using this formula.
=CONCATENATE(A1, " ", B1, " ", C1)

It will display result “One Man Army”. In this example (" ") is used to make a space between texts.

You can also use ampersand (&) operator instead of CONCATENATE formula.

Example:
=A1 & " " & B1 & " " & C1
It will also display the same result “One Man Army”.

LEN Formula: This formula is used to find the number of characters in a text string. 
Syntax: =LEN(text)

Example:
=LEN(A1) – Length of the string in A1.
=LEN(water) – It will display result 5.
=LEN(cold water) – It will display result 10. This formula counts spaces as character also.

TRIM Formula: This formula removes all unnecessary spaces from the given text strings.
Syntax: =TRIM(text)

Example:
=TRIM(“most popular    functions        in    excel”) - It will display result “most popular functions in excel”.

FIND Formula: This formula is used to find the location of one string within a second string.
Syntax: =FIND(find_text, within_text)

Example:
=FIND(“M”,“Microsoft”) – It will show the position of M in Microsoft and result will be 1.
=FIND(“o”,”Microsoft”) - It will show result 5. This formula finds the location of first text string from the starting of the second text string.
=FIND(“O”,”Microsoft”) - It will show an error as this formula is case sensitive.

Common Excel Formula and Function Errors:-
When you apply a wrong formula in the cell then excel shows you a error message. Here is the list of some errors with description that will help you to correct your formula.

Error        Meaning

#VALUE!    It indicates that you give parameters in the formula is not correct data type.

#DIV/0!    It indicates that you are attempting to divide a number by zero.

#NULL!    It indicates that you have not mentioned the correct range operators.

#NAME?    You have misspelled a formula or its range.

#NUM!    A formula has invalid numeric data for the type of operation.

#REF!        It denotes the invalid range for the parameters.