Scribd
Upload
24 views38 pages

EXCEL LAB1

Uploaded by

22bca004
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
24 views38 pages

EXCEL LAB1

Uploaded by

22bca004
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 38

EXCEL LAB

1.IF FUNCTION
The Excel IF function runs a logical test and returns one value for a TRUE result,
and another for a FALSE result.
2.COUNTIF
The Excel COUNTIF function returns the count of cells in a range that meet a
single condition.
3.SUMIF

The Excel SUMIF function returns the sum of cells that meet a single condition.
4.AVERAGE
The AVERAGE function calculates the average of numbers provided
as arguments.
5.CONCAT
The Excel CONCAT function concatenates (joins) values supplied as references.

6.INDEX
The Excel INDEX function returns the value at a given location in a range or array.
7.MATCH
MATCH is an Excel function used to locate the position of a lookup value in a row,
column, or table.
8.UNIQUE
The Excel UNIQUE function returns a list of unique values in a list or range.
9.IFS
The Excel IFS function can run multiple tests and return a value corresponding to
the first TRUE result.
10.COUNTIFS
The Excel COUNTIFS function returns the count of cells in a range that meet one
or more conditions.
11.SUMIFS
The Excel SUMIFS function returns the sum of cells that meet multiple conditions,
referred to as criteria.

12.AVERAGEIFS
The Excel AVERAGEIFS function returns the average of cells that meet multiple
conditions, referred to as criteria.
13.VLOOKUP
The Excel VLOOKUP function is used to retrieve information from a table using a
lookup value. The lookup values must appear in the first column of the table, and
the information to retrieve is specified by column number.
14.XLOOKUP
The Excel XLOOKUP function is a modern and flexible replacement for older
functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports
approximate and exact matching, wildcards (* ?) for partial matches, and lookups
in vertical or horizontal ranges.
15.HLOOKUP
The Excel HLOOKUP function finds and retrieve a value from data in a horizontal
table. The "H" in HLOOKUP stands for "horizontal", and lookup values must
appear in the first row of the table, moving horizontally to the right.
16.COUNT
The Excel COUNT function returns a count of values that are numbers. Empty
cells and text values are ignored.
17.COUNTA
The Excel COUNTA function returns the count of cells that contain numbers, text,
logical values, error values, and empty text (""). COUNTA does not count empty
cells.
18.LEFT

The Excel LEFT function extracts a given number of characters from the left side
of a supplied text string. For example, =LEFT("apple",3) returns "app".
19.MID
The Excel MID function extracts a given number of characters from the middle of
a supplied text string based on the provided starting location. For example,
=MID("apple",2,3) returns "ppl".
20.RIGHT

The Excel RIGHT function extracts a given number of characters from


the right side of a supplied text string. For example, =RIGHT("apple",3) returns
"ple".
21.LEN
The Excel LEN function returns the length of a given text string as the number of
characters. LEN will also count characters in numbers.
22.SUBSTITUTE
The Excel SUBSTITUTE function replaces text in a given string by matching.
23.SEARCH
The Excel SEARCH function returns the location of one text string inside
another.
24.ISNUMBER
The Excel ISNUMBER function returns TRUE when a cell contains a number, and
FALSE if not.
25.TODAY
The Excel TODAY function returns the current date.
26.NOW
The Excel NOW function returns the current date and time.
27.YEAR
The Excel YEAR function returns the year component of a date as a 4-digit
number.
The default start year in excel is 1900.In case date is displayed wrongly format
cell into text after executing the function.

28.MONTH
The Excel MONTH function extracts the month from a given date as a number
between 1 and 12.
29.NETWORKDAYS
The Excel NETWORKDAYS function calculates the number of working days
between two dates. NETWORKDAYS automatically excludes weekends (Saturday
and Sunday) and can optionally exclude a list of holidays supplied as dates.
30.EOMONTH
The Excel EOMONTH function returns the last day of the month, n months in the
past or future. You can use EOMONTH to calculate expiration dates, due dates,
and other dates that need to land on the last day of a month. Use a positive value
for months to move forward in time, and a negative number to move back in time.
31.OFFSET
The Excel OFFSET function returns a reference to a range constructed with five
inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in
rows, (5) a width in columns.

32.CHOOSE
The Excel CHOOSE function returns a value from a list using a given position or
index. For example, =CHOOSE(2,"red","blue","green") returns "blue", since blue
is the 2nd value listed after the index number.
33.LET
The Excel LET function lets you define named variables in a formula.
34.MAX
The Excel MAX function returns the largest numeric value in the data provided.
35.SORT
The Excel SORT function sorts the contents of a range or array in ascending or
descending order.

36.SORTBY
The Excel SORTBY function sorts the contents of a range or array based on the
values from another range or array.
37.RANK
The Excel RANK function returns the rank of a numeric value when compared to a
list of other numeric values.

38.FILTER
The Excel FILTER function is used to extract matching values from data based on
one or more conditions.
39.FREQUENCY
The Excel FREQUENCY function returns a frequency distribution, which is a list
that shows the frequency of values at given intervals.
40.SEQUENCE
The Excel SEQUENCE function generates a list of sequential numbers in an
array.
41.RANDARRAY
The Excel RANDARRAY function generates an array of random numbers between
two values. The size or the array is specified by rows and columns arguments.
The generated values can be either decimals or whole numbers.
42.IFERROR
The Excel IFERROR function returns a custom result when a formula generates
an error, and a standard result when no error is detected.

You might also like