1 of 25

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

MS EXCEL FUNCTIONS

2 of 25

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.

3 of 25

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.

4 of 25

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.

5 of 25

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. 

6 of 25

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.

7 of 25

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.

8 of 25

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

9 of 25

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.

10 of 25

MODULUS�

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

11 of 25

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. 

12 of 25

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.

13 of 25

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. 

14 of 25

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. 

15 of 25

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.  

16 of 25

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.

17 of 25

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

18 of 25

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.

19 of 25

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.

20 of 25

DATEDIF�

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

21 of 25

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. 

22 of 25

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.

23 of 25

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. 

24 of 25

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. 

25 of 25

THANK

YOU