Scribd
Upload
1 views

Excel Formulas

Uploaded by

Mansimar Kaur
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
1 views

Excel Formulas

Uploaded by

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

EXCEL FORMULAS

 SUM
 AVERAGE
 MAX
 MIN
 INT – Returns the integer part of the numerical value, removing the decimal value
(truncates the decimal part). Is NOT equivalent to rounding off.
 ROUND:
Syntax: value, decimal places
Where decimal places can be: 1(1dp), 2(2dp), 0(whole no.), -1(nearest 10), -2(nearest
100).
 ROUNDUP
Same syntax as ROUND
 ROUND DOWN
 PI() : To input value of pi
 COUNT
 COUNTA: Counts ALL the values including strings
 IF
 AND: ensures all conditions are true
 OR: displays result if one of the conditions is true
 NOT: displays result if the conditions are not true
 IFS: looks at a number of different conditions and performs different operations if any
one of them is met. If none of the conditions are true, an error message is returned.
EG. =IFS(B2="Alpha","London",B2="Beta","Milan",B2="Gamma", "Tunis")
The highlighted areas are the output to be displayed if the condition is met
 ISERROR: used to detect if an error will occur when a formula is used, and returns True
if there is an error or False otherwise.
 IFERROR: Allows error trapping, i.e., allows a value to be displayed if an error is found.
=IFERROR(IFS(B2="Alpha","London",B2="Beta","Milan",B2 ="Gamma","Tunis"),"Project location unknown")
If any error is found in the purple formula, output will be given.

 COUNTIF: Used when counting is required with a single condition


 COUNTIFS: Used for conditional counting with two or more conditions.
Only counts the cells that meet all the conditions specified.

 SUMIF
 SUMIFS
Perform calculation only if all the specified
 AVERAGEIF conditions are true.

 AVERAGEIFS
 MAXIFS
 MEDIAN
 MODE
 SUBTOTAL: Allows a number of functions to be used in one cell.
 LOOKUP
 HLOOKUP
 VLOOKUP
 XLOOKUP
 INDEX: This function returns values from a given location in a table, where the user
specifies the row and column position of the item in the table.
 MATCH: This function searches for a specified item in a range of cells and returns the
relative position of that item in the range.
Returns the row and column numbers individually, when a column or row in selected respectively.
 CONCATENATE: Used to join strings
 RIGHT- Extracts a number of characters from the right side
 LEFT- Extracts a number of characters from the left side
 MID- Extracts a number of characters from the middle of a string
EG: =MID(A2,3,1), which extracts from the contents of cell A2, starts at the third character, and
extracts just a single character
 LEN- Counts the number of characters within a string
 FIND- This function returns a numeric value that represents the position of a character
or substring within a string
 ISTEXT
 ISNONTEXT
 ISNUMBER
 UPPER
 LOWER
 EXACT- Checks if two strings are exactly the same, including case
 CODE- Returns the ASCII numeric code for any character
 CHAR- Returns the character for any ASCII for any numeric code
 DEC2BIN
 DEC2HEX
 BIN2DEC
 BIN2HEX
 HEX2BIN
 HEX2DEC
 WEEKDAY- Returns a number between 1 and 7 from a given date. If the day is a
Sunday, it returns 1, and Monday is a 2.
 DAY- Used to return the day part of a day
 MONTH- Used to return the month part of a date, from 1 to 12
 YEAR- Used to return the year part of a date
 DATEDIF- Calculates the difference between two dates and displays the number of
days, months or years between the two dates.
SYNTAX: =DATEDIF(start date, end date, return code)

 HOUR- Used to return a value between 0 and 23 from a given time


 MINUTE- Used to return a value between 0 and 59 from a given time
 SECOND- Used to return a value between 0 and 59 from a given time
 ABS- Ensures the value is positive
 TEXT- Used to specify the format of date required
 TRANSPOSE

Use ctrl+shift+enter and


 Unique() select the column or row
in which data is to be
transposed

You might also like