Microsoft Excel

Microsoft Excel is an office use application designed by Microsoft. It comes with the Office Suite with several other Microsoft applications, like as Word, PowerPoint, Access, Outlook, and OneNote, etc. It is supported in Windows as well as Mac operating system too.

Moreover, the Microsoft Excel is one of the most suitable spreadsheet programs that help us to store and represent the data in tabular form, manage and manipulate data, create optically logical charts, and more. Excel provides us the worksheet to create a new document in it. We can save the Excel file with .xls extension.

What is Formula?

In Microsoft Excel, the formula is used to define the relationship between the given variables as well. Generally, the formula involves symbols and various mathematical operators like as addition, subtraction, multiplication, and division. Formulas are used in enormous fields like science, education, research, finance, and various developing as well as developed fields. The purpose of the formula is to just find out the required solution to the problem, and based on the requirement, the formula is modified respectively.

Why make use of the formula for finding solutions?

There are several reasons why the formula is important for the calculations:

• Manual calculations are a time-consuming process and sometimes they lead to Error.
• The formula represents the complex relationship between the given variables and helps the user to understand the problem quickly and easily.
• Moreover, the formulas help us to predict the data and draw the best output for the data as well.
• Results are retrieved by making use of the formula that are accurate and precise.

Formulas in Microsoft Excel

By default, Microsoft Excel primarily provides various formulas to calculate the data. The reasons why formulas are used in Microsoft Excel are as follows:

• Repetitive mathematical tasks are done in Microsoft Excel. Hence every time doing manual calculations takes more time respectively.
• And to get the accuracy as well as the speed of the data, the formulas are used.
• The formula helps us to easily organize the data, which helps the user to study and go through the data easily.

How to enter formulas in Excel?

Now to type a formula in Microsoft Excel, the following steps need to be followed as well:

Step 1: First of all we are required to select a new cell, namely B1, to enter the formula as well.

Step 2: Now after that, we need to enter the formula in the cell which is preceded by the equal to sign (=) respectively.

• For example: If in case the mathematical operation is based on the addition, the formula will be like =SUM (A1, A2). Here A1 and A2 are the cell names, and the SUM function primarily adds two given variables.

Step 3: And by just pressing the enter button, the result will be displayed in the selected cell as well.

Excel Formulas

Microsoft Excel usually contains a wide range of formulas for each mathematical operation. The formulas primarily return the required result for the data and the result even though contains an Error respectively.

And the variety of Microsoft Excel formulas is listed as follows:

Addition

The addition is considered as one of the most important arithmetic operations that can be used to sum or count the given values. The symbol for the addition is plus (+) sign.

Moreover, the various formulas that are used for the addition in Microsoft Excel are as follows:

1. By making use of the '+' operator

By making use of the '+' operator, the values that are present in the worksheet are added in Microsoft Excel

• For example: If the cells A1 and B1 contain the data, which needs to be summed, we need to enter the formula in the new cell, namely C1 as =A1+B1. And by just pressing the Enter button, the result will be getting displayed in cell C1 respectively.

SUM Function

And now the SUM function in Microsoft Excel is used to sum or count the specified cell or cell range respectively. The formula would be =SUM (A1, B1) to count the data in the specified cell. And to count the data for the specified range, the formula would be =SUM (A1:A5).

1.1 Sum function with multiple ranges

In this, if the requirement is to just add out the multiple data ranges, the formula would be modified as =SUM (A1:A5, B1:B5, C1:C5). Here A1:A5, B1:B5, and C1:C5 are cell ranges.

• For example: If we want to sum out the data that are present in the cell range A1:A5, B1:B5 we need to enter the formula in the new cell C1 as =SUM (A1:A5, B1:B5), after that we need to press out the Enter button as well. The sum of the data will be displayed in the selected cell respectively.

2. 2 Sum function with Conditions

If the requirement is to just add the given data that are based upon the conditions, the SUM function is modified which are based on the conditions respectively.

• For example: To add the specified data range namely A1:A5 which is greater than the number 5, the formula to be entered is =SUMIF (A1:A5,">5") in the new cell B1, and then we will be pressing out the Enter button respectively. The sum of values greater than five is displayed in cell B1.

Similarly, the syntax will get varied for the conditions like lesser than, not equal to, equal to, etc.

Subtraction

Subtraction is considered one of the other most important arithmetic operations that can be used to find out the difference between the given values and it is the inverse of addition. The symbol for subtraction is plus (-) sign.

1. By making use of the '-' operator

By making use of the '-' operator, the values which are present in the worksheet are subtracted in the Microsoft Excel Sheet.

• For example: If cell A1 contains the value five and B1 contains the data 4, where B1 needs to be subtracted with A1, and we need to enter the formula in the new cell, which is named as C1 as =A1-B1, and pressing out the Enter button will display the result in cell C1 as 1.

2. Sum function for Negative Values

The Sum function is used to subtract out the range of values that are present in the given cell as well.

• For example: If the worksheet contains the data of the cell range B1:B5 which needs to be subtracted from the value present in cell A1, the formula would be, =A1-SUM (B1:B5) which needs to be entered in cell C1. After that, we need to press the "Enter" button. The value will get displayed in cell C1 respectively.

Multiplication

Multiplication is termed to be the other most important type of arithmetic operations that multiply two numbers to get out the product. The term involved in multiplication is multiplicand and multiplier as well. The symbol represents multiplication,

Asterisk (*)

Dot (.)

Multiply symbol (x)

1. By making use of the asterisk Symbol

The asterisk symbol in Microsoft Excel is used to multiply the given values, and the symbol is represented by the (*) sign effectively.

• For example: If the particular cells A1 and B1 contain the value that must be multiplied, then the formula will be written as =A1*B1 and need to be entered in cell C1, after that we need to press the "Enter" button as well. The multiplication result will be displayed in cell C1 respectively.

2. By making use of the Product Function

As the name implies, the Product function is used to multiply two given values.

• For example, Cell A1 and B1 contain the value which needs to be get multiplied to write down the formula as =PRODUCT (A1, B1) in cell C1, just after that we are required to press the "Enter" button as well. The product of the value will be displayed in cell C1.
  
• For example: To multiply the specified given range, the formula will be modified as =PRODUCT (A1:A5), which needs to be entered in the new cell B1, just after that we need to press the "Enter" button as well. The result will be displayed in cell B1 which is the product of the range A1:A5.
  
• For example, The formula will be then modified to multiply the given specified range with a particular number as =PRODUCT (A1:A5)*B1. Here A1:A5 is the cell range, and B1 is the specified number and is entered as value 10 respectively in cell C1, we need to enter the formula and press the "Enter" button as well. The result will be displayed in the selected cell.
  

Division

The division is one of the other important arithmetic operations which divide the specified value into equal parts, and it is the inverse of multiplication. The symbol denotes it,

Horizontal fraction bar (/).

Division symbol (÷).

1. By making use of the '/' operator

The '/' operator is primarily used to divide out the specified values.

• For example: If the value present in cell A1 is called 10, then it needs to be divided with cell B1 called 5, the formula would be written as =A1/B1 in the new cell C1, after that we need to press out the "Enter" button. The result will be displayed in cell C1 as well.

2. By making use of the Quotient Function

The quotient function returns the quotient of the dividing values which is an integer.

• For example: If the value that is present in cell A1 needs to be divided with cell B1, the formula would be written as =QUOTIENT (A1, B1) in cell C1.The quotient function displays the integer part of the division as well, after that, we need to press the "Enter" button as well. The quotient for the given data will be displayed in cell C1 as well.

Average

The average function is purposely used to find out the mean of the given numbers, and the average function is calculated by dividing the total value by the total Count of numbers in the data.

The formula that is used to calculate the Average Function in Microsoft Excel is as follows: =AVERAGE (A1, B1, and C1). Here A1, B1, and C1 are the name of the cell.

• For example: Now to calculate out the average for three cells, namely A1, B1, and C1 enter the formula in the new cell D1 as =AVERAGE (A1, B1, C1), and after that, we need to press out the "Enter" button, the function returns the average for the specified values respectively.

Count

The Count function counts the number of data present in the specified range.

• For example: If the cell ranging from A1:A5 contains the values, that need to be counted, the formula will be written as =COUNT (A1:A5) in cell B1. Here A1:A5 is called cell range, and after that, we are required to "Enter" button the result will be displayed in cell B1 respectively.

The COUNT function counts the cells which contain only the numerical values.

1. COUNTA

If the data contains a mixture of the text as well as the alphabet, the COUNTA function counts all the cells containing the data. It does not count the blank cells.

The syntax used is none other than: =COUNTA (A1, B1)

• For example: If cells A1 and B1 need to be counted, then the result will be =COUNTA (A1, B1) is entered in new cell C1, after that we need to press the "Enter" button. The result will be displayed in cell C1 respectively.

2. Countblank ()

The count blank () function in Microsoft Excel is used to count out the blank cell in the data, and if in case the data contains a mixture of the blank cells, then the count blank () function displays the number of the blank cells in the given data as well.

The syntax used is,

=COUNTBLANK (range)

Here A1:A5 is called the cell range.

• For example: If cell A1:A5 needs to be counted for the blank cells, then we can make use of the formula that is none other than=COUNTBLANK (A1:A5) and is entered in the new cell B1, after that we need to press the "Enter" button as well. The number of blank cells will get displayed in cell B1 efficiently.

3. Count if ()

And the Count if () is an in-built function in Microsoft Excel that is used to count out the cells in the given range which are based upon certain conditions or criteria as well.

The syntax for Count if the () condition is:

=COUNTIF (range, criteria)

In which,

• Range- It primarily defines the cells in the particular range that need to be counted respectively.
• Criteria- These are based upon these criteria the cells are counted respectively.
• For example: If the cell A1:A5, needs to be counted and whose value is greater than 5, then the formula will be = Count if (A1:A5,">5") is entered in new cell B1, after that we need to press "Enter" button, and the Count of cells that meet the specified criteria will be displayed in cell B1 respectively.

4. Counties

The Countif function counts the range of the cells which are based upon the multiple criteria.

The syntax for the Countif function is:

=COUNTIFS (range1, critera1, range 2, criteria 2,..)

In which,

• Range 1, Range 2- The range needs to be evaluated in the given data.
• Criteria 1, Criteria 2- The respective cells are counted in the data that are based upon the given criteria.

And the Countifs function can accept around 127 range or criteria respectively.

• For example: If the cell A1:A5 which needs to be counted whose value is equal to APPLE, and cell range id ranging from B1:B5 is counted whose value is equal to "A" then the formula would be = Count if (A1:A5, " an apple," B1:B5, "A") and it will be entered in new cell C1 just after that we need to press "Enter" button, so the Count of cells that meet the specified criteria will get displayed in cell C1 respectively.

MODULUS

Now the Modulus function in Microsoft Excel is used to return out the remainder of the dividing values, and the terms which are involved in the division are divisor and dividend as well. The MOD represents the Modulus function.

The syntax to perform the Modulus function is as follows,

=MOD (A1, B1)

Here A1 and B1 is the cell name.

And in the formula either the dividend is specified directly or by cell name.

• For example: To enter out the dividend directly, the formula will be modified as =MOD (A1, 4) in new cell B1, and after that we are required to press the "Enter" button as well, and the MOD function will then returns the remainder in cell B1 respectively.

Here A1 is the divisor, and the value 4 is the dividend.

And both the divisors, as well as the dividend, are entered directly in the formula as,

=MOD (25, 4).

Power

The power function in Excel raises the given number to the specified power value as well, and the formula that can be used to find out the power of the given value in Excel is as follows:

=POWER (A1, B1)

Here A1, B1 is called the cell value.

• For example: If the particular cell A1 contains value 5, and cell B1 contains value 4, then in that case we need to enter the formula as =POWER (5, 4) in new cell C1, after that we are required to press "Enter" button as well so the result will be displayed in cell C1 respectively.

And the power value is entered directly or referred to in the cell name. To directly enter the power, the syntax will be:

=POWER (A1, 5).

The formula A1 contains the value raised to the power of 5.

CEILING

The ceiling function in Microsoft Excel is primarily used to round a number to the nearest multiple of the specified number, and the syntax of the Ceiling function is none other than the: =CEILING (number, significance).

The number usually defines the value rounded, and significance is the multiple to which we want to round as well.

• For example, CEILING (11, 5) means the result is 15, the nearest multiple of 5.

The CEILING function rounds the number, so if it is already the multiple of the significance, it returns the number without any change.

CEILING (10, 5) returns 10, as 10 is already a multiple of 5.

FLOOR

The FLOOR function works opposite to the CEILING function. It rounds a number down to the nearest multiple of the specified number, and the syntax of the FLOOR is,

=FLOOR (number, significance)

The number primarily defines the value which is rounded, and significance is the multiple to which we want to round respectively.

• For example: FLOOR (15.6, 5) means that the result is 15 which is the number down to the nearest multiple of the 5.

The FLOOR function usually rounds the number, so if it is already the multiple of the significance, it returns the number without any change as well.

FLOOR (15, 5) returns 15, as 15 is already a multiple of 5 respectively.

CONCATENATE

Concatenation is joining or the combining of two or more strings or characters.

• For example: If A1 contains the data HELLO and B1 contains the data GOOGLE, then the syntax will be like this,
• =CONCATENATE (A1," ", B1) which is entered in cell C1. After that, we need to press the "Enter" button as well. The result will be HELLO GOOGLE.

Another, Syntax for CONCATENATE function is none other than the =CONCATENATE (A1&" "&B1)

LEN

As the name suggests the particular LEN function returns the total length of the characters in the given specified cell, and it counts the special characters as well as the spaces respectively.

• For example: If in case of cell A1 contains the characters called GOOGLE, to find out the length of the characters, then the syntax will be =LEN (A1) entered in new cell B1 and just after that we need to press "Enter" button and the result will get displayed in the cell B1.

The LEN function displays the result as six, and the Count of the total characters in cell A1 respectively.

REPLACE

As the name implies that the REPLACE function which replaces the specified text string with another one.

The syntax for the REPLACE function will be like this:

=REPLACE (old_text, start_num,num_chars,new_text)

Here start_num refers to the characters' index position that needs to be replaced.

num_chars defines the number of characters that need to be replaced as well.

• For example: If cell A1 contains the data A101, which needs to be replaced with the data B101, then the formula would be written as =REPLACE (A1, 1, 1, "B") in new cell B1 and just after that we are required to press "Enter" button and the result will get displayed in cell B1 respectively.

Next, if cell A1 contains the name Vijay which needs to be replaced with Ajay, then the formula will be written as =REPLACE (A1, 1, 2, "A").

SUBSTITUTE

And the SUBSTITUTE function replaces the existing text with the given new text.

The syntax for the SUBSTITUTE function is as follows,

=SUBSTITUTE (text, old_text, new_text,[instance_num])

• For example: If cell A1 contains the data "Hello Google" that needs to be replaced with "Hello World", the syntax will be like, =SUBSTITUTE (A1, "Google", "World") in the given cell B1, and after that we required to press "Enter" button as well and the result will be "Hello World."

Besides all this, the [instance_num] refers to the index position of the present text more than once as well.

And if the cell contains text strings that are repeated more than once, the position of the text wants to be mentioned in the given formula as well.

• For example: If cell A1 contains the text string " MS Excel 2007, MS Word 2007", the year 2007, which is present in the second position, needs to be replaced with the year 2023, and the syntax of the formula will be,

=SUBSTITUTE (A1, 2007, 2023, 2)

And the output will be displayed as follows, "MS EXCEL 2007, MS Word 2023" The second position of 2007 is replaced with 2023.

In the example of substituting both the year with the same year, then the formula will be written as:

=SUBSTITUTE (A1, 2007, 2023)

The result will be "MS EXCEL 2023, MS Word 2023".

LEFT, RIGHT, MID

LEFT Function

Now the LEFT function in Microsoft Excel usually returns the text which is left of the starting position of the given specified text as well.

• For example: If the given cell A1 contains the text string "Google is a search engine" to display the text "Google", the formula will be written as =LEFT (A1, 6) in new cell B1, and just after that we need to press "Enter" button. The LEN function extracts the character on the left of the starting position. Here A1 represents the cell name. As Google contains six characters, it is written in the formula, so the LEFT function displays out the text left of the 6th position character respectively.

MID Function

The MID function in Microsoft Excel is purposely used to extract the specific text from the cell.

The syntax of the MID function is as:

=MID (text,start_num,num_chars)

In the formula,

The text primarily indicates the specified text in the cell where the particular text is extracted.

Start_num usually represents the starting position of the specified text as well.

Num_chars represents the number of characters extracted from the specified text.

• For example:
  If cell A1 contains the "Hello Google" data, to extract the word Google from it, the formula will be written as =MID (A1, 7, 6) in new cell B1, and then just after that we need to press the "Enter" button as well. The MID function primarily extracts the character which is present in the mid of the starting position. The result will be Google respectively.

Right Function

The Right function in Microsoft Excel extracts the specified number of characters from the right side of the text string as well.

The syntax is as follows:

=RIGHT (text,[num_chars])

The text represents the cell where the specified characters are extracted. In which the Num_chars represents the number of the characters that need to be extracted as well. This argument is an optional one, and if this argument is not mentioned, then the RIGHT function extracts one of the characters from the right of the string respectively.

• For example: If cell A1 contains the data "Hello World", to extract the text World then the syntax will be =RIGHT (A1, 6) in cell B1, and just after that we need to press the "Enter" Button and the RIGHT Function primarily extracts out the character that is present on the right starting position. The result will be World.

Here the RIGHT function displays the result as World respectively.

Upper, Lower, and Proper Case

Upper

As the name indicates, the Upper function converts all the specified text into Upper Case. And the syntax for the function is,

=UPPER (text)

• For example: If cell A1 contains the data, "Google" to change the word to uppercase, then the formula will be, =UPPER (A1) is entered in a new cell, namely B1, and just after that we need to press "Enter" Button and the result will be GOOGLE.

Lower

Now the Lower function in Microsoft Excel is used to convert all the specified text into Lower Case as well.

The syntax for the function is,

=LOWER (text)

• For example: If cell A1 contains the data "GOOGLE" to change the word to lowercase, the formula will be =LOWER (A1) entered in cell B1, and just after that we need to press the "Enter" button and the result will be Google.

Proper Case

It was well known that, the respective proper case function in Microsoft Excel is used to convert the first letter of the specified text into a capital letter and the remaining text into lowercase as well.

The syntax of the proper case,

=PROPER (text)

• For example: If a particular cell contains the data "GOOGLE" and to convert the data into the proper case, then the syntax will be =PROPER (A1) that needs to be entered in new cell B1, and just after that we will be pressing the "Enter" Button and the outcome will " Google".

NOW ()

And the NOW () function displays the system's current date as well as the time.

The syntax for the NOW () function is,

=NOW ()

Enter the syntax in any of the cells and just after that press the "Enter" Button as well. The current date and time of the system will be displayed respectively.

TODAY ()

And the TODAY () function in Microsoft Excel is used to display the system's current date.

The syntax for the TODAY () function is,

=TODAY ()

Now we need to enter the syntax in any of the cells and just after that, we need to press the "Enter" button. The current date of the system will be displayed as well.

DAY ()

The DAY () function primarily returns the day of the Month. A month comprises the day from 1 to 31. 1 denotes the first day of the Month, while 31 denotes the last day of the month.

• For example: To display today's day, we need to enter the syntax as:

=DAY (TODAY ())

In any of the cells, the result will be today's date respectively.

MONTH ()

As the name implies that the MONTH () function is used to return the Month of the year. The Month comprises 1 to 12, 1 in January, and 12 in December.

• For example: To display the current Month (), we need to enter the syntax as:

=MONTH (TODAY ())

In any of the cells, the result will be the current Month.

YEAR ()

As the name implies, the YEAR () function returns the Year from the date value effectively.

• For example: To display the current Year, we need to enter the syntax as:

=YEAR (TODAY ()) in any of the cells, and the result will be the current year, 2023.

TIME ()

The TIME () function in Microsoft Excel is used to convert the specified hours, minutes, and seconds into time format, which are entered as Excel Serial Number.

• For example: We need to enter specified hours, minutes, and second in the formula as,=TIME (20, 40, 20)

The result will be displayed at 8:40 P.M respectively.

HOUR, MINUTE, SECOND

The Hour function primarily displays the hour value from the current time starting from 0 to 23, and here 0 indicates 12 AM, and 23 indicates 11 PM.

• For example: To display the current hour, we need to enter the syntax in the new cell as =HOUR (NOW ()), and just after that we need to press the "Enter" button as well, and the Hour Function will return the current hour of the day respectively.

Minute ()

The Minute () function is used to return the minute from a time value where the number ranges from 0 to 59.

For example: To display the current minute, we need to enter the syntax in the new cell as =MINUTE (NOW ()), and just after that we need to press the "Enter" Button and the Minute Function will return the current minute of the hour.

Second ()

The Second () function in Microsoft Excel is used to return the second from a time value where the number ranges from 0 to 59 respectively.

• For example: To display the second, we need to enter the syntax in the new cell as =SECOND (NOW ()), and just after that we need to press the "Enter" Button. The Second Function returns the second from a current time efficiently.

Dated if ()

The Dated if the () function returns out the difference between two dates in terms of the years, days, or months.

• For example: To calculate a person's age, the two required dates are entered in cells A2 and B2. A1 contains the person's date of birth, namely 1-1-2014, and B1 contains today' date, such as 5-6-2016

And if we want to calculate the age of a person, we need to enter the syntax as =DATEDIF (A2, B 2, "y") and just after that we need to press the "ENTER" Button. The result will be displayed as 2, which is a person's age.

VLOOKUP ()

As the name implies, the VLOOUP () function is called the "Vertical Lookup" and looks for the specified data that are present in the table's leftmost column. It returns the value present in the specified row as well as the column.

The syntax for the VLOOKUP function is,

=VLOOKUP(lookup_value, table_array,col_index_num,[range_lookup])

• lookup_value - The lookup_value indicates the value which needs to be searched in the leftmost column of the table.
• table_array- This table_array or function is used to find out the specified value in that range that needs to be retrieved as well.
• Col_index_num - The col_index_num represents the column number where the particular value is present.
• Range_lookup - This is an optional value, either "True" or "False". If the value is "True" or "blank", it looks for the appropriate value in the table. If the value "False" is entered, it looks for an exact match in the table.

HLOOKUP

As the name implies, HLOOKUP stands for Horizontal Lookup, which looks at the value in the top row of a table. It displays the value in the same column from a row that the user specifies.

The syntax for the HLOOKUP function is as follows:

=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

• lookup_value - The lookup_value primarily indicates the value that must be needed to be searched in the first row of the table.
• table_array- This table_array or function is used to find out the specified value in that range that needs to be retrieved as well.
• row_index_num - The row_index_num represents the row number where the particular value is present.
• Range_lookup - This is an optional value, either "True" or "False". If the value is "True" or "blank", it looks for the appropriate value in the table. If the value "False" is entered, it looks for an exact match in the table respectively.

IF Function

The IF is a logical function that will return the particular value if the given condition is TRUE, and if the given condition is FALSE, and then it will return another value.

The syntax for the IF function is:

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

• Logical_text- The condition that needs to be checked
• Value_if_true- If the given condition is true, this value is returned
• Value_if_false- If the given condition is false, this value is returned.

IF ERROR

The IF ERROR is one of the normal functions in Microsoft Excel, where it handles Errors in the formula. If the formula contains an error, it returns an expression or a value of the expression.

The syntax of the IFERROR is:

=IFERROR (value, value_if_error)

• Value- The formula or value which needs to be evaluated for errors.
• Value_if_error- This value is returned if the formula returns an error value.

The value_if_error is optional. If this option is not included in the formula, it returns a default error message. If it is included, it displays a message about what the user has entered in the formula.

TRIM

As the name suggests, the TRIM function removes the extra space in the cell. It removes the space from the start and end of the text, whereas it doesn't remove the space between the texts respectively.

The syntax for the TRIM function is,

=TRIM (text)

• Text- Text which needs to be trimmed out.

MAX and MIN

The MAX function in Microsoft Excel is used to find the maximum number in the given range.

The syntax for the MAX function,

=MAX (number1, [number 2],...)

Either the number or ranges can be entered in the formula.

The MIN function usually finds out the minimum number in the given range as well.

The syntax for the MIN function,

=MIN (number1, [number 2],...)

FIND

The FIND function finds out the position of the specified text string which is present inside another text string, and it will return the position of the text string if the formula is correct, or it returns the #VALUE Error.

The syntax for the FIND formula,

=FIND (find_text, within_text,[start_num])

• Find_text- The text that needs to be searched.
• Within_text- The text to search within.
• Start_num- It is an optional one that indicates the starting position of the number.

Sumproduct

The Sumproduct function multiplies out the elements in the ranges or arrays, will add the products, and returns the result in a single element as well.

The syntax for the Sumproduct is:

=SUMPRODUCT (array 1,[array 2],[array 3])

Unique

The unique function is used to return the unique values that are present in the list. Values include text, alphabet, time, date, etc.

The syntax for the unique function is:

=UNIQUE (array,[by_col],[exactly_once])

Sort

The Sort function in Microsoft Excel is used to return the selected data in ascending or descending order.

The syntax for the Sort function is:

=SORT (array, [sort_index], [sort_order], [by_col])

ISODD and ISEVEN

The ISODD function returns the result as TRUE if the value is ODD otherwise it will return the result as FALSE if the value is EVEN. It returns the #VALUE error if the given value is not numeric respectively.

The syntax for the ISODD function:

=ISODD (value)

And the ISEVEN function returns the result as TRUE if the value is EVEN. It returns the result as FALSE if the value is ODD, otherwise, it will be returning the #VALUE error if the given value is not numeric as well.

The syntax for the ISEVEN function:

=ISEVEN (value)

TEXT BEFORE

The TEXTBEFORE function in Microsoft Excel is used to return the text which occurs before the given data. And the syntax for the TEXTBEFORE function is:

=TEXTBEFORE (text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

EXACT and MATCH

The match function returns the position of the lookup value where it is present in a row, column, or table respectively.

The syntax for the MATCH function is:

=MATCH (lookup_value, lookup_array, [match_type])

As the name suggests that the EXACT function usually returns the result as TRUE if the compared string is exactly matched, and it will return the result as FALSE if the compared string is not matched. The exact function is case-sensitive as well.

The syntax for the EXACT function is:

=EXACT (text 1, text 2)

REPT function

The REPT function repeats the given character a specified number of times.

The syntax for the REPT function is:

=REPT (text, number times)

Transpose

The transpose function primarily changes the orientation of the given array or the range in the given table. If the given range is horizontal, then the range is converted to vertical. And if in case the given range is vertical, then it will be get converted to a horizontal range as well.

Syntax of the transpose function is:

=TRANSPOSE (array)

IS NUMBER

The ISNUMBER in Microsoft Excel returns the result as TRUE if a cell contains the number and it will return the FALSE if not.

The syntax for the ISNUMBER is,

=ISNUMBER (value)

Drop

The syntax of the Drop function is:

=DROP (array,[rows],[col])

ABS function

The ABS function is termed the "absolute function" which returns the absolute value of the given data respectively. It returns a positive number if the specified value is a negative number.

And the syntax for the ABS function is:

=ABS (number)

Summary

The formula usually plays an important role in the calculation, and in this tutorial, some of the formulas are listed, that are used in Microsoft Excel for calculating the data. By default, it contains enormous formulas based on the data.