Microsoft Excel Advanced
|
Excel Function Syntax (Required arguments shown
in bold) |
What it does |
|
Logical |
|
|
=IF(logical_test,True,False) |
Tests if a logical test is met and returns one true
and one false result. |
|
=AND(condition1,condition2,...) |
Returns TRUE or FALSE if both or all conditions are
met. |
|
=OR(condition1,condition2,...) |
Returns TRUE or FALSE if either conditions met. |
|
=NOT(condition) |
Returns TRUE if a condition is not met. |
|
=IFERROR(value,
value_if_error) |
Displays a message if an error occurs. |
|
Lookup
& Reference |
|
|
=VLOOKUP(ItemToFind,RangeToLook,Column, SortedOrUnsorted) |
Looks for an item in the leftmost column of a table
range and returns a value from a specified column number. |
|
=CHOOSE(Index_Value, item1, item2, item3) |
Chooses a value or action from a list of items based
on an index number. |
|
=MATCH(lookup_value, lookup_array, match_type) |
Returns the relative position of an item in range. |
|
=INDEX(range, row_num, column_num) |
Returns a value in a range based on a row and column
position within the range. |
|
Statistical |
|
|
=COUNTIFS(criteria_range1, criteria 1,
criteria_range2, criteria2) |
Counts the number of items that meet both or all the
criteria in the criteria ranges. |
|
=SUMIFS(Sum range, criteria_range1, criteria
1, criteria_range2, criteria2) |
Sums a range of values that meet all of the criteria
in the criteria ranges. |
|
=RANK(NumberToRank,RangeOfNumbers, RankOrder) |
Returns the rank position of a value in a list of
numbers (highest rank for ties). |
|
=RANK.AVG(number,ref,order) |
Returns the rank position of a value in a list of
numbers (average rank for ties). |
|
=MEDIAN(Range1,Range2,Range3…) |
Returns the middle rank value in a range. |
|
=MODE(Range1,Range2,Range3...
) |
Returns the most common value in a range. |
|
=ROUND(NumberToRound,DecimalPlaces) |
Rounds a value to a specified number of decimal
places. |
|
=ROUNDUP(NumberToRound,DecimalPlaces) |
Rounds up a value to specified number of decimal
places. |
|
=ROUNDDOWN(Cell,DecimalPlaces) |
Rounds down a value to specified number of decimal
places. |
|
=CEILING(Cell,Decimal
Places) |
Rounds a number to a specified significance. |
|
=MOD(Cell,Devisor) |
Returns the remainder when a number is divided by a
devisor. |
|
=ROW() |
Returns the row number of a reference. |
|
=COLUMN() |
Returns the column number of a reference. |
|
Date
and Time |
|
|
=TODAY() |
Returns the current date |
|
=NETWORKDAYS(StartDate,EndDate,Holidays) |
Calculates the number of working days between two
dates not including holidays. |
|
=DATEDIF(FirstDate,SecondDate,
Interval) |
Subtracts the days between 2 dates in days, months
or years. |
|
=WORKDAY(StartDate,Days,Holidays) |
Returns the date after a specified number of working
days. |
|
Date(year,month,day) |
Returns the date a year, month and date value. |
|
Day(date) |
Returns the Day number of a a date. |
|
Month(date) |
Returns the Month number of a date. |
|
Text |
|
|
=CONCATENATE(text1,text2,text3…) |
Joins together text values. |
|
=LEFT(text,
num_chars) |
Returns a number of characters to the right of a
cell. |
|
=RIGHT(text,num_chars) |
Returns a number of characters to the right of a
cell. |
|
=SEARCH(find_text,within_text,start_num) |
Searches for the character position of a text
character within text. |
|
=FIND(find_text,
within_text, start_num) |
Searches for the character position of a text
character within text (case sensitive). |
|
=LEN(text) |
Returns the number of characters in a cell. |
|
=MID(text,
start_num, num_chars) |
Returns text within cell starting from a given
position and number. |
|
Financial |
|
|
=PMT(rate, nper, pv, fv, type) |
Calculates the payment on a loan based on constant
payment and constant interest rates. |
|
=FV(rate,nper,pmt,pv,type) |
Calculates the future value of a regular number of
payments. |
|
=IRR(initial
investment,cash flow1,cashflow2,guess) |
Calculates the percentage rate of return on a series
of payments of varying amount. |
|
Information |
|
|
=ISNA(cell) |
Checks whether a value is #NA and returns either
TRUE or FALSE. |
|
=ISBLANK(cell) |
Returns TRUE if a cell is empty. |
|
=ISTEXT(cell) |
Returns TRUE if a cell is a text. |
|
=ISNUMBER(cell) |
Reyurns TRUE if a cell is a number. |
|
=CELL(“filename”) |
Returns the filename and path of the current file.
Displays blank if a file is not yet saved. Press F9 to update after saving. |
|
Array
Functions Press Ctrl+Shift+Enter after
typing |
|
|
{=TRANSPOSE(array)} |
Converts a vertical range to horizontal or visa vice
versa. |
|
{=SUM(range1*range2)} |
Returns the product of ranges or arrays. |
|
=SUMPRODUCT(range1,range2) |
Alternative way to return the product of ranges or
arrays. No need to press CSE |
|
{=FREQUENCY(data_range,bin_array)} |
Calculates how often a value occurs in each element
of the bin_array. |
0 Comments