Submitted by:�Dr.(Mrs.) Sangeeta Arora�(Dept. of computer Science and IT)

MS EXCEL FUNCTIONS

MS EXCEL

• Microsoft Excel enables users to format, organize and calculate data in a spreadsheet.
• By organizing data using software like Excel, data analysts and other users can make information easier to view as data is added or changed.
• Excel contains a large number of boxes called cells that are ordered in rows and columns.

What is Excel Formula? �

• In Microsoft Excel, a formula is an expression that operates on values in a range of cells.
• These formulas return a result, even when it is an error.
• Excel formulas enable you to perform calculations such as addition, subtraction, multiplication, and division.

​

Formulas in Excel�

• Choose a cell.
• To enter an equal sign, click the cell and type =.
• Enter the address of a cell in the selected cell or select a cell from the list.
• You need to enter an operator.
• Enter the address of the next cell in the selected cell.
• Press Enter.

�

Excel formulas and functions

• There are plenty of Excel formulas and functions depending on what kind of operation you want to perform on the dataset.
• There are functions on mathematical operations, character-text functions, data and time, sumif-countif, and few lookup functions. 

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.

​

AVERAGE�

• The AVERAGE() function focuses on calculating the average of the selected range of cell values.
• It automatically calculates the average, and you can store the result in your desired location.

​

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. �

SUBTOTAL

• 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.

MODULUS�

• The MOD() function works on returning the remainder when a particular number is divided by a divisor

​

POWER and CEILING

•  POWER
• The function “Power()” returns the result of a number raised to a certain power.

​

​

• CEILING
• Next, we have the ceiling function. The CEILING() function rounds a number up to its nearest multiple of significance. 

​

FLOOR and CONCATENATE

• FLOOR
• Contrary to the Ceiling function, the floor function rounds a number down to the nearest multiple of significance.

​

• CONCATENATE
• This function merges or joins several text strings into one text string.

​

LENGTH and REPLACE

• 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.
• REPLACE
• The REPLACE() function works on replacing the part of a text string with a different text string. 

SUBSTITUTE�

• The SUBSTITUTE() function replaces the existing text with a new text in a text string. 
• The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”.
• Here, [instance_num] refers to the index position of the present texts more than once. 

LEFT, RIGHT, MID�

• The LEFT() function gives the number of characters from the start of a text string.
• The MID() function returns the characters from the middle of a text string, given a starting position and length.
• The right() function returns the number of characters from the end of a text string.  

​

UPPER, LOWER, PROPER�

• The UPPER() function converts any text string to uppercase.
• The LOWER() function converts any text string to lowercase.
• The PROPER() function converts any text string to proper case, i.e., the first letter in each word will be in uppercase, and all the other will be in lowercase.

​

NOW and TODAY

​

• The NOW() function in Excel gives the current system date and time.

​

​

• The TODAY() function in Excel provides the current system date

​

TIME()�

• The TIME() function converts hours, minutes, seconds given as numbers to an Excel serial number, formatted with a time format.
• HOUR, MINUTE, SECOND
• The HOUR() function generates the hour from a time value as a number from 0 to 23. Here, 0 means 12 AM and 23 is 11 PM.

​

​

​

​

​

​

​

​

MINUTE AND SECONDS

• The function MINUTE(), returns the minute from a time value as a number from 0 to 59.

​

• The SECOND() function returns the second from a time value as a number from 0 to 59.

DATEDIF�

• The DATEDIF() function provides the difference between two dates in terms of years, months, or days.

​

VLOOKUP�

• Next up in this article is the VLOOKUP() function. This stands for the vertical lookup that is responsible for looking for a particular value in the leftmost column of a table. It then returns a value in the same row from a column you specify. 

​

HLOOKUP�

• Similar to VLOOKUP, we have another function called HLOOKUP() or horizontal lookup. The function HLOOKUP looks for a value in the top row of a table or array of benefits. It gives the value in the same column from a row you specify.

IF Formula and COUNTIF�

• The IF() function checks a given condition and returns a particular value if it is TRUE.
• It will return another value if the condition is FALSE.
• The function COUNTIF() is used to count the total number of cells within a range that meet the given condition. 

​

SUMIF and IF-ERROR�

• The SUMIF() function adds the cells specified by a given condition or criteria.
• IF-ERROR
• The Excel IFERROR function returns an alternative result when a formula generates an error and an expected result when no error is detected. 

​

​

THANK

YOU