Excel Formulas 500

Count cells between dates

COUNTIFSDATE

Count cells between two numbers

COUNTIFSCOUNTIF

Count cells equal to

COUNTIF

Count cells equal to case sensitive

SUMPRODUCTEXACT

Count cells equal to either x or y

COUNTIF

Count cells equal to one of many things

COUNTIFSUMPRODUCT

Count cells greater than

COUNTIF

Count cells less than

COUNTIF

Count cells not equal to

COUNTIF

Count cells not equal to many things

MATCHISNASUMPRODUCTCOUNTA

Count cells not equal to x or y

COUNTIFSSUMPRODUCT

Count cells over 100 characters

SUMPRODUCTLENN

Count cells that are blank

COUNTBLANKCOUNTA

Count cells that are not blank

COUNTACOUNTIFCOUNTIFS

Count cells that begin with

COUNTIFCOUNTIFS

Count cells that contain either x or y

COUNTIFSUMPRODUCTISNUMBERFIND

Count cells that contain errors

SUMPRODUCTISERRORISERR

Count cells that contain five characters

COUNTIF

Count cells that contain negative numbers

COUNTIF

Count cells that contain numbers

COUNT

Count cells that contain odd numbers

SUMPRODUCTMOD

Count cells that contain positive numbers

COUNTIF

Count cells that contain specific text

COUNTIFSUMPRODUCTFINDISNUMBER

Count cells that contain text

COUNTIFISTEXTSUMPRODUCTCOUNTIFS

Count cells that do not contain

COUNTIF

Count cells that do not contain errors

ISERRORNOTSUMPRODUCT

Count cells that do not contain many strings

ISNUMBERSEARCHMMULTTRANSPOSE

Count cells that end with

COUNTIF

Count dates by day of week

SUMPRODUCTWEEKDAY

Count dates in given year

YEARSUMPRODUCT

Count if row meets internal criteria

SUMPRODUCT

Count if row meets multiple internal criteria

SUMPRODUCT

Count if two criteria match

COUNTIFSSUMPRODUCT

Count items in list

COUNTIFS

Count long numbers without COUNTIF

SUMPRODUCTCOUNTIF

Count matches between two columns

SUMPRODUCT

Count multiple criteria with NOT logic

SUMPRODUCTISNAMATCH

Count numbers by range with COUNTIFS

COUNTIFS

Count numbers that begin with

SUMPRODUCTLEFT

Count numbers third digit equals 3

SUMPRODUCTMID

Count occurrences in entire workbook

COUNTIFSUMPRODUCT

Count paired items in listed combinations

COUNTIFSCONCAT

Count rows that contain specific values

MMULTCOLUMNTRANSPOSE

Count rows with at least n matching values

MMULTCOLUMNTRANSPOSE

Count rows with multiple OR criteria

SUMPRODUCT

Count sold and remaining

COUNTA

Count total matches in two ranges

SUMPRODUCTCOUNTIF

Count unique numeric values in a range

FREQUENCYSUMCOUNTIF

Count unique numeric values with criteria

FREQUENCYSUM

Count unique text values in a range

FREQUENCYMATCHROWSUMPRODUCT

Count unique text values with criteria

FREQUENCYMATCHROWSUM

Count unique values in a range with COUNTIF

SUMPRODUCTCOUNTIF

Count visible rows in a filtered list

SUBTOTAL

Count visible rows only with criteria

SUBTOTALOFFSETSUMPRODUCT

COUNTIF with non-contiguous range

COUNTIFINDIRECT

COUNTIFS with multiple criteria and OR logic

COUNTIFS

Running count of occurrence in list

COUNTIF

Summary count by month with COUNTIFS

COUNTIFSEDATE

Summary count of non-blank categories

COUNTIFS

Summary count with COUNTIF

COUNTIF

Summary count with percentage breakdown

COUNTIFCOUNTA

SUMPRODUCT count multiple OR criteria

SUMPRODUCT

Two-way summary count with COUNTIFS

COUNTIFS

Sum
3D sum multiple worksheets
SUM

3D SUMIF for multiple worksheets

SUMPRODUCTSUMIFINDIRECT

Calculate running total

SUM

Subtotal by color
SUMIF

Subtotal by invoice number

SUMIFCOUNTIF

Subtotal invoices by age

SUMIF

Sum bottom n values

SUMPRODUCTSMALLSUMROWINDIRECT

Sum bottom n values with criteria

SMALLSUM

Sum by group
SUMIF

Sum by month
SUMIFSEOMONTH

Sum by month ignore year

SUMPRODUCTMONTH

Sum by month in columns

SUMIFSEOMONTH

Sum by week
SUMIFS

Sum by week number

SUMIFSWEEKNUM

Sum by weekday
SUMPRODUCTWEEKDAY

Sum columns based on adjacent criteria

SUMPRODUCT

Sum entire column

SUM

Sum every n rows

SUMOFFSET

Sum every nth column

SUMPRODUCTCOLUMNMOD

Sum formulas only

SUMPRODUCTISFORMULANOT

Sum if begins with

SUMIFSUMIFS

Sum if between
SUMIFS

Sum if by year
SUMIFSDATE

Sum if cell contains text in another cell

SUMIFSUMIFS

Sum if cells are equal to

SUMIFSUMIFS

Sum if cells are not equal to

SUMIFSUMIFS

Sum if cells contain an asterisk

SUMIFSUMIFS

Sum if cells contain both x and y

SUMIFS

Sum if cells contain either x or y

SUMIFSSUMPRODUCTISNUMBERFINDSEARCH

Sum if cells contain specific text

SUMIFSUMIFS

Sum if date is between

SUMIFSDATE

Sum if date is greater than

SUMIFSUMIFSDATE

Sum if ends with

SUMIFSUMIFS

Sum if equal to either x or y

SUMIFSUMPRODUCT

Sum if equal to one of many things

SUMIFSUMPRODUCT

Sum if greater than

SUMIFSUMIFS

Sum if less than

SUMIFSUMIFS

Sum if multiple criteria

SUMIFS

Sum if not blank

SUMIFSUMIFS

Sum if one criteria multiple columns

SUMPRODUCT

Sum last n columns

INDEXCOLUMNSUM

Sum matching columns

SUMPRODUCTLEFT

Sum matching columns and rows

SUMPRODUCT

Sum sales in last 30 days by ID

SUMIFSTODAY

Sum through n months

OFFSETSUM

Sum top n values

SUMPRODUCTLARGESUMROWINDIRECT

Sum top n values with criteria

SUMPRODUCTLARGE

Sum visible rows in a filtered list

SUBTOTAL

SUMIFS with horizontal range

SUMIFS

SUMIFS with multiple criteria and OR logic

SUMIFSSUM

SUMPRODUCT with IF
SUMPRODUCT

Average
Average and ignore errors
AVERAGEIFAGGREGATE

Average by month
AVERAGEIFSEOMONTH

Average if criteria not blank

AVERAGEIFS

Average last 5 values

OFFSETCOUNTAVERAGE

Average last 5 values in columns

OFFSETCOUNTAVERAGE

Average numbers
AVERAGE

Average numbers ignore zero

AVERAGEIFAVERAGEIFS

Average pay per week

SUMPRODUCTCOUNTIF

Average response time per month

AVERAGEIFSEOMONTH

Average the last 3 numeric values

LOOKUPLARGEROWAVERAGE

Average top 3 scores

LARGEAVERAGE

Average with multiple criteria

AVERAGEIFS

Must pass 4 out of 6 subjects

IFCOUNTIFAND

Weighted average
SUMPRODUCTSUM

Dynamic array
Basic filter example
FILTER

Count unique values

UNIQUECOUNTA

Distinct values
UNIQUE

Extract common values from two lists

FILTERCOUNTIFUNIQUESORT

Filter by date
FILTERMONTH

Filter contains one of many

FILTERISNUMBERMATCH

Filter data between dates

FILTER

Filter exclude blank values

FILTER

Filter text contains

FILTERSEARCHISNUMBER

Filter this or that

FILTER

FILTER with complex multiple criteria

FILTERLEFTMONTHNOT

Filter with exact match

FILTERSEARCHISNUMBER

FILTER with multiple OR criteria

FILTERISNUMBERMATCH

Random list of names

SORTBYRANDARRAYCOUNTASEQUENCE

Sequence of days
SEQUENCEWORKDAY.INTL

Sequence of months
SEQUENCEEDATEEOMONTH

Sequence of times
SEQUENCETIME

Sequence of years
SEQUENCEYEARMONTHDAY

Sort by custom list

SORTBYMATCH

Sort by one column

SORT

Sort by random
SORTBYRANDARRAYCOUNTA

Sort by two columns

SORTBY

Sort text by length

SORTBYLEN

Sort values by columns

SORTSORTBY

Unique values
UNIQUE

Unique values by count

UNIQUEFILTERCOUNTIF

Unique values ignore blanks

UNIQUEFILTER

Unique values with criteria

UNIQUEFILTER

Unique values with multiple criteria

UNIQUEFILTER

Min and Max

First in, last out times
MINIFSMAXIFSMAXIF

Large with criteria

LARGE

Larger of two values

MAX

Max of every nth column

MAXCOLUMNMOD

Max value ignore all errors

AGGREGATEMAXIFS

Max value in given month

MAXIFAGGREGATETEXTINDEXMATCH

Max value on given weekday

MAXIFAGGREGATE

Max value with variable column

INDEXMATCHMAXFILTERCOUNTIF

Maximum if multiple criteria

MAXIFMAXIFS

Maximum value
MAXMAXIFS

Maximum value if
MAXIFMAXIFS

Minimum difference if not blank

MINIFMINIFS

Minimum if multiple criteria

MINIFMINIFS

Minimum value
MIN

Minimum value if
MINIFMINIFS

nth largest value

LARGE

nth largest value with criteria

LARGE

nth largest value with duplicates

MAXIF

nth smallest value

SMALL

nth smallest value with criteria

SMALL

Smaller of two values

MIN

Lookup
Approximate match with multiple criteria
INDEXMATCHIF

Basic INDEX MATCH approximate

INDEXMATCH

Basic INDEX MATCH exact

INDEXMATCH

Basic Tax Rate calculation with VLOOKUP

VLOOKUP

Break ties with helper column and COUNTIF

SMALLINDEXMATCH

Calculate grades with VLOOKUP

VLOOKUP

Calculate shipping cost with VLOOKUP

VLOOKUP

Case sensitive match

MATCHEXACT

Commission split formula example

VLOOKUP

Count missing values

COUNTIFSUMPRODUCTMATCHISNA

Dynamic lookup table with INDIRECT

INDIRECTVLOOKUP

Exact match lookup with INDEX and MATCH

INDEXMATCHEXACT

Exact match lookup with SUMPRODUCT

SUMPRODUCTEXACT

Extract all matches with helper column

INDEXMATCHANDSUM

Extract all partial matches

INDEXAGGREGATEISNUMBERSEARCH

Extract multiple matches into separate columns

INDEXSMALLIFERRORROWCOLUMNS

Extract multiple matches into separate rows

INDEXSMALLIFERRORROWROWS

Faster VLOOKUP with 2 VLOOKUPS

VLOOKUPNA

Find closest match

INDEXMATCHABSMIN

Find longest string in column

INDEXMATCHMAXLEN

Find longest string with criteria

INDEXMATCHMAXLEN

Find lowest n values

SMALLINDEXMATCH

Find missing values

COUNTIFVLOOKUPMATCH

First match in range with wildcard

INDEXMATCH

Get address of lookup result

INDEXMATCHCELL

Get cell content at given row and column

ADDRESSINDIRECTINDEX

Get employee information with VLOOKUP

VLOOKUP

Get first match cell contains

INDEXMATCHISNUMBERSEARCH

Get first non-blank value in a list

INDEXMATCHISBLANK

Get first text value in a list

VLOOKUP

Get first text value with HLOOKUP

HLOOKUP

Get information corresponding to max value

MAXMATCHINDEX

Get last match

INDEXMATCHMAXROW

Get last match cell contains

LOOKUPSEARCH

Get location of value in 2D array

SUMPRODUCTROWCOLUMN

Get nth match

SMALLIFMINROW

Get nth match with INDEX / MATCH

INDEXSMALL

Get nth match with VLOOKUP

VLOOKUPCOUNTIF

INDEX and MATCH descending order

INDEXMATCH

Index and match on multiple columns

MMULTCOLUMNTRANSPOSEINDEX

INDEX and MATCH with multiple criteria

INDEXMATCH

INDEX with variable array

INDEXMATCHCHOOSE

Join tables with INDEX and MATCH

INDEXMATCH

Look left with VLOOKUP

VLOOKUPCHOOSE

Lookup and sum column

INDEXMATCHSUM

Lookup entire column

INDEXMATCH

Lookup entire row

INDEXMATCH

Lookup last file version

LOOKUPISNUMBERFIND

Lookup latest price

LOOKUP

Lookup lowest Monday tide

INDEXMATCHIFMINXLOOKUP

Lookup lowest value

INDEXMATCHMIN

Lookup up cost for product or service

VLOOKUP

Lookup value between two numbers

LOOKUP

Lookup with variable sheet name

VLOOKUPINDIRECT

Match first does not begin with

INDEXMATCHLEFT

Match first error

MATCHISERROR

Match first occurrence does not contain

INDEXMATCHISNUMBERSEARCH

Match long text

MATCHLEFTMIDEXACT

Match next highest value

INDEXMATCH

Max if criteria match

MAXIFMAXIFS

Merge tables with VLOOKUP

VLOOKUP

Multi-criteria lookup and transpose

INDEXMATCH

Multiple chained VLOOKUPs

VLOOKUPIFERROR

Multiple matches in comma separated list

TEXTJOIN

Nearest location with XMATCH

INDEXXMATCH

Next largest match with the MATCH function

INDEXMATCH

Partial match against numbers with wildcard

MATCHTEXT

Partial match with VLOOKUP

VLOOKUP

Position of first partial match

MATCHINDEX

Position of max value in list

MAXMATCH

Reverse VLOOKUP example

VLOOKUPCHOOSEINDEXMATCHXLOOKUP

Self-contained VLOOKUP
VLOOKUP

Sum lookup values using SUMIF

SUMIFSUMPRODUCT

Sum range with INDEX

INDEXSUM

SUMIFS multiple criteria lookup in table

SUMIFS

Two-way approximate match multiple criteria

INDEXMATCHIF

Two-way lookup with INDEX and MATCH

INDEXMATCH

Two-way lookup with VLOOKUP

VLOOKUPMATCH

VLOOKUP by date
VLOOKUP

VLOOKUP from another sheet

VLOOKUP

VLOOKUP from another workbook

VLOOKUP

VLOOKUP if blank return blank

VLOOKUPIF

VLOOKUP override output

VLOOKUP

VLOOKUP with 2 lookup tables

VLOOKUP

VLOOKUP with multiple critiera

VLOOKUP

VLOOKUP with numbers and text

VLOOKUP

VLOOKUP with two client rates

VLOOKUP

VLOOKUP without #N/A error

VLOOKUPIFERRORIFNA

XLOOKUP basic approximate match

XLOOKUP

XLOOKUP basic exact match

XLOOKUP

XLOOKUP case-sensitive
XLOOKUPEXACT

XLOOKUP date of max value

XLOOKUPINDEXMATCH

XLOOKUP horizontal lookup

XLOOKUP

XLOOKUP last match

XLOOKUP

XLOOKUP latest by date

XLOOKUPMAX

XLOOKUP lookup left

XLOOKUP

XLOOKUP lookup row or column

XLOOKUP

XLOOKUP match text contains

XLOOKUPSEARCHFINDISNUMBER

XLOOKUP rearrange columns

XLOOKUP

XLOOKUP two-way exact match

XLOOKUPINDEXMATCH

XLOOKUP wildcard match example

XLOOKUPTRANSPOSE

XLOOKUP with complex multiple criteria

FILTERLEFTMONTHNOT

XLOOKUP with logical criteria

XLOOKUP

XLOOKUP with multiple criteria

XLOOKUP

XMATCH reverse search

XMATCHINDEX

If
If cell begins with x, y, or z
SUMCOUNTIF

If cell contains
IF

if cell contains this or that

COUNTIFSUM

If cell equals
IF

If cell is blank
IFISBLANK

If cell is greater than

IF

If cell is not blank

IFISBLANKNOT

If cell is this OR that

IFOR

If cell is x or y and z
IFORAND

If else
IF

If not blank multiple cells

IFISBLANKNOT

If NOT this or that

IFORNOT

If this AND that

IFAND

If this AND that OR that

IFANDOR

IF with boolean logic

IF

IF with other calculations

IFSUMANDAVERAGE

IF with wildcards
IF

Invoice status with nested if

IFTODAY

Nested IF function example

IF

Nested IF with multiple AND

IFANDOR

Only calculate if not blank

IFISBLANKCOUNTBLANKANDORCOUNT

Return blank if
IFISBLANKCOUNTBLANK

Show checkmark if complete

IFISBLANKNOT

Tax rate calculation with fixed base

IF

Two-tier sales tax calculation

IF

Win loss points calculation

VLOOKUPIF

Grouping
Categorize text with keywords
INDEXMATCHISNUMBERSEARCHXLOOKUP

Group arbitrary text values

VLOOKUP

Group numbers at uneven intervals

LOOKUP

Group numbers with VLOOKUP

VLOOKUP

Group times into 3 hour buckets

FLOOR

Group times into unequal buckets

VLOOKUP

If cell contains one of many things

INDEXMATCHISNUMBERSEARCH

Map inputs to arbitrary values

VLOOKUPCHOOSE

Map text to numbers

VLOOKUP

Running count group by n size

COUNTACEILING

Conditional formatting
Highlight 3 smallest values with criteria
SMALLAND

Conditional formatting based on another cell

Conditional formatting based on another column
Conditional formatting column is blank
ORANDNOT

Conditional formatting date past due

TODAY

Conditional formatting dates overlap

SUMPRODUCT

Conditional formatting highlight target percentage

Conditional formatting last n rows
Find duplicate values in two columns
COUNTIFAND

Gantt chart
AND

Gantt chart by week

AND

Gantt chart time schedule

ANDOR

Gantt chart with weekends

WEEKDAY

Highlight approximate match lookup conditional formatting

LOOKUPORAND

Highlight blank cells

ISBLANKLEN

Highlight bottom values

SMALL

Highlight cells that begin with

COUNTIFFINDIFERROR

Highlight cells that contain

SEARCHFINDISNUMBER

Highlight cells that contain one of many

SEARCHFINDISNUMBERSUMPRODUCT

Highlight cells that end with

COUNTIFRIGHTLENEXACT

Highlight cells that equal

EXACT

Highlight column differences

EXACTNOT

Highlight data by quartile

QUARTILE

Highlight dates between

DATEAND

Highlight dates greater than

DATE

Highlight dates in same month and year

DATETEXT

Highlight dates in the next N days

TODAYAND

Highlight dates that are weekends

WEEKDAYOR

Highlight duplicate columns

SUMPRODUCTCOUNTIF

Highlight duplicate rows

COUNTIFSCOUNTIFSUMPRODUCT

Highlight duplicate values

COUNTIF

Highlight entire rows

Highlight every other row
ISEVENISODDROWMOD

Highlight integers only

MOD

Highlight missing values

COUNTIF

Highlight multiples of specific value

MOD

Highlight numbers that include symbols

ISNUMBERMID

Highlight row and column intersection exact match

ORAND
Highlight rows that contain
SEARCHFIND

Highlight rows with blank cells

COUNTBLANK

Highlight rows with dates between

DATEAND

Highlight top values

LARGE

Highlight unique values

COUNTIF

Highlight unprotected cells

CELL

Highlight values between

AND

Highlight values greater than

Highlight values not between X and Y
ANDNOT

Shade alternating groups of n rows

ROWCEILINGISEVENISODD

Data validation
Data validation allow numbers only
ISNUMBER

Data validation allow text only

ISTEXT

Data validation allow uppercase only

UPPEREXACTAND

Data validation allow weekday only

YEARTODAY

Data validation date in next 30 days

ANDTODAY

Data validation date in specific year

YEARTODAY

Data validation don't exceed total

SUM

Data validation exists in list

COUNTIF

Data validation must begin with

EXACTLEFTCOUNTIF

Data validation must contain specific text

FINDISNUMBER

Data validation must not contain

ISNUMBERSEARCHSUMPRODUCT

Data validation must not exist in list

COUNTIF

Data validation no punctuation

FINDCOUNT

Data validation number multiple 100

MOD

Data validation only dates between

ANDDATE

Data validation require unique number

ANDISNUMBERCOUNTIF

Data validation specific characters only

MATCHCOUNTLENMIDINDIRECT

Data validation unique values only

COUNTIF

Data validation whole percentage only

TRUNCAND

Data validation with conditional list

IF

Rank
Rank function example
RANK

Rank if formula
COUNTIFSRANK

Rank race results

RANK

Rank with ordinal suffix

CHOOSEABSMOD

Rank without ties

RANKCOUNTIF

Round
Get decimal part of a number
TRUNC

Get integer part of a number

TRUNCINT

Get number at place value

MOD

Round a number
ROUND

Round a number down

ROUNDDOWN
Round a number down to nearest multiple
FLOOR

Round a number to n significant digits

ROUNDLOG10INTABS

Round a number to nearest multiple

MROUND

Round a number up
ROUNDUP

Round a number up to nearest multiple

CEILING

Round a number up to next half

CEILING

Round a price to end in .99

ROUND

Round by bundle size

CEILING

Round time to nearest 15 minutes

MROUNDCEILINGFLOOR

Round to nearest 1000

ROUND

Round to nearest 5
MROUNDCEILINGFLOOR

Date and Time

Add business days to date
WORKDAYNETWORKDAYS

Add days exclude certain days of week

WORKDAY.INTL
Add days to date
DAY

Add decimal hours to time

TIMEMOD

Add decimal minutes to time

TIMEMOD

Add months to date

EDATE

Add workdays no weekends

WORKDAY.INTLNETWORKDAYS

Add workdays to date custom weekends

WORKDAY.INTLNETWORKDAYS

Add years to date

DATEYEARMONTHDAY

Assign points based on late time

IFVALUE

Basic overtime calculation formula

MIN

Basic timesheet formula with breaks

MOD

Calculate date overlap in days

MAXMIN

Calculate days remaining

TODAY

Calculate expiration date

EOMONTHEDATE

Calculate number of hours between two times

IFMOD
Calculate retirement date
EDATEEOMONTH

Calculate years between dates

YEARFRACINT

Convert date string to date time

DATEVALUELEFTMIDTIMEVALUE

Convert date to Julian format

DATEYEARTEXT

Convert date to month and year

TEXT

Convert date to text

TEXT

Convert decimal hours to Excel time

Convert decimal minutes to Excel time
Convert decimal seconds to Excel time
Convert Excel time to decimal hours
Convert Excel time to decimal minutes
Convert Excel time to decimal seconds
Convert Excel time to Unix time
DATE

Convert text date dd/mm/yy to mm/dd/yy

DATELEFTMIDRIGHTTRIM

Convert text timestamp into time

TIMEMID

Convert text to date

DATELEFTMIDRIGHT

Convert time to money

Convert time to time zone
MOD

Convert Unix time stamp to Excel date

DATE

Count birthdays by month

SUMPRODUCTMONTH

Count dates in current month

COUNTIFSEOMONTHSUMPRODUCTN

Count day of week between dates

WEEKDAYROWINDIRECTSUMPRODUCTSEQUENCE

Count holidays between two dates

SUMPRODUCT

Count times in a specific range

COUNTIFSTIME

Create date range from two dates

TEXTIF

Custom weekday abbreviation

WEEKDAYCHOOSE

Date is same month

MONTH

Date is same month and year

MONTHYEAR

Date is workday
WORKDAYWORKDAY.INTL

Days in month
DAYEOMONTH

Days until expiration date

Display the current date
TODAY

Display the current date and time

NOW

Dynamic calendar grid

WEEKDAYCHOOSETODAY

Dynamic date list

TODAYROWS

Extract date from a date and time

INTTRUNC

Extract time from a date and time

MOD

Get age from birthday

INTTODAYYEARFRAC

Get date from day number

DATERIGHTLEFT

Get day from date

DAY

Get day name from date

TEXTWEEKDAYCHOOSE

Get days before a date

TODAY

Get days between dates

TODAYDAYS

Get days between dates ignoring years

DATEDIF

Get days, hours, and minutes between dates

INTTEXT
Get days, months, and years between dates
DATEDIF

Get first day of month

DAYEOMONTH

Get first day of previous month

EOMONTH

Get fiscal quarter from date

MONTHCHOOSE

Get fiscal year from date

MONTHYEAR

Get last day of month

EOMONTHDATE

Get last weekday in month

EOMONTHWEEKDAY

Get last working day in month

WEEKDAYEOMONTH

Get Monday of the week

WEEKDAY

Get month from date

MONTHDATE

Get month name from date

MONTHCHOOSETEXT

Get months between dates

DATEDIF

Get most recent day of week

MOD

Get next day of week

WEEKDAY
Get next scheduled event
MINIFMINIFSINDEXMATCH

Get nth day of week in month

DAYWEEKDAY

Get nth day of year

DATEYEAR

Get percent of year complete

YEARFRACYEARDATE

Get project end date

WORKDAYWORKDAY.INTL

Get project midpoint

WORKDAYWORKDAY.INTL

Get project start date

WORKDAYWORKDAY.INTL

Get quarter from date

MONTHROUNDUP

Get same date next month

EDATE

Get same date next year

EDATE

Get week number from date

WEEKNUMISOWEEKNUM

Get work hours between dates

NETWORKDAYSNETWORKDAYS.INTL

Get work hours between dates and times

NETWORKDAYSNETWORKDAYS.INTL

Get work hours between dates custom schedule

MIDROWINDIRECTWEEKDAYSUMPRODUCT
Get workdays between dates
NETWORKDAYSNETWORKDAYS.INTL

Get year from date

YEAR

If Monday, roll back to Friday

WEEKDAYIF

Join date and text

TEXT

Last n days
ANDTODAY

Last n months
ANDTODAYEOMONTH

Last n weeks
ANDTODAYWEEKDAY

Last updated date stamp

TEXTTODAY

List holidays between two dates

TEXTJOINIF

Next anniversary date

EDATEDATEDIF

Next biweekly payday from date

CEILING

Next business day 6 months in future

WORKDAYWORKDAY.INTL

Next working day

WORKDAYWORKDAY.INTL

Pad week numbers with zeros

TEXTWEEKNUM
Parse time string to time
TIMERIGHTLEFTMID

Sum race time splits

SUM

Sum time by week and project

SUMIFS

Sum time over 30 minutes

SUMPRODUCTTIMESUMIFSCOUNTIFS

Sum time with SUMIFS

SUMIFS

Time difference in hours as decimal value

MOD

Total hours that fall between two times

MINMAX

Workdays per month

NETWORKDAYSNETWORKDAYS.INTLEOMONTH

Working days in year

NETWORKDAYSNETWORKDAYS.INTLDATETODAY

Working days left in month

NETWORKDAYSNETWORKDAYS.INTLTODAY

Year is a leap year

DATEYEARMONTH

Date series
Series of dates by custom days
WORKDAY.INTL

Series of dates by day

Series of dates by month
DAYMONTHYEARDATEEOMONTH

Series of dates by weekends

WORKDAY.INTLWEEKDAY

Series of dates by workdays

WORKDAYNETWORKDAYS.INTLIFWEEKDAY

Series of dates by year

DAYMONTHYEARDATE

Text
Abbreviate names or words
TEXTJOINMIDROWCODELENINDIRECT

Add a line break with a formula

CHAR

Add line break based on OS

INFOCHAR

Capitalize first letter

LEFTMIDLEN

Cell contains all of many things

SUMPRODUCTISNUMBERSEARCHCOUNTA

Cell contains number

FINDCOUNT

Cell contains one of many things

SUMPRODUCTISNUMBERSEARCH

Cell contains one of many with exclusions

SUMPRODUCTISNUMBERSEARCH

Cell contains some words but not others

COUNTSEARCHAND

Cell contains specific text

SEARCHISNUMBERFIND

Cell contains which things

SEARCHISNUMBERFIND

Cell equals one of many things

SUMPRODUCT

Clean and reformat telephone numbers

SUBSTITUTE

Compare two strings

EXACT

Conditional message with REPT function

REPTIFAND

Convert numbers to text

TEXT

Convert string to array

MIDROWLENINDIRECT

Convert text to numbers

VALUELEFTRIGHT

Count keywords cell contains

SUMPRODUCTMATCHISNUMBERSEARCH

Count line breaks in cell

LENSUBSTITUTECHARISBLANK

Count specific characters in a cell

LENSUBSTITUTEUPPER

Count specific characters in a range

LENSUBSTITUTEUPPERSUMPRODUCT

Count specific words in a cell

LENSUBSTITUTEUPPER

Count specific words in a range

LENSUBSTITUTEUPPERSUMPRODUCT

Count total characters in a cell

LEN

Count total characters in a range

LENSUMPRODUCT

Count total words in a cell

LENSUBSTITUTETRIMISBLANK

Count total words in a range

SUMPRODUCTLENSUBSTITUTETRIM

Double quotes inside a formula

CHAR

Extract last two words from cell

MIDLENSUBSTITUTEFIND

Extract multiple lines from a cell

TRIMMIDSUBSTITUTEREPTLEN

Extract nth word from text string

TRIMMIDSUBSTITUTEREPTLEN

Extract substring
MID

Extract text between parentheses

MIDSEARCH

Extract word containing specific text

MIDMAXFINDSUBSTITUTEREPTTRIM

Extract word that begins with specific character

MIDLENFINDSUBSTITUTEREPTTRIM

Find and replace multiple values

SUBSTITUTEINDEX

Find nth occurrence of character

SUBSTITUTEFINDCHAR

Get first word

LEFTFINDISERROR

Get last line in cell

TRIMSUBSTITUTERIGHTREPTCHAR

Get last word

TRIMSUBSTITUTERIGHTREPT

Join cells with comma

TRIMSUBSTITUTETEXTJOIN

Most frequent text with criteria

INDEXMATCHMODEIF

Most frequently occurring text

INDEXMATCHMODE

Normalize text
SUBSTITUTETRIMLOWER

Pad text to equal length

REPTLEN

Position of 2nd 3rd etc instance of character

SUBSTITUTEFIND

Remove characters from right

LEFTLENVALUE

Remove file extension from filename

LEFTFIND

Remove first character

REPLACERIGHTLENLEFT

Remove leading and trailing spaces from text

CLEANTRIMSUBSTITUTE

Remove line breaks

SUBSTITUTECLEAN

Remove text by matching

SUBSTITUTE

Remove text by position

REPLACE

Remove text by variable position

REPLACEFIND

Remove unwanted characters

SUBSTITUTECHARCODELEFT

Replace one character with another

SUBSTITUTE

Reverse text string

TEXTJOINMIDINDIRECTSEQUENCE

Split dimensions into three parts

LEFTRIGHTMIDLENSUBSTITUTE

Split dimensions into two parts

SUBSTITUTELEFTRIGHTFIND

Split numbers from units of measure

MAXLEFTRIGHTISNUMBERVALUEMID

Split text and numbers

FINDMINRIGHTLEFT

Split text string at specific character

LEFTRIGHTLENFIND

Split text with delimiter

TRIMMIDSUBSTITUTEREPTLEN

Strip html from text or numbers

MIDLEN

Strip non-numeric characters

TEXTJOINMIDROWINDIRECT

Strip numeric characters from cell

TEXTJOINMIDROWINDIRECT

Translate letters to numbers

TEXTJOINVLOOKUPMIDROWT

Trim text to n words

LEFTSUBSTITUTEFIND

Financial
Annual compound interest schedule
FV

Annuity solve for interest rate

RATE

Bond valuation example

PVFVPRICE

CAGR formula examples

GEOMEANRRI

Calculate compound interest

FV

Calculate cumulative loan interest

CUMIPMT

Calculate cumulative loan principal payments

CUMPRINC

Calculate interest for given period

PPMT

Calculate interest rate for loan

RATE

Calculate loan interest in given year

CUMIPMT

Calculate original loan amount

PV

Calculate payment for a loan

PMT

Calculate payment periods for loan

NPER

Calculate periods for annuity

PMTPVFV

Calculate principal for given period

PPMT

Calculate simple interest

Compare effect of compounding periods
FV

Effective annual interest rate

EFFECT

Estimate mortgage payment

PMTPVFV

Future value of annuity

FVPV

Future value vs. Present value

PVFV

Income tax bracket calculation

VLOOKUP

NPV formula for net present value

NPV

Payment for annuity

PMTPVFV
Present value of annuity
PVFV

Workbook
Dynamic workbook reference
INDIRECT

Dynamic worksheet reference

INDIRECT

Get full workbook name and path

CELL

Get sheet name only

CELLMIDFIND

Get workbook name and path without sheet

CELLLEFTFINDSUBSTITUTE

Get workbook name only

CELLMIDFIND

Get workbook path only

CELLLEFTFIND

Indirect named range different sheet

INDIRECT

List sheet index numbers

SHEET

List sheet names with formula

INDEXMIDROWNOWT

Worksheet name exists

ISREFINDIRECT

Internet
Create email address from name
LEFTLOWER

Create email address with name and domain

LEFTLOWER

Get domain from email address

RIGHTLENFIND

Get domain name from URL

LEFTFIND

Get name from email address

LEFTFIND

Get page from URL

TRIMRIGHTSUBSTITUTEREPT

Get top level domain (TLD)

RIGHTLENSUBSTITUTEFIND

Remove trailing slash from url

LENRIGHTLEFT

Strip protocol and trailing slash from URL

MIDRIGHTLENFIND

Names
Get first name from name
LEFTFIND

Get first name from name with comma

RIGHTLENFIND

Get last name from name

RIGHTLENSUBSTITUTEFIND

Get last name from name with comma

LENLEFT

Get middle name from full name

MIDLENTRIM

Join first and last name

CONCATENATE

Put names into proper case

PROPERTRIM

Percentage
Calculate percent variance
ABS

Decrease by percentage
Get amount with percentage
Get original number from percent change
Get original price from percentage discount
Get percent change
Get percentage discount
Get percentage of total
Get profit margin percentage
Get total from percentage
Increase by percentage
Percent of goal
Percent of students absent
Percent sold
Project complete percentage
COUNTA

Random
Random date between two dates
RANDBETWEENWORKDAY

Random number between two numbers

RANDBETWEEN

Random number from fixed set of options

CHOOSERANDBETWEEN

Random number weighted probability

RANDMATCHINDEX

Random text values

CHOOSERANDBETWEEN

Random times at specific intervals

RAND

Random value from list or table

INDEXRANDBETWEENROWS

Randomly assign data to groups

RANDBETWEENCHOOSE

Randomly assign people to groups

RANDROUNDUPCEILING

Range
Address of first cell in range
ADDRESSROWCOLUMNCELL

Address of last cell in range

ADDRESSROWCOLUMNROWSCOLUMNS

All cells in range are blank

SUMPRODUCT

All values in a range are at least

COUNTIFNOT

Count visible columns

CELLNSUM

COUNTIFS with variable range

COUNTIFSOFFSETADDRESSINDIRECTROW

Define range based on cell value

INDEXOFFSET

Dynamic named range with INDEX

INDEXCOUNTA

Dynamic named range with OFFSET

OFFSETCOUNTA

First column number in range

ROWSMIN

First match between two ranges

INDEXMATCHCOUNTIF

First row number in range

ROWMIN

Get address of named range

ADDRESSROWCOLUMNROWSCOLUMNS

Get relative column numbers in range

COLUMN

Get relative row numbers in range

ROW

Last column number in range

COLUMNCOLUMNSMIN

Last n rows
ROWROWSINDEX

Last row in mixed data with blanks

MATCH

Last row in mixed data with no blanks

COUNTA

Last row in numeric data

MATCH

Last row in text data

MATCHREPT

Last row number in range

ROWROWSMIN

Multiple cells are equal

ANDEXACT

Multiple cells have same value

COUNTIF

Multiple cells have same value case sensitive

EXACTSUMPRODUCTCOUNTA

Range contains a value not in another range

SUMPRODUCTMATCHISNA

Range contains numbers

SUMPRODUCTISNUMBER

Sequential row numbers

ROW

Total cells in a range

ROWSCOLUMNS

Total columns in range

COLUMNS

Total rows in range

ROWS

Tables
Average last N values in a table
INDEXAVERAGEROWS

Basic inventory formula example

SUMIFS

Count table columns

COLUMNS

Count table rows

ROWS

COUNTIFS with variable table column

COUNTIFSINDEXMATCHINDIRECT

Dynamic reference Table name

INDIRECT

Get column index in Excel Table

MATCH

Get column name from index in table

INDEX

Percentile IF in table
PERCENTILE

Running total in Table

INDEXSUM

Sum multiple tables

SUM

SUMIFS vs other lookup formulas

SUMIFSINDEXMATCHLOOKUPXLOOKUPSUMPRODUCT

SUMIFS with Excel Table

SUMIFS

Two-way lookup VLOOKUP in a Table

VLOOKUPMATCH

Two-way summary with SUMIFS

SUMIFS

Errors
How to fix a circular reference error
How to fix the #### (hashtag) error
How to fix the #DIV/0! error
IFERRORISERRORERROR.TYPE

How to fix the #N/A error

VLOOKUPIFERRORMATCH

How to fix the #NAME? error

IFERRORISERRORERROR.TYPE

How to fix the #NULL! error

IFERRORISERRORERROR.TYPE

How to fix the #NUM! error

IFERRORISERRORERROR.TYPE

How to fix the #REF! error

ISREFIFERROR

How to fix the #VALUE! error

IFERRORISERRORERROR.TYPE

Miscellaneous
Abbreviate state names
VLOOKUPINDEXMATCH

Basic array formula example

MAXMIN

Basic attendance tracking formula

COUNTIF

Basic error trapping example

IFERROR
Basic in cell histogram
REPTCHAR

Basic numeric sort formula

RANKCOUNTIF

Basic outline numbering

COUNTAMIDFINDLEN

Basic text sort formula

RANKCOUNTIF

BMI calculation formula

CONVERTPOWER

Build hyperlink with VLOOKUP

HYPERLINKVLOOKUP

Calculate a ratio from two numbers

GCD

Cap percentage at 100

MIN

Cap percentage at specific amount

MIN

Carry-on baggage Inches to centimeters

CONVERT

Cash denomination calculator

SUMPRODUCTINTFLOOR

Celsius to Fahrenheit conversion

CONVERT

Change negative numbers to positive

ABS

Check register balance

IFANDISBLANK
Coefficient of variation
STDEV.PSTDEV.SAVERAGE

Conditional median with criteria

MEDIAN

Conditional mode with criteria

MODE

Convert column letter to number

COLUMNINDIRECT

Convert column number to letter

ADDRESSSUBSTITUTE

Convert expense time units

INDEXMATCH

Convert feet and inches to inches

LEFTFINDMIDSUBSTITUTE

Convert inches to feet and inches

INTMOD

Convert pounds to kilograms

CONVERT

Copy value from every nth column

OFFSETCOLUMN

Copy value from every nth row

OFFSETROW

Count consecutive monthly orders

FREQUENCYMAXIF

Count values out of tolerance

SUMPRODUCTABS

Count with repeating values

ROUNDUPCOLUMNROW
Course completion status summary
COUNTIFS

Course completion summary with criteria

COUNTIFS

Create array of numbers

INDIRECTROW

Cube root of number

POWER

Customer is new
COUNTIFS

Display sorted values with helper column

INDEXMATCHROWS

Dropdown sum with all option

SUMIFSUMIF

Easy bundle pricing with SUMPRODUCT

SUMPRODUCT

Expense begins on specific month

IF

Extract unique items from a list

INDEXMATCHCOUNTIFLOOKUP

Filter values in array formula

ISNUMBERMATCH

Find and retrieve missing values

INDEXMATCHISNA

Fixed value every N columns

MODCOLUMN

Flag first duplicate in a list

COUNTIFCOUNTIFS
Flip table rows to columns
TRANSPOSE

Force negative numbers to zero

MAX

Forecast vs actual variance

SUMIFS

Formula with locked reference

INDIRECT

Get date associated with last entry

LOOKUP

Get first entry by month and year

INDEXMATCHTEXT

Get last entry by month and year

LOOKUPTEXT

Get pivot table grand total

GETPIVOTDATA

Get pivot table subtotal

GETPIVOTDATA

Get pivot table subtotal grouped date

GETPIVOTDATA

Get value of last non-empty cell

LOOKUP

Hyperlink to first blank cell

HYPERLINKCELLINDEXMATCH

Hyperlink to first match

HYPERLINKCELLINDEXMATCH

Increment a calculation with ROW or COLUMN

ROWCOLUMN
Increment a number in a text string
RIGHTTEXT

Increment cell reference with INDIRECT

INDIRECTCELL

Issue response time calculation

VLOOKUP

Leave a comment in a formula

N

Link to multiple sheets

HYPERLINKCELLINDEXMATCH

List contains duplicates

SUMPRODUCTCOUNTIF

List most frequently occuring numbers

MODEISNUMBERMATCH

Longest winning streak

FREQUENCYMAXIF

Lookup last file revision

SEARCHISERRORMAXINDEXIFROW

Most frequently occurring number

MODE

Multiplication table formula

New customers per month
COUNTIFSEOMONTH

Nightly hotel rate calculation

SUMPRODUCTFILTERINDEXMATCH

Normalize size units to Gigabytes

MATCHLEFTRIGHT
nth root of number
POWER

Odometer gas mileage log

SUMMAXMIN

One or the other not both

XOR

Pad a number with zeros

TEXTREPT

Random sort formula

INDEXMATCH

Range contains one of many substrings

SUMPRODUCTCOUNTIF

Range contains one of many values

SUMPRODUCTISNUMBERSEARCH

Range contains specific text

COUNTIF

Repeat fixed value every 3 months

MODDATEDIF

Return array with INDEX function

INDEXMATCHN

Reverse a list or range

INDEXCOUNTAROW

Risk Matrix Example

INDEXMATCH

Search entire worksheet for value

COUNTIF

Search multiple worksheets for value

COUNTIFINDIRECT
Send email with formula
HYPERLINK

Show formula text with formula

FORMULATEXTIFERRORISFORMULA

Simple currency conversion

VLOOKUPINDEXMATCH

Sort and extract unique values

MMULTTRANSPOSEINDEXMATCH

Sort numbers ascending or descending

SMALLLARGE

Sort text and numbers with formula

RANKCOUNTIFCOUNT

Split payment across months

AND

Square root of number

SQRTPOWER

Standard deviation calculation

STDEV.PSTDEV.SSTDEVSTDEVP

Student class enrollment with table

IFCOUNTIF

Sum every 3 cells

OFFSETCOLUMN

Sum text values like numbers

INDEXMATCHN

Transpose table without zeros

TRANSPOSEIF

Validate input with check mark

IFCOUNTIF
Value exists in a range
COUNTIFMATCH

Value is between two numbers

ANDMAXMIN

Value is within tolerance

IFABS

Volunteer hours requirement calculation

ANDCOUNTIFSUM

Excel formula from another source: yoda learning

Functions Excel Formulas Description

Returns the serial

number of a particular
DATE =DATE(year,month,day) date

Converts a date in the

form of text to a serial
DATEVALUE =DATEVALUE(date_text) number

Converts a serial
number to a day of the
DAY =DAY(serial_number) month

Converts a serial
HOUR =HOUR(serial_number) number to an hour
 Converts a serial
MINUTE =MINUTE(serial_number) number to a minute

Converts a serial
MONTH =MONTH(serial_number) number to a month

Returns the serial

number of the current
NOW =NOW() date and time

Converts a serial
SECOND =SECOND(serial_number) number to a second

Returns the serial

number of a particular
TIME =TIME(hour,minute,second) time

Converts a time in the

form of text to a serial
TIMEVALUE =TIMEVALUE(time_text) number

Returns the serial

TODAY =TODAY() number of today’s date

Converts a serial
YEAR =YEAR(serial_number) number to a year

Returns information
about the formatting,
location, or contents of a
CELL =CELL(info_type, [reference]) cell
 Returns TRUE if the
ISBLANK =ISBLANK(value) value is blank

Returns TRUE if the

ISERROR =ISERROR(value) value is any error value

Returns TRUE if the

ISNONTEXT =ISNONTEXT(value) value is not text

Returns TRUE if the

ISNUMBER =ISNUMBER(value) value is a number

Returns TRUE if the

ISTEXT =ISTEXT(value) value is text

Returns TRUE if all of its

AND =AND(logical1,logical2,…) arguments are TRUE

Returns the logical value

FALSE =FALSE FALSE

=IF(logical_test, [value_if_true], Specifies a logical test to

IF [value_if_false]) perform

Returns a value you

specify if a formula
evaluates to an error;
otherwise, returns the
IFERROR =IFERROR(value, value_if_error) result of the formula
 Reverses the logic of its
NOT =NOT(logical) argument

Returns TRUE if any

OR =OR(logical1,logical2,…) argument is TRUE

Returns the logical value

TRUE =TRUE TRUE

=ADDRESS(row_num, Returns a reference as

column_num, [abs_num], [a1], text to a single cell in a
ADDRESS [sheet_text]) worksheet

Returns the column

COLUMN =COLUMN([reference]) number of a reference

Returns the number of

COLUMNS =COLUMNS(array) columns in a reference

Looks in the top row of

=HLOOKUP(lookup_value,table_a an array and returns the
rray,row_index_num, value of the indicated
HLOOKUP [range_lookup]) cell

Uses an index to choose

=INDEX(array,row_num, a value from a reference
INDEX [column_num])  – 2 types or array

Returns a reference
INDIRECT =INDIRECT(ref_text,a1) indicated by a text value
 =LOOKUP(lookup_value, array)    Looks up values in a
LOOKUP – 2 types vector or array

=MATCH(lookup_value,lookup_ar Looks up values in a

MATCH ray,match_type) reference or array

Returns a reference
=OFFSET(reference,rows,cols,hei offset from a given
OFFSET ght,width) reference

Returns the row number

ROW =ROW([reference]) of a reference

Returns the number of

ROWS =ROWS(array) rows in a reference

Looks in the first column

=VLOOKUP(lookup_value,table_a of an array and moves
rray,col_index_num, across the row to return
VLOOKUP [range_lookup]) the value of a cell

Returns the absolute

ABS =ABS(number) value of a number

=PRODUCT(number1,number2,
PRODUCT …) Multiplies its arguments

Returns a random
number between 0 and
RAND =RAND() 1
 Returns a random
RANDBETWE number between the
EN =RANDBETWEEN(bottom,top) numbers you specify

Rounds a number to a
specified number of
ROUND =ROUND(number,num_digits) digits

ROUNDDOW =ROUNDDOWN(number,num_di Rounds a number down,

N gits) toward zero

Rounds a number up,

ROUNDUP =ROUNDUP(number,num_digits) away from zero

=SUBTOTAL(function_num,ref1, Returns a subtotal in a

SUBTOTAL …) list or database

SUM =SUM(number1,number2,…) Adds its arguments

=SUMIF(range,criteria, Adds the cells specified

SUMIF [sum_range]) by a given criteria

Adds the cells in a range

=SUMIFS(sum_range,criteria_ran that meet multiple
SUMIFS ge,criteria,…) criteria

Returns the sum of the

products of
SUMPRODUC =SUMPRODUCT(array1,array2, corresponding array
T [array3],…) components
 =AVERAGE(number1,number2, Returns the average of
AVERAGE …) its arguments

Returns the average

(arithmetic mean) of all
=AVERAGEIF(range,criteria, the cells in a range that
AVERAGEIF [average_range]) meet a given criteria

Counts how many

numbers are in the list
COUNT =COUNT(value1,value2,…) of arguments

Counts how many

values are in the list of
COUNTA =COUNTA(value1,value2,…) arguments

Counts the number of

COUNTBLAN blank cells within a
K =COUNTBLANK(range) range

Counts the number of

cells within a range that
COUNTIF =COUNTIF(range,criteria) meet the given criteria

Counts the number of

=COUNTIFS(criteria_range,criteri cells within a range that
COUNTIFS a,…) meet multiple criteria

Returns the maximum

value in a list of
MAX =MAX(number1,number2,…) arguments
 Returns the median of
MEDIAN =MEDIAN(number1,number2,…) the given numbers

Returns the minimum

value in a list of
MIN =MIN(number1,number2,…) arguments

Joins several text items

into one text item. 
CONCATENAT Easier to use ‘&’ instead
E =CONCATENATE(text1,text2,…) of the function usually.

Checks to see if two text

EXACT =EXACT(text1,text2) values are identical

Finds one text value

=FIND(find_text,within_text,start within another (case-
FIND _num) sensitive)

Returns the leftmost

characters from a text
LEFT =LEFT(text,num_chars) value

Returns the number of

characters in a text
LEN =LEN(text) string

Converts text to
LOWER =LOWER(text) lowercase

MID =MID(text,start_num,num_chars) Returns a specific

 number of characters
from a text string
starting at the position
you specify

Capitalizes the first

letter in each word of a
PROPER =PROPER(text) text value

=REPLACE(old_text,start_num,nu Replaces characters

REPLACE m_chars,new_text) within text

Returns the rightmost

characters from a text
RIGHT =RIGHT(text,num_chars) value

Finds one text value

=SEARCH(find_text,within_text,st within another (not
SEARCH art_num) case-sensitive)

Formats a number and

TEXT =TEXT(value,format_text) converts it to text

Removes spaces from

TRIM =TRIM(text) text

Converts text to
UPPER =UPPER(text) uppercase

DGET =DGET(database,field,criteria) Extracts from a

database a single record
 that matches the
specified criteria

Adds the numbers in the

field column of records
in the database that
DSUM =DSUM(database,field,criteria) match the criteria

Calculates the number

of days between two
=DAYS360(start_date,end_date, dates based on a 360-
DAYS360 method) day year

Returns the serial

number of the date that
is the indicated number
of months before or
EDATE =EDATE(start_date,months) after the start date

Returns the serial

number of the last day
of the month before or
after a specified number
EOMONTH =EOMONTH(start_date,months) of months

Returns the number of

NETWORKDA =NETWORKDAYS(start_date,end_ whole workdays
YS date,[holidays]) between two dates

NETWORKDA =NETWORKDAYS.INTL(start_date Returns the number of

YS.INTL ,end_date,[weekend],[holidays]) whole workdays
 between two dates
using parameters to
indicate which and how
many days are weekend
days

Converts a serial
=WEEKDAY(serial_number, number to a day of the
WEEKDAY [return_type]) week

Converts a serial
number to a number
representing where the
=WEEKNUM(serial_number, week falls numerically
WEEKNUM [return_type]) with a year

Returns the serial

number of the date
before or after a
=WORKDAY(start_date, days, specified number of
WORKDAY [holidays]) workdays

Returns the serial

number of the date
before or after a
specified number of
workdays using
parameters to indicate
WORKDAY.IN =WORKDAY.INTL(start_date,days which and how many
TL ,weekend,holidays) days are weekend days
 Returns the year
fraction representing
the number of whole
=YEARFRAC(start_date,end_date, days between start_date
YEARFRAC basis) and end_date

Converts a number from

=CONVERT(number,from_unit,to one measurement
CONVERT _unit) system to another

Tests whether two

DELTA =DELTA(number1,number2) values are equal

Returns the error

ERF =ERF(lower_limit,upper_limit) function

Returns the
complementary error
ERFC =ERFC(x) function

Tests whether a number

is greater than a
GESTEP =GESTEP(number,step) threshold value

Returns the depreciation

=AMORDEGRC(cost,date_purcha for each accounting
sed,first_period,salvage,period,r period by using a
AMORDEGRC ate,basis) depreciation coefficient

AMORLINC =AMORLINC(cost,date_purchase Returns the depreciation

d,first_period,salvage,period,rate for each accounting
 ,basis) period

Converts a dollar price,

expressed as a fraction,
into a dollar price,
=DOLLARDE(fractional_dollar,fra expressed as a decimal
DOLLARDE ction) number

Converts a dollar price,

expressed as a decimal
number, into a dollar
=DOLLARFR(decimal_dollar,fracti price, expressed as a
DOLLARFR on) fraction

Returns the straight-line

depreciation of an asset
SLN =SLN(cost,salvage,life) for one period

Returns the sum-of-

years’ digits depreciation
of an asset for a
SYD =SYD(cost,salvage,life,per) specified period

Returns a number
corresponding to an
ERROR.TYPE =ERROR.TYPE(error_val) error type

Returns information
about the current
INFO =INFO(type_text) operating environment
 Returns TRUE if the
value is any error value
ISERR =ISERR(value) except #N/A

Returns TRUE if the

ISEVEN =ISEVEN(number) number is even

Returns TRUE if the

ISLOGICAL =ISLOGICAL(value) value is a logical value

Returns TRUE if the

value is the #N/A error
ISNA =ISNA(value) value

Returns TRUE if the

ISODD =ISODD(number) number is odd

Returns TRUE if the

ISREF =ISREF(value) value is a reference

Returns a value
N =N(value) converted to a number

Returns the error value

NA =NA() #N/A

Returns a number
indicating the data type
TYPE =TYPE(value) of a value
 =CHOOSE(index_num,value1,val Chooses a value from a
CHOOSE ue2,…) list of values

GETPIVOTDA =GETPIVOTDATA(data_field,pivot Returns data stored in a

TA _table,field,item,…) PivotTable report

Creates a shortcut or
jump that opens a
document stored on a
=HYPERLINK(link_location,friendl network server, an
HYPERLINK y_name) intranet, or the Internet

Returns the transpose of

TRANSPOSE =TRANSPOSE(array) an array

Rounds a number to the

nearest integer or to the
nearest multiple of
CEILING =CEILING(number,significance) significance

Rounds a number the

nearest integer or to the
nearest multiple of
significance. Regardless
of the sign of the
CEILING.PREC =CEILING.PRECISE(number,signi number, the number is
ISE ficance) rounded up.

Rounds a number up to
EVEN =EVEN(number) the nearest even integer
 Returns e raised to the
power of a given
EXP =EXP(number) number

Returns the factorial of a

FACT =FACT(number) number

Rounds a number down,

FLOOR =FLOOR(number,significance) toward zero

Rounds a number the

nearest integer or to the
nearest multiple of
significance. Regardless
of the sign of the
FLOOR.PRECI =FLOOR.PRECISE(number,signific number, the number is
SE ance) rounded up.

Returns the greatest

GCD =GCD(number1,number2,…) common divisor

Rounds a number down

INT =INT(number) to the nearest integer

Returns a number that

is rounded up to the
nearest integer or to the
=ISO.CEILING(number,significanc nearest multiple of
ISO.CEILING e) significance

LCM =LCM(number1,number2,…) Returns the least

 common multiple

Returns the remainder

MOD =MOD(number,divisor) from division

Returns a number
rounded to the desired
MROUND =MROUND(number,multiple) multiple

Rounds a number up to
ODD =ODD(number) the nearest odd integer

PI =PI() Returns the value of pi

Returns the result of a

number raised to a
POWER =POWER(number,power) power

=QUOTIENT(numerator,denomin Returns the integer

QUOTIENT ator) portion of a division

Returns the sum of a

power series based on
SERIESSUM =SERIESSUM(x,n,m,coefficients) the formula

Returns the sign of a

SIGN =SIGN(number) number

Returns a positive
SQRT =SQRT(number) square root
 Returns the sum of the
squares of the
SUMSQ =SUMSQ(number1,number2,…) arguments

Truncates a number to
TRUNC =TRUNC(number,num_digits) an integer

Returns the average of

its arguments, including
numbers, text, and
AVERAGEA =AVERAGEA(value1,value2,…) logical values

Returns the average

(arithmetic mean) of all
=AVERAGEIFS(average_range,crit cells that meet multiple
AVERAGEIFS eria_range,criteria,…) criteria.

=GEOMEAN(number1,number2, Returns the geometric

GEOMEAN …) mean

=INTERCEPT(known_y’s,known_x’ Returns the intercept of

INTERCEPT s) the linear regression line

Returns the k-th largest

LARGE =LARGE(array,k) value in a data set

=LINEST(known_y’s,known_x’s,co Returns the parameters

LINEST nst,stats) of a linear trend

=LOGEST(known_y’s,known_x’s,c Returns the parameters

LOGEST onst,stats) of an exponential trend
 Returns the maximum
value in a list of
arguments, including
numbers, text, and
MAXA =MAXA(value1,value2,…) logical values

Returns the smallest

value in a list of
arguments, including
numbers, text, and
MINA =MINA(value1,value2,…) logical values

Returns a vertical array

of the most frequently
occurring, or repetitive
=MODE.MULT(number1,number values in an array or
MODE.MULT 2,…) range of data

Returns the most

=MODE.SNGL(number1,number common value in a data
MODE.SNGL 2,…) set

Returns the probability

=PROB(x_range,prob_range,lowe that values in a range
PROB r_limit,upper_limit) are between two limits

Returns the rank of a

number in a list of
RANK.AVG =RANK.AVG(number,ref,order) numbers

RANK.EQ =RANK.EQ(number,ref,order) Returns the rank of a

 number in a list of
numbers

Returns the skewness of

SKEW =SKEW(number1,number2,…) a distribution

Returns the slope of the

SLOPE =SLOPE(known_y’s,known_x’s) linear regression line

Returns the k-th

smallest value in a data
SMALL =SMALL(array,k) set

STANDARDIZ =STANDARDIZE(x,mean,standard Returns a normalized

E _dev) value

=TREND(known_y’s,known_x’s,ne Returns values along a

TREND w_x’s,const) linear trend

Returns the character

specified by the code
CHAR =CHAR(number) number

Removes all
nonprintable characters
CLEAN =CLEAN(text) from text

Returns a numeric code

for the first character in
CODE =CODE(text) a text string
 Converts a number to
text, using the $ (dollar)
DOLLAR =DOLLAR(number,decimals) currency format

Formats a number as
=FIXED(number,decimals,no_co text with a fixed number
FIXED mmas) of decimals

Extracts the phonetic

(furigana) characters
PHONETIC =PHONETIC(reference) from a text string

Repeats text a given

REPT =REPT(text,number_times) number of times

=SUBSTITUTE(text,old_text,new_t Substitutes new text for

SUBSTITUTE ext,instance_num) old text in a text string

Converts its arguments

T =T(value) to text

Converts a text
VALUE =VALUE(text) argument to a number

Returns the individual

=BINOMDIST(number_s,trials,pr term binomial
BINOMDIST obability_s,cumulative) distribution probability

Returns the one-tailed

probability of the chi-
CHIDIST =CHIDIST(x,deg_freedom) squared distribution
 Returns the inverse of
the one-tailed
=CHIINV(probability,deg_freedo probability of the chi-
CHIINV m) squared distribution

=CHITEST(actual_range,expected Returns the test for

CHITEST _range) independence

Returns the confidence

=CONFIDENCE(alpha,standard_d interval for a population
CONFIDENCE ev,size) mean

FTEST =FTEST(array1,array2)  

Returns the inverse of

=LOGINV(probability,mean,stand the lognormal
LOGINV ard_dev) cumulative distribution

LOGNORMDI =LOGNORMDIST(x,mean,standar Returns the cumulative

ST d_dev) lognormal distribution

Returns the most

common value in a data
MODE =MODE(number1,number2,…) set

=NORMDIST(x,mean,standard_d Returns the normal

NORMDIST ev,cumulative) cumulative distribution

Returns the inverse of

=NORMINV(probability,mean,sta the normal cumulative
NORMINV ndard_dev) distribution
 Returns the standard
normal cumulative
NORMSDIST =NORMSDIST(z) distribution

Returns the inverse of

the standard normal
NORMSINV =NORMSINV(probability) cumulative distribution

Returns the k-th

percentile of values in a
PERCENTILE =PERCENTILE(array,k) range

Returns the percentage

PERCENTRAN =PERCENTRANK(array,x,significa rank of a value in a data
K nce) set

Returns the Poisson

POISSON =POISSON(x,mean,cumulative) distribution

Returns the quartile of a

QUARTILE =QUARTILE(array,quart) data set

Returns the rank of a

number in a list of
RANK =RANK(number,ref,order) numbers

Estimates standard
deviation based on a
STDEV =STDEV(number1,number2,…) sample

STDEVP =STDEVP(number1,number2,…) Calculates standard

 deviation based on the
entire population

Returns the Student’s t-

TDIST =TDIST(x,deg_freedom,tails) distribution

Returns the inverse of

the Student’s t-
TINV =TINV(probability,deg_freedom) distribution

Estimates variance
VAR =VAR(number1,number2,…) based on a sample

Calculates variance
based on the entire
VARP =VARP(number1,number2,…) population

Returns the average of

=DAVERAGE(database,field,criter selected database
DAVERAGE ia) entries

Counts the cells that

=DCOUNT(database,field,criteria contain numbers in a
DCOUNT ) database

=DCOUNTA(database,field,criteri Counts nonblank cells in

DCOUNTA a) a database

Returns the maximum

value from selected
DMAX =DMAX(database,field,criteria) database entries
 Returns the minimum
value from selected
DMIN =DMIN(database,field,criteria) database entries

Multiplies the values in a

particular field of
=DPRODUCT(database,field,crite records that match the
DPRODUCT ria) criteria in a database

Estimates the standard

deviation based on a
sample of selected
DSTDEV =DSTDEV(database,field,criteria) database entries

Calculates the standard

deviation based on the
entire population of
=DSTDEVP(database,field,criteria selected database
DSTDEVP ) entries

Estimates variance
based on a sample from
selected database
DVAR =DVAR(database,field,criteria) entries

Calculates variance
based on the entire
population of selected
DVARP =DVARP(database,field,criteria) database entries

ERF.PRECISE =ERF.PRECISE(X) Returns the error

 function

Returns the
complementary ERF
function integrated
ERFC.PRECISE =ERFC.PRECISE(X) between x and infinity

Returns the depreciation

of an asset for a
specified period by
=DB(cost,salvage,life,period,mon using the fixed-declining
DB th) balance method

Returns the depreciation

of an asset for a
specified period by
using the double-
declining balance
=DDB(cost,salvage,life,period,fac method or some other
DDB tor) method that you specify

Returns the effective

EFFECT =EFFECT(nominal_rate,npery) annual interest rate

Returns the future value

FV =FV(rate,nper,pmt,pv,type) of an investment

Returns the interest

payment for an
investment for a given
IPMT =IPMT(rate,per,nper,pv,fv,type) period
 Returns the internal rate
of return for a series of
IRR =IRR(values,guess) cash flows

Returns the internal rate

of return where positive
and negative cash flows
=MIRR(values,finance_rate,reinv are financed at different
MIRR est_rate) rates

Returns the annual

NOMINAL =NOMINAL(effect_rate,npery) nominal interest rate

Returns the number of

periods for an
NPER =NPER(rate,pmt,pv,fv,type) investment

Returns the net present

value of an investment
based on a series of
periodic cash flows and
NPV =NPV(rate,value1,value2,…) a discount rate

Returns the present

PV =PV(rate,nper,pmt,fv,type) value of an investment

=RATE(nper,pmt,pv,fv,type,guess Returns the interest rate

RATE ) per period of an annuity

YIELD =YIELD(settlement,maturity,rate, Returns the yield on a

pr,redemption,frequency,basis) security that pays
 periodic interest

Returns the number of

AREAS =AREAS(reference) areas in a reference

Retrieves real-time data

from a program that
supports COM
automation
(Automation: A way to
work with an
application’s objects
from another
application or
development tool.
Formerly called OLE
Automation, Automation
is an industry standard
and a feature of the
=RTD(progID,server,topic1,topic2 Component Object
RTD ,…) Model (COM).)

=AGGREGATE(function_num,opti Returns an aggregate in

AGGREGATE ons,array,k) a list or database

Returns the number of

=COMBIN(number,number_chos combinations for a given
COMBIN en) number of objects

Returns the cosine of a

COS =COS(number) number
 Returns the hyperbolic
COSH =COSH(number) cosine of a number

Returns the double

FACTDOUBLE =FACTDOUBLE(number) factorial of a number

Returns the natural

LN =LN(number) logarithm of a number

Returns the logarithm of

a number to a specified
LOG =LOG(number,base) base

Returns the base-10

LOG10 =LOG10(number) logarithm of a number

MULTINOMIA =MULTINOMIAL(number1,numb Returns the multinomial

L er2,…) of a set of numbers

Returns the sine of the

SIN =SIN(number) given angle

Returns the hyperbolic

SINH =SINH(number) sine of a number

Returns the sum of the

difference of squares of
corresponding values in
SUMX2MY2 =SUMX2MY2(array_x,array_y) two arrays
 Returns the sum of the
sum of squares of
corresponding values in
SUMX2PY2 =SUMX2PY2(array_x,array_y) two arrays

Returns the sum of

squares of differences of
corresponding values in
SUMXMY2 =SUMXMY2(array_x,array_y) two arrays

Returns the tangent of a

TAN =TAN(number) number

Returns the hyperbolic

TANH =TANH(number) tangent of a number

Returns the inverse of

the standard normal
NORM.S.INV =NORM.S.INV(probability) cumulative distribution

Returns the average of

the absolute deviations
of data points from their
AVEDEV =AVEDEV(number1,number2,…) mean

Returns the beta

=BETA.DIST(x,alpha,beta,cumula cumulative distribution
BETA.DIST tive,A,B) function

BETA.INV =BETA.INV(probability,alpha,beta Returns the inverse of

,A,B) the cumulative
 distribution function for
a specified beta
distribution

Returns the individual

=BINOM.DIST(number_s,trials,pr term binomial
BINOM.DIST obability_s,cumulative) distribution probability

Returns the smallest

value for which the
cumulative binomial
distribution is less than
=BINOM.INV(trials,probability_s, or equal to a criterion
BINOM.INV alpha) value

Returns the cumulative

=CHISQ.DIST(x,deg_freedom,cu beta probability density
CHISQ.DIST mulative) function

Returns the one-tailed

CHISQ.DIST.R probability of the chi-
T =CHISQ.DIST.RT(x,deg_freedom) squared distribution

Returns the cumulative

=CHISQ.INV(probability,deg_free beta probability density
CHISQ.INV dom) function

Returns the inverse of

the one-tailed
=CHISQ.INV.RT(probability,deg_f probability of the chi-
CHISQ.INV.RT reedom) squared distribution
 =CHISQ.TEST(actual_range,expec Returns the test for
CHISQ.TEST ted_range) independence

Returns the confidence

CONFIDENCE. =CONFIDENCE.NORM(alpha,stan interval for a population
NORM dard_dev,size) mean

Returns the confidence

interval for a population
CONFIDENCE. =CONFIDENCE.T(alpha,standard_ mean, using a Student’s
T dev,size) t distribution

Returns the correlation

coefficient between two
CORREL =CORREL(array1,array2) data sets

Returns covariance, the

COVARIANCE. average of the products
P =COVARIANCE.P(array1,array2) of paired deviations

Returns the sample

covariance, the average
of the products
deviations for each data
COVARIANCE. point pair in two data
S =COVARIANCE.S(array1,array2) sets

Returns the sum of

DEVSQ =DEVSQ(number1,number2,…) squares of deviations

EXPON.DIST =EXPON.DIST(x,lambda,cumulati Returns the exponential

 ve) distribution

=F.DIST(x,deg_freedom1,deg_fre Returns the F probability

F.DIST edom2,cumulative) distribution

=F.DIST.RT(x,deg_freedom1,deg_ Returns the F probability

F.DIST.RT freedom2) distribution

Returns the inverse of

=F.INV(probability,deg_freedom1 the F probability
F.INV ,deg_freedom2) distribution

Returns the inverse of

=F.INV.RT(probability,deg_freedo the F probability
F.INV.RT m1,deg_freedom2) distribution

Returns the result of an

F.TEST =F.TEST(array1,array2) F-test

Returns the inverse of

=FINV(probability,deg_freedom1, the F probability
FINV deg_freedom2) distribution

Returns the Fisher

FISHER =FISHER(x) transformation

Returns the inverse of

the Fisher
FISHERINV =FISHERINV(y) transformation
 =FORECAST(x,known_y’s,known_ Returns a value along a
FORECAST x’s) linear trend

Returns a frequency
=FREQUENCY(data_array,bins_ar distribution as a vertical
FREQUENCY ray) array

=GAMMA.DIST(x,alpha,beta,cum Returns the gamma

GAMMA.DIST ulative) distribution

Returns the inverse of

=GAMMA.INV(probability,alpha,b the gamma cumulative
GAMMA.INV eta) distribution

Returns the natural

logarithm of the gamma
GAMMALN =GAMMALN(x) function, Γ(x)

Returns the natural

GAMMALN.P logarithm of the gamma
RECISE =GAMMALN.PRECISE(x) function, Γ(x)

=GROWTH(known_y’s,known_x’s, Returns values along an

GROWTH new_x’s,const) exponential trend

=HARMEAN(number1,number2, Returns the harmonic

HARMEAN …) mean

=HYPGEOM.DIST(sample_s,num Returns the

HYPGEOM.DI ber_sample,population_s,numbe hypergeometric
ST r_pop,cumulative) distribution
 Returns the kurtosis of a
KURT =KURT(number1,number2,…) data set

LOGNORM.DI =LOGNORM.DIST(x,mean,standa Returns the cumulative

ST rd_dev,cumulative) lognormal distribution

Returns the inverse of

LOGNORM.IN =LOGNORM.INV(probability,mea the lognormal
V n,standard_dev) cumulative distribution

NEGBINOM.D =NEGBINOM.DIST(number_f,nu Returns the negative

IST mber_s,probability_s,cumulative) binomial distribution

=NORM.DIST(x,mean,standard_d Returns the normal

NORM.DIST ev,cumulative) cumulative distribution

Returns the inverse of

=NORM.INV(probability,mean,st the normal cumulative
NORM.INV andard_dev) distribution

Returns the standard

normal cumulative
NORM.S.DIST =NORM.S.DIST(z,cumulative) distribution

Returns the Pearson

product moment
PEARSON =PEARSON(array1,array2) correlation coefficient

PERCENTILE.E =PERCENTILE.EXC(array,k) Returns the k-th

XC percentile of values in a
range, where k is in the
 range 0..1, exclusive

Returns the k-th

PERCENTILE.I percentile of values in a
NC =PERCENTILE.INC(array,k) range

Returns the rank of a

value in a data set as a
PERCENTRAN =PERCENTRANK.EXC(array,x,sign percentage (0..1,
K.EXC ificance) exclusive) of the data set

Returns the percentage

PERCENTRAN =PERCENTRANK.INC(array,x,signi rank of a value in a data
K.INC ficance) set

Returns the number of

=PERMUT(number,number_chos permutations for a given
PERMUT en) number of objects

POISSON.DIS =POISSON.DIST(x,mean,cumulati Returns the Poisson

T ve) distribution

Returns the quartile of

the data set, based on
QUARTILE.EX percentile values from
C =QUARTILE.EXC(array,quart) 0..1, exclusive

QUARTILE.IN Returns the quartile of a

C =QUARTILE.INC(array,quart) data set

RSQ =RSQ(known_y’s,known_x’s) Returns the square of

 the Pearson product
moment correlation
coefficient

Calculates standard
deviation based on the
STDEV.P =STDEV.P(number1,number2,…) entire population

Estimates standard
deviation based on a
STDEV.S =STDEV.S(number1,number2,…) sample

Estimates standard
deviation based on a
sample, including
numbers, text, and
STDEVA =STDEVA(value1,value2,…) logical values

Calculates standard
deviation based on the
entire population,
including numbers, text,
STDEVPA =STDEVPA(value1,value2,…) and logical values

Returns the standard

error of the predicted y-
value for each x in the
STEYX =STEYX(known_y’s,known_x’s) regression

T.DIST =T.DIST(x,deg_freedom,cumulati Returns the Percentage

ve) Points (probability) for
 the Student t-
distribution

Returns the Percentage

Points (probability) for
the Student t-
T.DIST.2T =T.DIST.2T(x,deg_freedom) distribution

Returns the Student’s t-

T.DIST.RT =T.DIST.RT(x,deg_freedom) distribution

Returns the t-value of

the Student’s t-
distribution as a
function of the
probability and the
T.INV =T.INV(probability,deg_freedom) degrees of freedom

Returns the inverse of

=T.INV.2T(probability,deg_freedo the Student’s t-
T.INV.2T m) distribution

Returns the probability

associated with a
T.TEST =T.TEST(array1,array2,tails,type) Student’s t-test

Returns the mean of the

TRIMMEAN =TRIMMEAN(array,percent) interior of a data set

VAR.P =VAR.P(number1,number2,…) Calculates variance

based on the entire
 population

Estimates variance
VAR.S =VAR.S(number1,number2,…) based on a sample

Estimates variance
based on a sample,
including numbers, text,
VARA =VARA(value1,value2,…) and logical values

Calculates variance
based on the entire
population, including
numbers, text, and
VARPA =VARPA(value1,value2,…) logical values

WEIBULL.DIS =WEIBULL.DIST(x,alpha,beta,cu Returns the Weibull

T mulative) distribution

Returns the one-tailed

probability-value of a z-
Z.TEST =Z.TEST(array,x,sigma) test

Changes full-width
(double-byte) English
letters or katakana
within a character string
to half-width (single-
ASC =ASC(text) byte) characters

BETADIST =BETADIST(x,alpha,beta,A,B) Returns the beta

 cumulative distribution
function

Returns the inverse of

the cumulative
distribution function for
=BETAINV(probability,alpha,beta, a specified beta
BETAINV A,B) distribution

Returns covariance, the

average of the products
COVAR =COVAR(array1,array2) of paired deviations

Returns the smallest

value for which the
cumulative binomial
distribution is less than
=CRITBINOM(trials,probability_s, or equal to a criterion
CRITBINOM alpha) value

=EXPONDIST(x,lambda,cumulativ Returns the exponential

EXPONDIST e) distribution

=FDIST(x,deg_freedom1,deg_free Returns the F probability

FDIST dom2) distribution

=GAMMADIST(x,alpha,beta,cumu Returns the gamma

GAMMADIST lative) distribution

GAMMAINV =GAMMAINV(probability,alpha,b Returns the inverse of

eta) the gamma cumulative
 distribution

=HYPGEOMDIST(sample_s,numb Returns the

HYPGEOMDIS er_sample,population_s,number hypergeometric
T _pop) distribution

NEGBINOMDI =NEGBINOMDIST(number_f,num Returns the negative

ST ber_s,probability_s) binomial distribution

Returns the probability

associated with a
TTEST =TTEST(array1,array2,tails,type) Student’s t-test

Calculates variance
based on the entire
population, including
=WEIBULL(x,alpha,beta,cumulati numbers, text, and
WEIBULL ve) logical values

Returns the one-tailed

probability-value of a z-
ZTEST =ZTEST(array,x,sigma) test

Returns the modified

BESSELI =BESSELI(x,n) Bessel function In(x)

Returns the Bessel

BESSELJ =BESSELJ(x,n) function Jn(x)

Returns the modified

BESSELK =BESSELK(x,n) Bessel function Kn(x)
 Returns the Bessel
BESSELY =BESSELY(x,n) function Yn(x)

Converts a binary
BIN2DEC =BIN2DEC(number) number to decimal

Converts a binary
BIN2HEX =BIN2HEX(number,places) number to hexadecimal

Converts a binary
BIN2OCT =BIN2OCT(number,places) number to octal

Converts real and

=COMPLEX(real_num,i_num,suffi imaginary coefficients
COMPLEX x) into a complex number

Converts a decimal
DEC2BIN =DEC2BIN(number,places) number to binary

Converts a decimal
DEC2HEX =DEC2HEX(number,places) number to hexadecimal

Converts a decimal
DEC2OCT =DEC2OCT(number,places) number to octal

Converts a hexadecimal
HEX2BIN =HEX2BIN(number,places) number to binary

Converts a hexadecimal
HEX2DEC =HEX2DEC(number) number to decimal
 Converts a hexadecimal
HEX2OCT =HEX2OCT(number,places) number to octal

Returns the absolute

value (modulus) of a
IMABS =IMABS(inumber) complex number

Returns the imaginary

coefficient of a complex
IMAGINARY =IMAGINARY(inumber) number

Returns the argument

IMARGUMEN theta, an angle
T =IMARGUMENT(inumber) expressed in radians

Returns the complex

IMCONJUGAT conjugate of a complex
E =IMCONJUGATE(inumber) number

Returns the cosine of a

IMCOS =IMCOS(inumber) complex number

Returns the quotient of

IMDIV =IMDIV(inumber1,inumber2) two complex numbers

Returns the exponential

IMEXP =IMEXP(inumber) of a complex number

Returns the natural

logarithm of a complex
IMLN =IMLN(inumber) number
 Returns the base-10
logarithm of a complex
IMLOG10 =IMLOG10(inumber) number

Returns the base-2

logarithm of a complex
IMLOG2 =IMLOG2(inumber) number

Returns a complex
number raised to an
IMPOWER =IMPOWER(inumber,number) integer power

=IMPRODUCT(inumber1,inumbe Returns the product of

IMPRODUCT r2,…) complex numbers

Returns the real

coefficient of a complex
IMREAL =IMREAL(inumber) number

Returns the sine of a

IMSIN =IMSIN(inumber) complex number

Returns the square root

IMSQRT =IMSQRT(inumber) of a complex number

Returns the difference

between two complex
IMSUB =IMSUB(inumber1,inumber2) numbers

Returns the sum of

IMSUM =IMSUM(inumber1,inumber2,…) complex numbers
 Converts an octal
OCT2BIN =OCT2BIN(number,places) number to binary

Converts an octal
OCT2DEC =OCT2DEC(number) number to decimal

Converts an octal
OCT2HEX =OCT2HEX(number,places) number to hexadecimal

Returns the accrued

=ACCRINT(issue,first_interest,set interest for a security
tlement,rate,par,frequency,basis that pays periodic
ACCRINT ,calc_method) interest

Returns the accrued

interest for a security
=ACCRINTM(issue,settlement,rat that pays interest at
ACCRINTM e,par,basis) maturity

Returns the number of

days from the beginning
=COUPDAYBS(settlement,maturi of the coupon period to
COUPDAYBS ty,frequency,basis) the settlement date

Returns the number of

days in the coupon
=COUPDAYS(settlement,maturity period that contains the
COUPDAYS ,frequency,basis) settlement date

COUPDAYSN =COUPDAYSNC(settlement,matu Returns the number of

C rity,frequency,basis) days from the
 settlement date to the
next coupon date

Returns the next coupon

=COUPNCD(settlement,maturity, date after the
COUPNCD frequency,basis) settlement date

Returns the number of

coupons payable
=COUPNUM(settlement,maturity between the settlement
COUPNUM ,frequency,basis) date and maturity date

Returns the previous

=COUPPCD(settlement,maturity, coupon date before the
COUPPCD frequency,basis) settlement date

Returns the cumulative

=CUMIPMT(rate,nper,pv,start_pe interest paid between
CUMIPMT riod,end_period,type) two periods

Returns the cumulative

=CUMPRINC(rate,nper,pv,start_p principal paid on a loan
CUMPRINC eriod,end_period,type) between two periods

=DISC(settlement,maturity,pr,re Returns the discount

DISC demption,basis) rate for a security

Returns the annual

duration of a security
=DURATION(settlement,maturity with periodic interest
DURATION ,coupon,yld,frequency,basis) payments
 Returns the future value
of an initial principal
=FVSCHEDULE(principal,schedul after applying a series of
FVSCHEDULE e) compound interest rates

Returns the interest rate

=INTRATE(settlement,maturity,in for a fully invested
INTRATE vestment,redemption,basis) security

Calculates the interest

paid during a specific
ISPMT =ISPMT(rate,per,nper,pv) period of an investment

Returns the Macauley

modified duration for a
security with an
=MDURATION(settlement,maturi assumed par value of
MDURATION ty,coupon,yld,frequency,basis) $100

Returns the price per

=ODDFPRICE(settlement,maturit $100 face value of a
y,issue,first_coupon,rate,yld,rede security with an odd first
ODDFPRICE mption,frequency,basis) period

=ODDFYIELD(settlement,maturit Returns the yield of a

y,issue,first_coupon,rate,pr,rede security with an odd first
ODDFYIELD mption,frequency,basis) period

ODDLPRICE =ODDLPRICE(settlement,maturit Returns the price per

y,last_interest,rate,yld,redemptio $100 face value of a
n,frequency,basis) security with an odd last
 period

=ODDLYIELD(settlement,maturit Returns the yield of a

y,last_interest,rate,pr,redemptio security with an odd last
ODDLYIELD n,frequency,basis) period

Returns the periodic

PMT =PMT(rate,nper,pv,fv,type) payment for an annuity

Returns the payment on

the principal for an
investment for a given
PPMT =PPMT(rate,per,nper,pv,fv,type) period

Returns the price per

$100 face value of a
=PRICE(settlement,maturity,rate, security that pays
PRICE yld,redemption,frequency,basis) periodic interest

Returns the price per

=PRICEDISC(settlement,maturity, $100 face value of a
PRICEDISC discount,redemption,basis) discounted security

Returns the price per

$100 face value of a
=PRICEMAT(settlement,maturity, security that pays
PRICEMAT issue,rate,yld,basis) interest at maturity

Returns the amount

=RECEIVED(settlement,maturity,i received at maturity for
RECEIVED nvestment,discount,basis) a fully invested security
 Returns the bond-
=TBILLEQ(settlement,maturity,di equivalent yield for a
TBILLEQ scount) Treasury bill

Returns the price per

=TBILLPRICE(settlement,maturity $100 face value for a
TBILLPRICE ,discount) Treasury bill

=TBILLYIELD(settlement,maturity Returns the yield for a

TBILLYIELD ,pr) Treasury bill

Returns the depreciation

of an asset for a
specified or partial
period by using a
=VDB(cost,salvage,life,start_peri declining balance
VDB od,end_period,factor,no_switch) method

Returns the internal rate

of return for a schedule
of cash flows that is not
XIRR =XIRR(values,dates,guess) necessarily periodic

Returns the net present

value for a schedule of
cash flows that is not
XNPV =XNPV(rate,values,dates) necessarily periodic

YIELDDISC =YIELDDISC(settlement,maturity, Returns the annual yield

pr,redemption,basis) for a discounted
security; for example, a
 Treasury bill

Returns the annual yield

=YIELDMAT(settlement,maturity,i of a security that pays
YIELDMAT ssue,rate,pr,basis) interest at maturity

Returns the arccosine of

ACOS =ACOS(number) a number

Returns the inverse

hyperbolic cosine of a
ACOSH =ACOSH(number) number

Returns the arcsine of a

ASIN =ASIN(number) number

Returns the inverse

hyperbolic sine of a
ASINH =ASINH(number) number

Returns the arctangent

ATAN =ATAN(number) of a number

Returns the arctangent

from x- and y-
ATAN2 =ATAN2(x_num,y_num) coordinates

Returns the inverse

hyperbolic tangent of a
ATANH =ATANH(number) number
 Converts radians to
DEGREES =DEGREES(angle) degrees

Returns the matrix

MDETERM =MDETERM(array) determinant of an array

Returns the matrix

MINVERSE =MINVERSE(array) inverse of an array

Returns the matrix

MMULT =MMULT(array1,array2) product of two arrays

Converts degrees to
RADIANS =RADIANS(angle) radians

Converts an arabic
numeral to roman, as
ROMAN =ROMAN(number,form) text

Returns the square root

SQRTPI =SQRTPI(number) of (number * pi)

Converts a number to
text, using the ß (baht)
BAHTTEXT =BAHTTEXT(number) currency format

CUBEKPIMEM =CUBEKPIMEMBER(connection,k Returns a key

BER pi_name,kpi_property,caption) performance indicator
(KPI) name, property,
and measure, and
displays the name and
 property in the cell. A
KPI is a quantifiable
measurement, such as
monthly gross profit or
quarterly employee
turnover, used to
monitor an
organization’s
performance.

Returns a member or
tuple in a cube
hierarchy. Use to
validate that the
CUBEMEMBE =CUBEMEMBER(connection,me member or tuple exists
R mber_expression,caption) in the cube.

Returns the value of a

member property in the
cube. Use to validate
that a member name
exists within the cube
=CUBEMEMBERPROPERTY(conn and to return the
CUBEMEMBE ection,member_expression,prop specified property for
RPROPERTY erty) this member.

CUBERANKED =CUBERANKEDMEMBER(connect Returns the nth, or

MEMBER ion,set_expression,rank,caption) ranked, member in a
set. Use to return one or
more elements in a set,
such as the top sales
performer or top 10
 students.

Defines a calculated set

of members or tuples by
sending a set expression
to the cube on the
server, which creates
the set, and then returns
=CUBESET(connection,set_expre that set to Microsoft
CUBESET ssion,caption,sort_order,sort_by) Office Excel.

CUBESETCOU Returns the number of

NT =CUBESETCOUNT(set) items in a set.

=CUBEVALUE(connection,memb Returns an aggregated

CUBEVALUE er_expression1,…) value from a cube.

Functions Excel Formulas Description

DATE =DATE(year,month,day) Returns the serial number of a particular date

1. DATEVALUE =DATEVALUE(date_text) Converts a date in the form of text to a serial

number
2. DAY =DAY(serial_number) Converts a serial number to a day of the month
3. HOUR =HOUR(serial_number) Converts a serial number to an hour
4. MINUTE =MINUTE(serial_number) Converts a serial number to a minute
5. MONTH =MONTH(serial_number) Converts a serial number to a month
6. NOW =NOW() Returns the serial number of the current date and time
7. SECOND =SECOND(serial_number) Converts a serial number to a second
8. TIME =TIME(hour,minute,second) Returns the serial number of a particular time
9. TIMEVALUE =TIMEVALUE(time_text) Converts a time in the form of text to a serial
number
10. TODAY =TODAY() Returns the serial number of today’s date
11. YEAR =YEAR(serial_number) Converts a serial number to a year
12. CELL =CELL(info_type, [reference]) Returns information about the formatting, location, or
contents of a cell
13. ISBLANK =ISBLANK(value) Returns TRUE if the value is blank
14. ISERROR =ISERROR(value) Returns TRUE if the value is any error value
15. ISNONTEXT =ISNONTEXT(value) Returns TRUE if the value is not text
16. ISNUMBER =ISNUMBER(value) Returns TRUE if the value is a number
17. ISTEXT =ISTEXT(value) Returns TRUE if the value is text
18. AND =AND(logical1,logical2,…) Returns TRUE if all of its arguments are TRUE
19. FALSE =FALSE Returns the logical value FALSE
20. IF =IF(logical_test, [value_if_true], [value_if_false]) Specifies a logical test to
perform
21. IFERROR =IFERROR(value, value_if_error)Returns a value you specify if a formula
evaluates to an error; otherwise, returns the result of the formula
22. NOT =NOT(logical) Reverses the logic of its argument
23. OR =OR(logical1,logical2,…) Returns TRUE if any argument is TRUE
24. TRUE =TRUE Returns the logical value TRUE
25. ADDRESS =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Returns
a reference as text to a single cell in a worksheet
26. COLUMN =COLUMN([reference]) Returns the column number of a reference
27. COLUMNS =COLUMNS(array) Returns the number of columns in a reference
28. HLOOKUP =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) Looks
in the top row of an array and returns the value of the indicated cell
29. INDEX =INDEX(array,row_num,[column_num]) – 2 types Uses an index to choose a value
from a reference or array
30. INDIRECT =INDIRECT(ref_text,a1) Returns a reference indicated by a text value
31. LOOKUP =LOOKUP(lookup_value, array) – 2 types Looks up values in a vector or
array
32. MATCH =MATCH(lookup_value,lookup_array,match_type) Looks up values in a reference
or array
33. OFFSET =OFFSET(reference,rows,cols,height,width) Returns a reference offset from a given
reference
34. ROW =ROW([reference]) Returns the row number of a reference
35. ROWS =ROWS(array) Returns the number of rows in a reference
36. VLOOKUP =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) Looks
in the first column of an array and moves across the row to return the value of a cell
37. ABS =ABS(number) Returns the absolute value of a number
38. PRODUCT =PRODUCT(number1,number2,…) Multiplies its arguments
39. RAND =RAND() Returns a random number between 0 and 1
40. RANDBETWEEN =RANDBETWEEN(bottom,top) Returns a random number between the
numbers you specify
41. ROUND =ROUND(number,num_digits) Rounds a number to a specified number of digits
42. ROUNDDOWN =ROUNDDOWN(number,num_digits) Rounds a number down, toward zero
43. ROUNDUP =ROUNDUP(number,num_digits) Rounds a number up, away from zero
44. SUBTOTAL =SUBTOTAL(function_num,ref1,…) Returns a subtotal in a list or database
45. SUM =SUM(number1,number2,…) Adds its arguments
46. SUMIF =SUMIF(range,criteria,[sum_range]) Adds the cells specified by a given criteria
47. SUMIFS =SUMIFS(sum_range,criteria_range,criteria,…) Adds the cells in a range that meet
multiple criteria
48. SUMPRODUCT =SUMPRODUCT(array1,array2,[array3],…) Returns the sum of the
products of corresponding array components
49. AVERAGE =AVERAGE(number1,number2,…) Returns the average of its arguments
50. AVERAGEIF =AVERAGEIF(range,criteria,[average_range]) Returns the average (arithmetic
mean) of all the cells in a range that meet a given criteria
51. COUNT =COUNT(value1,value2,…) Counts how many numbers are in the list of arguments
52. COUNTA =COUNTA(value1,value2,…) Counts how many values are in the list of
arguments
53. COUNTBLANK =COUNTBLANK(range) Counts the number of blank cells within a range
54. COUNTIF =COUNTIF(range,criteria) Counts the number of cells within a range that
meet the given criteria
55. COUNTIFS =COUNTIFS(criteria_range,criteria,…) Counts the number of cells within a
range that meet multiple criteria
56. MAX =MAX(number1,number2,…) Returns the maximum value in a list of arguments
57. MEDIAN =MEDIAN(number1,number2,…) Returns the median of the given
numbers
58. MIN =MIN(number1,number2,…) Returns the minimum value in a list of arguments
59. CONCATENATE =CONCATENATE(text1,text2,…) Joins several text items into one text item.
Easier to use ‘&’ instead of the function usually.
60. EXACT =EXACT(text1,text2) Checks to see if two text values are identical
61. FIND =FIND(find_text,within_text,start_num) Finds one text value within another (case-
sensitive)
62. LEFT =LEFT(text,num_chars) Returns the leftmost characters from a text value
63. LEN =LEN(text) Returns the number of characters in a text string
64. LOWER =LOWER(text) Converts text to lowercase
65. MID =MID(text,start_num,num_chars) Returns a specific number of characters from a
text string starting at the position you specify
66. PROPER =PROPER(text) Capitalizes the first letter in each word of a text value
67. REPLACE =REPLACE(old_text,start_num,num_chars,new_text) Replaces characters
within text
68. RIGHT =RIGHT(text,num_chars) Returns the rightmost characters from a text value
69. SEARCH=SEARCH(find_text,within_text,start_num) Finds one text value within another (not
case-sensitive)
70. TEXT =TEXT(value,format_text) Formats a number and converts it to text
71. TRIM =TRIM(text) Removes spaces from text
72. UPPER =UPPER(text) Converts text to uppercase
73. DGET =DGET(database,field,criteria) Extracts from a database a single record that matches
the specified criteria
74. DSUM =DSUM(database,field,criteria) Adds the numbers in the field column of records in the
database that match the criteria
75. DAYS360 =DAYS360(start_date,end_date,method) Calculates the number of days
between two dates based on a 360-day year
76. EDATE =EDATE(start_date,months) Returns the serial number of the date that is the
indicated number of months before or after the start date
77. EOMONTH =EOMONTH(start_date,months) Returns the serial number of the last
day of the month before or after a specified number of months
78. NETWORKDAYS =NETWORKDAYS(start_date,end_date,[holidays]) Returns the number of
whole workdays between two dates
79. NETWORKDAYS.INTL =NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])
Returns the number of whole workdays between two dates using parameters to
indicate which and how many days are weekend days
80. WEEKDAY =WEEKDAY(serial_number,[return_type]) Converts a serial number to a
day of the week
81. WEEKNUM =WEEKNUM(serial_number,[return_type]) Converts a serial number to a
number representing where the week falls numerically with a year
82. WORKDAY =WORKDAY(start_date, days, [holidays]) Returns the serial number of
the date before or after a specified number of workdays
83. WORKDAY.INTL =WORKDAY.INTL(start_date,days,weekend,holidays) Returns the serial
number of the date before or after a specified number of workdays using parameters to indicate
which and how many days are weekend days
84. YEARFRAC =YEARFRAC(start_date,end_date,basis) Returns the year fraction representing
the number of whole days between start_date and end_date
85. CONVERT =CONVERT(number,from_unit,to_unit) Converts a number from one
measurement system to another
86. DELTA =DELTA(number1,number2) Tests whether two values are equal
87. ERF =ERF(lower_limit,upper_limit) Returns the error function
88. ERFC =ERFC(x) Returns the complementary error function
89. GESTEP =GESTEP(number,step) Tests whether a number is greater than a threshold value
90. AMORDEGRC =AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,basis)
Returns the depreciation for each accounting period by using a depreciation coefficient
91. AMORLINC =AMORLINC(cost,date_purchased,first_period,salvage,period,rate,basis)Returns
the depreciation for each accounting period
92. DOLLARDE =DOLLARDE(fractional_dollar,fraction) Converts a dollar price, expressed as a
fraction, into a dollar price, expressed as a decimal number
93. DOLLARFR =DOLLARFR(decimal_dollar,fraction) Converts a dollar price, expressed as a
decimal number, into a dollar price, expressed as a fraction
94. SLN =SLN(cost,salvage,life) Returns the straight-line depreciation of an asset for one period
95. SYD =SYD(cost,salvage,life,per) Returns the sum-of-years’ digits depreciation of an
asset for a specified period
96. ERROR.TYPE =ERROR.TYPE(error_val) Returns a number corresponding to an error
type
97. INFO =INFO(type_text) Returns information about the current operating environment
98. ISERR =ISERR(value) Returns TRUE if the value is any error value except #N/A
99. ISEVEN =ISEVEN(number) Returns TRUE if the number is even
100. ISLOGICAL =ISLOGICAL(value) Returns TRUE if the value is a logical value
101. ISNA =ISNA(value) Returns TRUE if the value is the #N/A error value
102. ISODD =ISODD(number) Returns TRUE if the number is odd
103. ISREF =ISREF(value) Returns TRUE if the value is a reference
104. N =N(value) Returns a value converted to a number
105. NA =NA() Returns the error value #N/A
106. TYPE =TYPE(value) Returns a number indicating the data type of a value
107. CHOOSE =CHOOSE(index_num,value1,value2,…) Chooses a value from a list of
values
108. GETPIVOTDATA =GETPIVOTDATA(data_field,pivot_table,field,item,…) Returns data
stored in a PivotTable report
109. HYPERLINK =HYPERLINK(link_location,friendly_name) Creates a shortcut or
jump that opens a document stored on a network server, an intranet, or the Internet
110. TRANSPOSE =TRANSPOSE(array) Returns the transpose of an array
111. CEILING=CEILING(number,significance) Rounds a number to the nearest integer or to
the nearest multiple of significance
112. CEILING.PRECISE =CEILING.PRECISE(number,significance) Rounds a number the
nearest integer or to the nearest multiple of significance. Regardless of the sign of the number,
the number is rounded up.
113. EVEN =EVEN(number)Rounds a number up to the nearest even integer
114. EXP =EXP(number) Returns e raised to the power of a given number
115. FACT =FACT(number) Returns the factorial of a number
116. FLOOR =FLOOR(number,significance) Rounds a number down, toward zero
117. FLOOR.PRECISE =FLOOR.PRECISE(number,significance) Rounds a number the nearest
integer or to the nearest multiple of significance. Regardless of the sign of the number, the
number is rounded up.
118. GCD =GCD(number1,number2,…) Returns the greatest common divisor
119. INT =INT(number) Rounds a number down to the nearest integer
120. ISO.CEILING =ISO.CEILING(number,significance) Returns a number that is
rounded up to the nearest integer or to the nearest multiple of significance
121. LCM =LCM(number1,number2,…) Returns the least common multiple
122. MOD =MOD(number,divisor) Returns the remainder from division
123. MROUND =MROUND(number,multiple) Returns a number rounded to the
desired multiple
124. ODD =ODD(number) Rounds a number up to the nearest odd integer
125. PI =PI() Returns the value of pi
126. POWER =POWER(number,power) Returns the result of a number raised to a
power
127. QUOTIENT =QUOTIENT(numerator,denominator) Returns the integer portion of a
division
128. SERIESSUM =SERIESSUM(x,n,m,coefficients) Returns the sum of a power series
based on the formula
129. SIGN =SIGN(number) Returns the sign of a number
130. SQRT =SQRT(number)Returns a positive square root
131. SUMSQ =SUMSQ(number1,number2,…) Returns the sum of the squares of the
arguments
132. TRUNC =TRUNC(number,num_digits) Truncates a number to an integer
133. AVERAGEA =AVERAGEA(value1,value2,…) Returns the average of its arguments,
including numbers, text, and logical values
134. AVERAGEIFS =AVERAGEIFS(average_range,criteria_range,criteria,…) Returns the
average (arithmetic mean) of all cells that meet multiple criteria.
135. GEOMEAN =GEOMEAN(number1,number2,…) Returns the geometric mean
136. INTERCEPT =INTERCEPT(known_y’s,known_x’s) Returns the intercept of the
linear regression line
137. LARGE =LARGE(array,k) Returns the k-th largest value in a data set
138. LINEST =LINEST(known_y’s,known_x’s,const,stats) Returns the parameters of a
linear trend
139. LOGEST =LOGEST(known_y’s,known_x’s,const,stats) Returns the parameters of an
exponential trend
140. MAXA =MAXA(value1,value2,…) Returns the maximum value in a list of
arguments, including numbers, text, and logical values
141. MINA =MINA(value1,value2,…) Returns the smallest value in a list of
arguments, including numbers, text, and logical values
142. MODE.MULT =MODE.MULT(number1,number2,…) Returns a vertical array of the
most frequently occurring, or repetitive values in an array or range of data
143. MODE.SNGL =MODE.SNGL(number1,number2,…) Returns the most common
value in a data set
144. PROB =PROB(x_range,prob_range,lower_limit,upper_limit) Returns the probability
that values in a range are between two limits
145. RANK.AVG =RANK.AVG(number,ref,order) Returns the rank of a number in a list of
numbers
146. RANK.EQ =RANK.EQ(number,ref,order) Returns the rank of a number in a list of
numbers
147. SKEW =SKEW(number1,number2,…) Returns the skewness of a distribution
148. SLOPE =SLOPE(known_y’s,known_x’s) Returns the slope of the linear regression line
149. SMALL =SMALL(array,k) Returns the k-th smallest value in a data set
150. STANDARDIZE =STANDARDIZE(x,mean,standard_dev) Returns a normalized value
151. TREND =TREND(known_y’s,known_x’s,new_x’s,const) Returns values along a linear
trend
152. CHAR =CHAR(number) Returns the character specified by the code number
153. CLEAN =CLEAN(text) Removes all nonprintable characters from text
154. CODE =CODE(text) Returns a numeric code for the first character in a text string
155. DOLLAR=DOLLAR(number,decimals) Converts a number to text, using the $ (dollar)
currency format
156. FIXED =FIXED(number,decimals,no_commas) Formats a number as text with a fixed
number of decimals
157. PHONETIC =PHONETIC(reference) Extracts the phonetic (furigana) characters from
a text string
158. REPT =REPT(text,number_times) Repeats text a given number of times
159. SUBSTITUTE =SUBSTITUTE(text,old_text,new_text,instance_num) Substitutes new
text for old text in a text string
160. T =T(value) Converts its arguments to text
161. VALUE =VALUE(text) Converts a text argument to a number
162. BINOMDIST =BINOMDIST(number_s,trials,probability_s,cumulative) Returns the
individual term binomial distribution probability
163. CHIDIST=CHIDIST(x,deg_freedom) Returns the one-tailed probability of the chi-
squared distribution
164. CHIINV =CHIINV(probability,deg_freedom) Returns the inverse of the one-tailed
probability of the chi-squared distribution
165. CHITEST =CHITEST(actual_range,expected_range) Returns the test for
independence
166. CONFIDENCE =CONFIDENCE(alpha,standard_dev,size) Returns the confidence interval
for a population mean
167. FTEST =FTEST(array1,array2)
168. LOGINV=LOGINV(probability,mean,standard_dev) Returns the inverse of the
lognormal cumulative distribution
169. LOGNORMDIST =LOGNORMDIST(x,mean,standard_dev) Returns the cumulative
lognormal distribution
170. MODE =MODE(number1,number2,…) Returns the most common value in a data set
171. NORMDIST =NORMDIST(x,mean,standard_dev,cumulative) Returns the normal
cumulative distribution
172. NORMINV =NORMINV(probability,mean,standard_dev) Returns the inverse of
the normal cumulative distribution
173. NORMSDIST =NORMSDIST(z)Returns the standard normal cumulative distribution
174. NORMSINV =NORMSINV(probability) Returns the inverse of the standard
normal cumulative distribution
175. PERCENTILE =PERCENTILE(array,k) Returns the k-th percentile of values in a range
176. PERCENTRANK =PERCENTRANK(array,x,significance) Returns the percentage rank of
a value in a data set
177. POISSON =POISSON(x,mean,cumulative) Returns the Poisson distribution
178. QUARTILE =QUARTILE(array,quart)Returns the quartile of a data set
179. RANK =RANK(number,ref,order) Returns the rank of a number in a list of
numbers
180. STDEV =STDEV(number1,number2,…) Estimates standard deviation based on a sample
181. STDEVP =STDEVP(number1,number2,…) Calculates standard deviation based on the
entire population
182. TDIST =TDIST(x,deg_freedom,tails) Returns the Student’s t-distribution
183. TINV =TINV(probability,deg_freedom) Returns the inverse of the Student’s t-
distribution
184. VAR =VAR(number1,number2,…) Estimates variance based on a sample
185. VARP =VARP(number1,number2,…) Calculates variance based on the entire
population
186. DAVERAGE =DAVERAGE(database,field,criteria) Returns the average of selected
database entries
187. DCOUNT =DCOUNT(database,field,criteria) Counts the cells that contain
numbers in a database
188. DCOUNTA =DCOUNTA(database,field,criteria) Counts nonblank cells in a
database
189. DMAX =DMAX(database,field,criteria) Returns the maximum value from selected
database entries
190. DMIN =DMIN(database,field,criteria) Returns the minimum value from selected
database entries
191. DPRODUCT =DPRODUCT(database,field,criteria) Multiplies the values in a
particular field of records that match the criteria in a database
192. DSTDEV=DSTDEV(database,field,criteria) Estimates the standard deviation based
on a sample of selected database entries
193. DSTDEVP =DSTDEVP(database,field,criteria) Calculates the standard
deviation based on the entire population of selected database entries
194. DVAR =DVAR(database,field,criteria) Estimates variance based on a sample from
selected database entries
195. DVARP =DVARP(database,field,criteria) Calculates variance based on the entire
population of selected database entries
196. ERF.PRECISE =ERF.PRECISE(X) Returns the error function
197. ERFC.PRECISE =ERFC.PRECISE(X) Returns the complementary ERF function
integrated between x and infinity
198. DB =DB(cost,salvage,life,period,month) Returns the depreciation of an asset for
a specified period by using the fixed-declining balance method
199. DDB =DDB(cost,salvage,life,period,factor) Returns the depreciation of an asset for
a specified period by using the double-declining balance method or some other method that you
specify
200. EFFECT =EFFECT(nominal_rate,npery) Returns the effective annual interest rate
201. FV =FV(rate,nper,pmt,pv,type) Returns the future value of an investment
202. IPMT =IPMT(rate,per,nper,pv,fv,type) Returns the interest payment for an investment
for a given period
203. IRR =IRR(values,guess) Returns the internal rate of return for a series of cash
flows
204. MIRR =MIRR(values,finance_rate,reinvest_rate) Returns the internal rate of
return where positive and negative cash flows are financed at different rates
205. NOMINAL =NOMINAL(effect_rate,npery) Returns the annual nominal interest
rate
206. NPER =NPER(rate,pmt,pv,fv,type) Returns the number of periods for an
investment
207. NPV =NPV(rate,value1,value2,…) Returns the net present value of an investment
based on a series of periodic cash flows and a discount rate
208. PV =PV(rate,nper,pmt,fv,type) Returns the present value of an investment
209. RATE =RATE(nper,pmt,pv,fv,type,guess) Returns the interest rate per period of
an annuity
210. YIELD =YIELD(settlement,maturity,rate,pr,redemption,frequency,basis) Returns
the yield on a security that pays periodic interest
211. AREAS =AREAS(reference) Returns the number of areas in a reference
212. RTD =RTD(progID,server,topic1,topic2,…) Retrieves real-time data from a
program that supports COM automation (Automation: A way to work with an application’s
objects from another application or development tool. Formerly called OLE Automation,
Automation is an industry standard and a feature of the Component Object Model (COM).)
213. AGGREGATE =AGGREGATE(function_num,options,array,k) Returns an aggregate in
a list or database
214. COMBIN =COMBIN(number,number_chosen) Returns the number of
combinations for a given number of objects
215. COS =COS(number) Returns the cosine of a number
216. COSH =COSH(number) Returns the hyperbolic cosine of a number
217. FACTDOUBLE =FACTDOUBLE(number)Returns the double factorial of a number
218. LN =LN(number) Returns the natural logarithm of a number
219. LOG =LOG(number,base) Returns the logarithm of a number to a specified base
220. LOG10 =LOG10(number) Returns the base-10 logarithm of a number
221. MULTINOMIAL =MULTINOMIAL(number1,number2,…) Returns the multinomial of a set
of numbers
222. SIN =SIN(number) Returns the sine of the given angle
223. SINH =SINH(number) Returns the hyperbolic sine of a number
224. SUMX2MY2 =SUMX2MY2(array_x,array_y) Returns the sum of the difference of
squares of corresponding values in two arrays
225. SUMX2PY2 =SUMX2PY2(array_x,array_y) Returns the sum of the sum of squares
of corresponding values in two arrays
226. SUMXMY2 =SUMXMY2(array_x,array_y) Returns the sum of squares of
differences of corresponding values in two arrays
227. TAN =TAN(number) Returns the tangent of a number
228. TANH =TANH(number) Returns the hyperbolic tangent of a number
229. NORM.S.INV =NORM.S.INV(probability) Returns the inverse of the standard
normal cumulative distribution
230. AVEDEV =AVEDEV(number1,number2,…)Returns the average of the absolute
deviations of data points from their mean
231. BETA.DIST =BETA.DIST(x,alpha,beta,cumulative,A,B) Returns the beta
cumulative distribution function
232. BETA.INV =BETA.INV(probability,alpha,beta,A,B) Returns the inverse of the
cumulative distribution function for a specified beta distribution
233. BINOM.DIST =BINOM.DIST(number_s,trials,probability_s,cumulative) Returns the
individual term binomial distribution probability
234. BINOM.INV =BINOM.INV(trials,probability_s,alpha) Returns the smallest value for
which the cumulative binomial distribution is less than or equal to a criterion value
235. CHISQ.DIST =CHISQ.DIST(x,deg_freedom,cumulative) Returns the cumulative
beta probability density function
236. CHISQ.DIST.RT =CHISQ.DIST.RT(x,deg_freedom) Returns the one-tailed
probability of the chi-squared distribution
237. CHISQ.INV =CHISQ.INV(probability,deg_freedom) Returns the cumulative beta
probability density function
238. CHISQ.INV.RT =CHISQ.INV.RT(probability,deg_freedom) Returns the inverse of
the one-tailed probability of the chi-squared distribution
239. CHISQ.TEST =CHISQ.TEST(actual_range,expected_range) Returns the test for
independence
240. CONFIDENCE.NORM =CONFIDENCE.NORM(alpha,standard_dev,size) Returns the
confidence interval for a population mean
241. CONFIDENCE.T =CONFIDENCE.T(alpha,standard_dev,size) Returns the confidence
interval for a population mean, using a Student’s t distribution
242. CORREL=CORREL(array1,array2)Returns the correlation coefficient between two data
sets
243. COVARIANCE.P =COVARIANCE.P(array1,array2) Returns covariance, the average of the
products of paired deviations
244. COVARIANCE.S =COVARIANCE.S(array1,array2) Returns the sample covariance, the
average of the products deviations for each data point pair in two data sets
245. DEVSQ =DEVSQ(number1,number2,…) Returns the sum of squares of deviations
246. EXPON.DIST =EXPON.DIST(x,lambda,cumulative) Returns the exponential
distribution
247. F.DIST =F.DIST(x,deg_freedom1,deg_freedom2,cumulative) Returns the F
probability distribution
248. F.DIST.RT =F.DIST.RT(x,deg_freedom1,deg_freedom2) Returns the F
probability distribution
249. F.INV =F.INV(probability,deg_freedom1,deg_freedom2) Returns the inverse of
the F probability distribution
250. F.INV.RT =F.INV.RT(probability,deg_freedom1,deg_freedom2) Returns the
inverse of the F probability distribution
251. F.TEST =F.TEST(array1,array2) Returns the result of an F-test
252. FINV =FINV(probability,deg_freedom1,deg_freedom2) Returns the inverse of
the F probability distribution
253. FISHER =FISHER(x) Returns the Fisher transformation
254. FISHERINV =FISHERINV(y) Returns the inverse of the Fisher transformation
255. FORECAST =FORECAST(x,known_y’s,known_x’s) Returns a value along a linear
trend
256. FREQUENCY =FREQUENCY(data_array,bins_array) Returns a frequency
distribution as a vertical array
257. GAMMA.DIST =GAMMA.DIST(x,alpha,beta,cumulative) Returns the gamma
distribution
258. GAMMA.INV =GAMMA.INV(probability,alpha,beta) Returns the inverse of the
gamma cumulative distribution
259. GAMMALN =GAMMALN(x) Returns the natural logarithm of the gamma function,
Γ(x)
260. GAMMALN.PRECISE =GAMMALN.PRECISE(x) Returns the natural logarithm of the
gamma function, Γ(x)
261. GROWTH =GROWTH(known_y’s,known_x’s,new_x’s,const) Returns values
along an exponential trend
262. HARMEAN =HARMEAN(number1,number2,…) Returns the harmonic mean
263. HYPGEOM.DIST
=HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative)
Returns the hypergeometric distribution
264. KURT =KURT(number1,number2,…) Returns the kurtosis of a data set
265. LOGNORM.DIST=LOGNORM.DIST(x,mean,standard_dev,cumulative) Returns the
cumulative lognormal distribution
266. LOGNORM.INV =LOGNORM.INV(probability,mean,standard_dev) Returns the
inverse of the lognormal cumulative distribution
267. NEGBINOM.DIST
=NEGBINOM.DIST(number_f,number_s,probability_s,cumulative) Returns the
negative binomial distribution
268. NORM.DIST =NORM.DIST(x,mean,standard_dev,cumulative) Returns the normal
cumulative distribution
269. NORM.INV =NORM.INV(probability,mean,standard_dev) Returns the inverse of
the normal cumulative distribution
270. NORM.S.DIST =NORM.S.DIST(z,cumulative) Returns the standard normal
cumulative distribution
271. PEARSON =PEARSON(array1,array2) Returns the Pearson product moment
correlation coefficient
272. PERCENTILE.EXC =PERCENTILE.EXC(array,k) Returns the k-th percentile of
values in a range, where k is in the range 0..1, exclusive
273. PERCENTILE.INC =PERCENTILE.INC(array,k) Returns the k-th percentile of
values in a range
274. PERCENTRANK.EXC =PERCENTRANK.EXC(array,x,significance) Returns the
rank of a value in a data set as a percentage (0..1, exclusive) of the data set
275. PERCENTRANK.INC =PERCENTRANK.INC(array,x,significance) Returns the
percentage rank of a value in a data set
276. PERMUT =PERMUT(number,number_chosen) Returns the number of
permutations for a given number of objects
277. POISSON.DIST =POISSON.DIST(x,mean,cumulative) Returns the Poisson distribution
278. QUARTILE.EXC =QUARTILE.EXC(array,quart) Returns the quartile of the data set,
based on percentile values from 0..1, exclusive
279. QUARTILE.INC =QUARTILE.INC(array,quart) Returns the quartile of a data set
280. RSQ =RSQ(known_y’s,known_x’s) Returns the square of the Pearson product
moment correlation coefficient
281. STDEV.P =STDEV.P(number1,number2,…) Calculates standard deviation
based on the entire population
282. STDEV.S =STDEV.S(number1,number2,…)Estimates standard deviation based on
a sample
283. STDEVA=STDEVA(value1,value2,…) Estimates standard deviation based on a
sample, including numbers, text, and logical values
284. STDEVPA =STDEVPA(value1,value2,…) Calculates standard deviation based on
the entire population, including numbers, text, and logical values
285. STEYX =STEYX(known_y’s,known_x’s) Returns the standard error of the predicted y-
value for each x in the regression
286. T.DIST =T.DIST(x,deg_freedom,cumulative) Returns the Percentage Points
(probability) for the Student t-distribution
287. T.DIST.2T =T.DIST.2T(x,deg_freedom) Returns the Percentage Points
(probability) for the Student t-distribution
288. T.DIST.RT =T.DIST.RT(x,deg_freedom) Returns the Student’s t-distribution
289. T.INV =T.INV(probability,deg_freedom) Returns the t-value of the Student’s t-
distribution as a function of the probability and the degrees of freedom
290. T.INV.2T =T.INV.2T(probability,deg_freedom) Returns the inverse of the
Student’s t-distribution
291. T.TEST =T.TEST(array1,array2,tails,type) Returns the probability associated with
a Student’s t-test
292. TRIMMEAN =TRIMMEAN(array,percent) Returns the mean of the interior of a
data set
293. VAR.P =VAR.P(number1,number2,…) Calculates variance based on the entire
population
294. VAR.S =VAR.S(number1,number2,…) Estimates variance based on a sample
295. VARA =VARA(value1,value2,…) Estimates variance based on a sample, including
numbers, text, and logical values
296. VARPA =VARPA(value1,value2,…) Calculates variance based on the entire
population, including numbers, text, and logical values
297. WEIBULL.DIST =WEIBULL.DIST(x,alpha,beta,cumulative) Returns the Weibull
distribution
298. Z.TEST =Z.TEST(array,x,sigma) Returns the one-tailed probability-value of a z-test
299. ASC =ASC(text) Changes full-width (double-byte) English letters or katakana
within a character string to half-width (single-byte) characters
300. BETADIST =BETADIST(x,alpha,beta,A,B) Returns the beta cumulative
distribution function
301. BETAINV =BETAINV(probability,alpha,beta,A,B) Returns the inverse of the
cumulative distribution function for a specified beta distribution
302. COVAR =COVAR(array1,array2) Returns covariance, the average of the products of
paired deviations
303. CRITBINOM =CRITBINOM(trials,probability_s,alpha) Returns the smallest value for
which the cumulative binomial distribution is less than or equal to a criterion value
304. EXPONDIST =EXPONDIST(x,lambda,cumulative) Returns the exponential
distribution
305. FDIST =FDIST(x,deg_freedom1,deg_freedom2)Returns the F probability distribution
306. GAMMADIST =GAMMADIST(x,alpha,beta,cumulative) Returns the gamma distribution
307. GAMMAINV =GAMMAINV(probability,alpha,beta) Returns the inverse of the
gamma cumulative distribution
308. HYPGEOMDIST =HYPGEOMDIST(sample_s,number_sample,population_s,number_pop)
Returns the hypergeometric distribution
309. NEGBINOMDIST=NEGBINOMDIST(number_f,number_s,probability_s) Returns the
negative binomial distribution
310. TTEST =TTEST(array1,array2,tails,type) Returns the probability associated with a
Student’s t-test
311. WEIBULL =WEIBULL(x,alpha,beta,cumulative) Calculates variance based on
the entire population, including numbers, text, and logical values
312. ZTEST =ZTEST(array,x,sigma) Returns the one-tailed probability-value of a z-test
313. BESSELI =BESSELI(x,n) Returns the modified Bessel function In(x)
314. BESSELJ=BESSELJ(x,n) Returns the Bessel function Jn(x)
315. BESSELK =BESSELK(x,n) Returns the modified Bessel function Kn(x)
316. BESSELY =BESSELY(x,n) Returns the Bessel function Yn(x)
317. BIN2DEC =BIN2DEC(number) Converts a binary number to decimal
318. BIN2HEX =BIN2HEX(number,places) Converts a binary number to
hexadecimal
319. BIN2OCT =BIN2OCT(number,places) Converts a binary number to octal
320. COMPLEX =COMPLEX(real_num,i_num,suffix) Converts real and imaginary
coefficients into a complex number
321. DEC2BIN =DEC2BIN(number,places) Converts a decimal number to binary
322. DEC2HEX =DEC2HEX(number,places) Converts a decimal number to
hexadecimal
323. DEC2OCT =DEC2OCT(number,places) Converts a decimal number to octal
324. HEX2BIN =HEX2BIN(number,places) Converts a hexadecimal number to
binary
325. HEX2DEC =HEX2DEC(number) Converts a hexadecimal number to decimal
326. HEX2OCT =HEX2OCT(number,places) Converts a hexadecimal number to
octal
327. IMABS =IMABS(inumber) Returns the absolute value (modulus) of a complex
number
328. IMAGINARY =IMAGINARY(inumber) Returns the imaginary coefficient of a complex
number
329. IMARGUMENT =IMARGUMENT(inumber) Returns the argument theta, an angle
expressed in radians
330. IMCONJUGATE =IMCONJUGATE(inumber) Returns the complex conjugate of a
complex number
331. IMCOS =IMCOS(inumber) Returns the cosine of a complex number
332. IMDIV =IMDIV(inumber1,inumber2) Returns the quotient of two complex numbers
333. IMEXP =IMEXP(inumber) Returns the exponential of a complex number
334. IMLN =IMLN(inumber) Returns the natural logarithm of a complex number
335. IMLOG10 =IMLOG10(inumber) Returns the base-10 logarithm of a complex
number
336. IMLOG2 =IMLOG2(inumber) Returns the base-2 logarithm of a complex
number
337. IMPOWER =IMPOWER(inumber,number) Returns a complex number raised to an
integer power
338. IMPRODUCT =IMPRODUCT(inumber1,inumber2,…) Returns the product of complex
numbers
339. IMREAL =IMREAL(inumber) Returns the real coefficient of a complex number
340. IMSIN =IMSIN(inumber) Returns the sine of a complex number
341. IMSQRT =IMSQRT(inumber) Returns the square root of a complex number
342. IMSUB =IMSUB(inumber1,inumber2) Returns the difference between two complex
numbers
343. IMSUM =IMSUM(inumber1,inumber2,…) Returns the sum of complex numbers
344. OCT2BIN =OCT2BIN(number,places) Converts an octal number to binary
345. OCT2DEC =OCT2DEC(number) Converts an octal number to decimal
346. OCT2HEX =OCT2HEX(number,places) Converts an octal number to
hexadecimal
347. ACCRINT
=ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis,calc_method) Returns
the accrued interest for a security that pays periodic interest
348. ACCRINTM =ACCRINTM(issue,settlement,rate,par,basis) Returns the accrued
interest for a security that pays interest at maturity
349. COUPDAYBS =COUPDAYBS(settlement,maturity,frequency,basis) Returns the
number of days from the beginning of the coupon period to the settlement date
350. COUPDAYS =COUPDAYS(settlement,maturity,frequency,basis) Returns the
number of days in the coupon period that contains the settlement date
351. COUPDAYSNC =COUPDAYSNC(settlement,maturity,frequency,basis) Returns the
number of days from the settlement date to the next coupon date
352. COUPNCD =COUPNCD(settlement,maturity,frequency,basis) Returns the
next coupon date after the settlement date
353. COUPNUM =COUPNUM(settlement,maturity,frequency,basis) Returns the
number of coupons payable between the settlement date and maturity date
354. COUPPCD =COUPPCD(settlement,maturity,frequency,basis) Returns the
previous coupon date before the settlement date
355. CUMIPMT =CUMIPMT(rate,nper,pv,start_period,end_period,type) Returns the
cumulative interest paid between two periods
356. CUMPRINC =CUMPRINC(rate,nper,pv,start_period,end_period,type) Returns
the cumulative principal paid on a loan between two periods
357. DISC =DISC(settlement,maturity,pr,redemption,basis) Returns the discount rate for a
security
358. DURATION =DURATION(settlement,maturity,coupon,yld,frequency,basis) Returns
the annual duration of a security with periodic interest payments
359. FVSCHEDULE =FVSCHEDULE(principal,schedule) Returns the future value of an
initial principal after applying a series of compound interest rates
360. INTRATE =INTRATE(settlement,maturity,investment,redemption,basis) Returns
the interest rate for a fully invested security
361. ISPMT =ISPMT(rate,per,nper,pv) Calculates the interest paid during a specific
period of an investment
362. MDURATION =MDURATION(settlement,maturity,coupon,yld,frequency,basis) Returns
the Macauley modified duration for a security with an assumed par value of $100
363. ODDFPRICE

=ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yld,redemption,frequency,basis)
Returns the price per $100 face value of a security with an odd first period
364. ODDFYIELD

=ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,redemption,frequency,basis)
Returns the yield of a security with an odd first period
365. ODDLPRICE
=ODDLPRICE(settlement,maturity,last_interest,rate,yld,redemption,frequency,basis)
Returns the price per $100 face value of a security with an odd last period
366. ODDLYIELD
=ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,basis)
Returns the yield of a security with an odd last period
367. PMT =PMT(rate,nper,pv,fv,type) Returns the periodic payment for an annuity
368. PPMT =PPMT(rate,per,nper,pv,fv,type) Returns the payment on the principal
for an investment for a given period
369. PRICE =PRICE(settlement,maturity,rate,yld,redemption,frequency,basis) Returns
the price per $100 face value of a security that pays periodic interest
370. PRICEDISC =PRICEDISC(settlement,maturity,discount,redemption,basis) Returns
the price per $100 face value of a discounted security
371. PRICEMAT =PRICEMAT(settlement,maturity,issue,rate,yld,basis) Returns the
price per $100 face value of a security that pays interest at maturity
372. RECEIVED =RECEIVED(settlement,maturity,investment,discount,basis) Returns
the amount received at maturity for a fully invested security
373. TBILLEQ =TBILLEQ(settlement,maturity,discount) Returns the bond-equivalent
yield for a Treasury bill
374. TBILLPRICE =TBILLPRICE(settlement,maturity,discount) Returns the price per
$100 face value for a Treasury bill
375. TBILLYIELD =TBILLYIELD(settlement,maturity,pr) Returns the yield for a Treasury
bill
376. VDB =VDB(cost,salvage,life,start_period,end_period,factor,no_switch) Returns
the depreciation of an asset for a specified or partial period by using a declining balance method
377. XIRR =XIRR(values,dates,guess) Returns the internal rate of return for a
schedule of cash flows that is not necessarily periodic
378. XNPV =XNPV(rate,values,dates) Returns the net present value for a schedule of
cash flows that is not necessarily periodic
379. YIELDDISC =YIELDDISC(settlement,maturity,pr,redemption,basis) Returns the
annual yield for a discounted security; for example, a Treasury bill
380. YIELDMAT =YIELDMAT(settlement,maturity,issue,rate,pr,basis) Returns the
annual yield of a security that pays interest at maturity
381. ACOS =ACOS(number) Returns the arccosine of a number
382. ACOSH =ACOSH(number) Returns the inverse hyperbolic cosine of a number
383. ASIN =ASIN(number) Returns the arcsine of a number
384. ASINH =ASINH(number) Returns the inverse hyperbolic sine of a number
385. ATAN =ATAN(number) Returns the arctangent of a number
386. ATAN2 =ATAN2(x_num,y_num) Returns the arctangent from x- and y-coordinates
387. ATANH =ATANH(number) Returns the inverse hyperbolic tangent of a number
388. DEGREES =DEGREES(angle) Converts radians to degrees
389. MDETERM =MDETERM(array) Returns the matrix determinant of an array
390. MINVERSE =MINVERSE(array) Returns the matrix inverse of an array
391. MMULT=MMULT(array1,array2) Returns the matrix product of two arrays
392. RADIANS =RADIANS(angle) Converts degrees to radians
393. ROMAN =ROMAN(number,form) Converts an arabic numeral to roman,
as text
394. SQRTPI =SQRTPI(number) Returns the square root of (number * pi)
395. BAHTTEXT =BAHTTEXT(number) Converts a number to text, using the ß (baht)
currency format
396. CUBEKPIMEMBER =CUBEKPIMEMBER(connection,kpi_name,kpi_property,caption)
Returns a key performance indicator (KPI) name, property, and measure, and displays
the name and property in the cell. A KPI is a quantifiable measurement, such as monthly gross
profit or quarterly employee turnover, used to monitor an organization’s performance.
397. CUBEMEMBER =CUBEMEMBER(connection,member_expression,caption) Returns
a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the
cube.
398. CUBEMEMBERPROPERTY
=CUBEMEMBERPROPERTY(connection,member_expression,property) Returns the
value of a member property in the cube. Use to validate that a member name exists within the
cube and to return the specified property for this member.
399. CUBERANKEDMEMBER
=CUBERANKEDMEMBER(connection,set_expression,rank,caption) Returns the
nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top
sales performer or top 10 students.
400. CUBESET =CUBESET(connection,set_expression,caption,sort_order,sort_by)
Defines a calculated set of members or tuples by sending a set expression to the cube
on the server, which creates the set, and then returns that set to Microsoft Office Excel.
401. CUBESETCOUNT =CUBESETCOUNT(set) Returns the number of items in a set.
402. CUBEVALUE =CUBEVALUE(connection,member_expression1,…) Returns an
aggregated value from a cube.
Excel Functions

500 Excel functions

1. DAVERAGE
2. Get average from matching records
3. databasefieldcriteria
4. DCOUNT
5. Count matching records in a database
6. databasefieldcriteria
7. DCOUNTA
8. Count matching records in a database
9. databasefieldcriteria
10. DGET
11. Get value from matching record
12. databasefieldcriteria
13. DMAX
14. Get max from matching records
15. databasefieldcriteria
16. DMIN
17. Get min from matching records
18. databasefieldcriteria
19. DPRODUCT
20. Get product from matching records
21. databasefieldcriteria
22. DSTDEV
23. Get standard deviation of sample in matching records
24. databasefieldcriteria
25. DSTDEVP
26. Get standard deviation of population in matching records
27. databasefieldcriteria
28. DSUM
29. Get sum from matching records
30. databasefieldcriteria
31. DVAR
32. Get sample variance for matching records
33. databasefieldcriteria
34. DVARP
35. Get population variance for matching records
36. databasefieldcriteria

37. Date and time

38. DATE
39. Create a date with year, month, and day
40. yearmonthday
41. DATEDIF
42. Get days, months, or years between two dates
43. start_dateend_dateunit
44. DATEVALUE
45. Convert a date in text format to a valid date
46. date_text
47. DAY
48. Get the day as a number (1-31) from a date
49. date
50. DAYS
51. Get days between dates
52. end_datestart_date
53. DAYS360
54. Get days between 2 dates in a 360-day year
55. start_dateend_datemethod
56. EDATE
57. Shift date n months in future or past
58. start_datemonths
59. EOMONTH
60. Get last day of month n months in future or past
61. start_datemonths
62. HOUR
63. Get the hour as a number (0-23) from a Time
64. serial_number
65. ISOWEEKNUM
66. Get ISO week number for a given date
67. date
68. MINUTE
69. Get minute as a number (0-59) from time
70. serial_number
71. MONTH
72. Get month as a number (1-12) from a date
73. date
74. NETWORKDAYS
75. Get the number of working days between two dates
76. start_dateend_dateholidays
77. NETWORKDAYS.INTL
78. Get work days between two dates
79. start_dateend_dateweekendholidays
80. NOW
81. Get the current date and time
82. SECOND
83. Get the Second as a number (0-59) from a Time
84. serial_number
85. TIME
86. Create a time with hours, minutes, and seconds
87. hourminutesecond
88. TIMEVALUE
89. Get a valid time from a text string
90. time_text
91. TODAY
92. Get the current date
93. WEEKDAY
94. Get the day of the week as a number
95. serial_numberreturn_type
96. WEEKNUM
97. Get the week number for a given date
98. serial_numreturn_type
99. WORKDAY
100. Get a date n working days in the future or past
101.start_datedaysholidays
102. WORKDAY.INTL
103. Get date n working days in future or past
104.start_datedaysweekendholidays
105. YEAR
106. Get the year from a date
107.date
108. YEARFRAC
109. Get the fraction of a year between two dates
110.start_dateend_datebasis

111. Engineering
112. BIN2DEC
113. Converts a binary number to decimal
114.number
115. BIN2HEX
116. Converts a binary number to hexadecimal
117.numberplaces
118. BIN2OCT
119. Converts a binary number to octal
120.numberplaces
121. BITAND
122. Returns a 'Bitwise And' of two numbers
123.number1number2
124. BITLSHIFT
125. Returns a number shifted left by some number of bits
126.numbershift_amount
127. BITOR
128. Returns a 'Bitwise Or' of two numbers
129.number1number2
130. BITRSHIFT
131. Returns a number shifted right by some number of bits
132.numbershift_amount
133. BITXOR
134. Returns a 'Bitwise Xor' of two numbers
135.number1number2
136. COMPLEX
137. Convert coefficients to complex number
138.real_numi_numsuffix
139. CONVERT
140. Convert measurement units
141.numberfrom_unitto_unit
142. DEC2BIN
143. Converts a decimal number to binary
144.numberplaces
145. DEC2HEX
146. Converts a decimal number to hexadecimal
147.numberplaces
148. DEC2OCT
149. Converts a decimal number to octal
150.numberplaces
151. DELTA
152. Test two values are equal
153.number1number2
154. HEX2BIN
155. Converts a hexadecimal number to binary
156.numberplaces
157. HEX2DEC
158. Converts a hexadecimal number to decimal
159.number
160. HEX2OCT
161. Converts a hexadecimal number to octal
162.numberplaces
163. IMABS
164. Get absolute value of complex number
165.inumber
166. IMAGINARY
167. Get imaginary coefficient of complex number
168.inumber
169. IMPOWER
170. Raise complex number to given power
171.inumbernumber
172. IMPRODUCT
173. Get product of complex numbers
174.inumber1inumber2...
175. IMREAL
176. Get real coefficient of complex number
177.inumber
178. IMSUB
179. Get difference between two complex numbers
180.inumber1inumber2
181. IMSUM
182. Get sum of complex numbers
183.inumber1inumber2...

184. Financial
185. ACCRINT
186. Get accrued interest periodic
187.idfdsdrateparfreqbasiscalc
188. ACCRINTM
189. Get accrued interest at maturity
190.idsdrateparbasis
191. AMORDEGRC
192. Depreciation for accounting period coefficient
193.costpurchasefirstsalvageperiodratebasis
194. AMORLINC
195. Depreciation for accounting period
196.costpurchasefirstsalvageperiodratebasis
197. COUPDAYBS
198. Get days from coupon period to settlement date
199.settlementmaturityfrequencybasis
200. COUPDAYS
201. Get days in coupon period incl settlement date
202.settlementmaturityfrequencybasis
203. COUPDAYSNC
204. Get days from settlement date to next coupon date
205.settlementmaturityfrequencybasis
206. COUPNCD
207. Get next coupon date after settlement date
208.settlementmaturityfrequencybasis
209. COUPNUM
210. Get number of coupons payable
211.settlementmaturityfrequencybasis
212. COUPPCD
213. Get previous coupon date before settlement date
214.settlementmaturityfrequencybasis
215. CUMIPMT
216. Get cumulative interest paid on a loan
217.ratenperpvstart_periodend_periodtype
218. CUMPRINC
219. Get cumulative principal paid on a loan
220.ratenperpvstart_periodend_periodtype
221. DB
222. Depreciation - fixed-declining balance
223.costsalvagelifeperiodmonth
224. DDB
225. Depreciation - double-declining
226.costsalvagelifeperiodfactor
227. DISC
228. Get discount rate for a security
229.settlementmaturityprredemptionbasis
230. DOLLARDE
231. Convert dollar price as fraction to decimal
232.fractional_dollarfraction
233. DOLLARFR
234. Convert price to fractional notation
235.decimal_dollarfraction
236. DURATION
237. Get annual duration with periodic interest
238.settlementmaturitycouponyldfreqbasis
239. EFFECT
240. Get effective annual interest rate
241.nominal_ratenpery
242. FV
243. Get the future value of an investment
244.ratenperpmtpvtype
245. FVSCHEDULE
246. Get future value of principal compound interest
247.principalschedule
248. INTRATE
249. Get interest rate for fully invested security
250.settlementmaturityinvestmentredemptionbasis
251. IPMT
252. Get interest in given period
253.ratepernperpvfvtype
254. IRR
255. Calculate internal rate of return
256.valuesguess
257. ISPMT
258. Get interest paid for specific period
259.ratepernperpv
260. MDURATION
261. Get Macauley modified duration par value of $100
262.settlementmaturitycouponyldfreqbasis
263. MIRR
264. Calculate modified internal rate of return
265.valuesfinance_ratereinvest_rate
266. NOMINAL
267. Get annual nominal interest rate
268.effect_ratenpery
269. NPER
270. Get number of periods for loan or investment
271.ratepmtpvfvtype
272. NPV
273. Calculate net present value
274.ratevalue1value2...
275. ODDFPRICE
276. Get price per $100 odd first period
277.sdmdidfdrateyldredemfreqbasis
278. ODDFYIELD
279. Get yield security with odd first period
280.sdmdidfdrateprredemfreqbasis
281. ODDLPRICE
282. Get price per $100 face value with odd last period
283.sdmdidrateyldredemfreqbasis
284. ODDLYIELD
285. Get yield of security with odd last period
286.sdmdldrateprredemfreqbasis
287. PDURATION
288. Get periods required to reach given value
289.ratepvfv
290. PMT
291. Get the periodic payment for a loan
292.ratenperpvfvtype
293. PPMT
294. Get principal payment in given period
295.ratepernperpvfvtype
296. PRICE
297. Get price per $100 face value - periodic interest
298.sdmdrateyldredemptionfrequencybasis
299. PRICEDISC
300. Get price per $100 discounted security
301.sdmddiscountredemptionbasis
302. PRICEMAT
303. Get price per $100 interest at maturity
304.sdmdidrateyldbasis
305. PV
306. Get the present value of an investment
307.ratenperpmtfvtype
308. RATE
309. Get the interest rate per period of an annuity
310.nperpmtpvfvtypeguess
311. RECEIVED
312. Get amount received at maturity
313.settlementmaturityinvestmentdiscountbasis
314. RRI
315. Get equivalent interest rate for growth
316.nperpvfv
317. SLN
318. Depreciation - straight-line
319.costsalvagelife
320. SYD
321. Depreciation - sum-of-years
322.costsalvagelifeperiod
323. TBILLEQ
324. Get bond-equivalent yield for a Treasury bill
325.settlementmaturitydiscount
326. TBILLPRICE
327. Get price per $100 Treasury bill
328.settlementmaturitydiscount
329. TBILLYIELD
330. Get yield for a Treasury bill
331.settlementmaturityprice
332. VDB
333. Depreciation - double-declining variable
334.costsalvagelifestartendfactorno_switch
335. XIRR
336. Calculate internal rate of return for irregular cash flows
337.valuesdatesguess
338. XNPV
339. Calculate net present value for irregular cash flows
340.ratevaluesdates
341. YIELD
342. Get yield for security that pays periodic interest
343.sdmdrateprredemptionfrequencybasis
344. YIELDDISC
345. Get annual yield for discounted security
346.sdmdprredemptionbasis
347. YIELDMAT
348. Get annual yield of security interest at maturity
349.sdmdidrateprbasis

350. Information
351. CELL
352. Get information about a cell
353.info_typereference
354. ERROR.TYPE
355. Test for a specific error value
356.error_val
357. INFO
358. Get information about current environment
359.type_text
360. ISBLANK
361. Test if a cell is empty
362.value
363. ISERR
364. Test for any error but #N/A
365.value
366. ISERROR
367. Test for any error
368.value
369. ISEVEN
370. Test if a value is even
371.value
372. ISFORMULA
373. Test if cell contains a formula
374.reference
375. ISLOGICAL
376. Test if a value is logical
377.value
378. ISNA
379. Test for the #N/A error
380.value
381. ISNONTEXT
382. Test for a non-text value
383.value
384. ISNUMBER
385. Test for numeric value
386.value
387. ISODD
388. Test if a value is odd
389.value
390. ISREF
391. Test for a reference
392.value
393. ISTEXT
394. Test for a text value
395.value
396. N
397. Convert a value to a number
398.value
399. NA
400. Create an #N/A error
401. SHEET
402. Get sheet index number
403.value
404. SHEETS
405. Get number of sheets in a reference
406.reference
407. T
408. Filter text values only
409.value
410. TYPE
411. Get the type of value in a cell
412.value

413. Logical
414. AND
415. Test multiple conditions with AND
416.logical1logical2...
417. FALSE
418. Generate the logical value FALSE
419. IF
420. Test for a specific condition
421.logical_testvalue_if_truevalue_if_false
422. IFERROR
423. Trap and handle errors
424.valuevalue_if_error
425. IFNA
426. Trap and handle #N/A errors
427.valuevalue_if_na
428. IFS
429. Test multiple conditions, return first true
430.test1value1test2, value2...
431. NOT
432. Reverse arguments or results
433.logical
434. OR
435. Test multiple conditions with OR
436.logical1logical2...
437. SWITCH
438. Match multiple values, return first match
439.expressionval1/result1val2/result2...default
440. TRUE
441. Generate the logical value TRUE
442. XOR
443. Perform exclusive OR
444.logical1logical2...

445. Lookup and reference

446. ADDRESS
447. Create a cell address from a given row and column
448.row_numcol_numabs_numa1sheet
449. AREAS
450. Get the number of areas in a reference.
451.reference
452. CHOOSE
453. Get a value from a list based on position
454.index_numvalue1value2...
455. COLUMN
456. Get the column number of a reference.
457.reference
458. COLUMNS
459. Get the number of columns in an array or reference.
460.array
461. FORMULATEXT
462. Get the formula in a cell
463.reference
464. GETPIVOTDATA
465. Retrieve data from a pivot table in a formula
466.data_fieldpivot_tablefield1, item1...
467. HLOOKUP
468. Look up a value in a table by matching on the first row
469.valuetablerow_indexrange_lookup
470. HYPERLINK
471. Create a clickable link.
472.link_locationfriendly_name
473. INDEX
474. Get a value in a list or table based on location
475.arrayrow_numcol_numarea_num
476. INDIRECT
477. Create a reference from text
478.ref_texta1
479. LOOKUP
480. Look up a value in a one-column range
481.lookup_valuelookup_vectorresult_vector
482. MATCH
483. Get the position of an item in an array
484.lookup_valuelookup_arraymatch_type
485. MMULT
486. Perform matrix multiplication
487.array1array2
488. OFFSET
489. Create a reference offset from given starting point
490.referencerowscolsheightwidth
491. ROW
492. Get the row number of a reference
493.reference
494. ROWS
495. Get the number of rows in an array or reference.
496.array
497. TRANSPOSE
498. Flip the orientation of a range of cells
499.array
500. VLOOKUP
501. Lookup a value in a table by matching on the first column
502.valuetablecol_indexrange_lookup

503. Math
504. ABS
505. Find the absolute value of a number
506.number
507. ACOS
508. Get the angle of a cosine value
509.number
510. AGGREGATE
511. Return aggregate calculation
512.function_numoptionsref1ref2
513. ARABIC
514. Converts a Roman numerals to an Arabic numerals
515.roman_text
516. ASIN
517. Get the angle of a triangle.
518.number
519. ATAN
520. Get arctangent of a number
521.number
522. ATAN2
523. Get arctangent from x- and y-coordinates
524.x_numy_num
525. BASE
526. Convert number to another base.
527.numberradixmin_length
528. CEILING
529. Round a number up to nearest multiple
530.numbermultiple
531. CEILING.MATH
532. Round a number up to nearest multiple
533.numbersignificancemode
534. CEILING.PRECISE
535. Round a number up to nearest multiple
536.numbersignificance
537. COS
538. Get the cosine of an angle
539.number
540. COT
541. Get the cotangent of an angle.
542.number
543. CSC
544. Get cosecant of an angle
545.number
546. DECIMAL
547. Converts an alpha-numeric number to decimal
548.numberradix
549. DEGREES
550. Converts radians to degrees
551.angle
552. EVEN
553. Round a number up to the next even integer
554.number
555. EXP
556. Find the value of e raised to the power of a number
557.number
558. FACT
559. Find the factorial of a number
560.number
561. FACTDOUBLE
562. Get double factorial of a number
563.number
564. FLOOR
565. Round a number down to the nearest specified multiple
566.numbermultiple
567. FLOOR.MATH
568. Round number down to nearest multiple
569.numbersignificancemode
570. FLOOR.PRECISE
571. Round number down to nearest multiple
572.numbersignificance
573. GCD
574. Get the greatest common divisor of two or more numbers
575.number1number2...
576. INT
577. Get the integer part of a decimal by rounding down
578.number
579. LCM
580. Get the least common multiple or two or more numbers
581.number1number2...
582. LN
583. Get the natural logarithm of a number
584.number
585. LOG
586. Get the logarithm of a number
587.numberbase
588. LOG10
589. Get the base-10 logarithm of a number
590.number
591. MDETERM
592. Get matrix determinant of given array
593.array
594. MINVERSE
595. Get inverse matrix of array
596.array
597. MOD
598. Get the remainder from division
599.numberdivisor
600. MROUND
601. Round a number to the nearest specified multiple
602.numbermultiple
603. MUNIT
604. Return unit matrix for a given dimension
605.dimension
606. ODD
607. Round a number up to the next odd integer
608.number
609. PI
610. Get the value of π
611. POWER
612. Raise a number to a power
613.numberpower
614. PRODUCT
615. Get the product of supplied numbers
616.number1number2...
617. QUOTIENT
618. Returns the quotient without a remainder.
619.numeratordenominator
620. RADIANS
621. Converts degrees into radians
622.angle
623. RAND
624. Get a random number between 0 and 1
625. RANDBETWEEN
626. Get a random integer between two values
627.bottomtop
628. ROMAN
629. Converts numbers to Roman numerals
630.numberform
631. ROUND
632. Round a number to a given number of digits
633.numbernum_digits
634. ROUNDDOWN
635. Round down to given number of digits
636.numbernum_digits
637. ROUNDUP
638. Round a number up to a given number of digits
639.numbernum_digits
640. SEC
641. Get secant of an angle
642.number
643. SIGN
644. Get the sign of a number
645.number
646. SIN
647. Get the sine of an angle
648.number
649. SQRT
650. Find the positive square root of a number
651.number
652. SUBTOTAL
653. Get a subtotal in a list or database
654.function_numref1ref2...
655. SUM
656. Add numbers together
657.number1number2number3...
658. SUMIF
659. Sum numbers in a range that meet supplied criteria
660.rangecriteriasum_range
661. SUMIFS
662. Sum cells that match multiple criteria
663.sum_rangerange1criteria1range2criteria2...
664. SUMPRODUCT
665. Multiply, then sum arrays
666.array1array2...
667. TAN
668. Get the tangent of an angle
669.number
670. TRUNC
671. Truncate a number to a given precision
672.numbernum_digits

673. Statistical
674. AVEDEV
675. Get sum of squared deviations
676.number1number2...
677. AVERAGE
678. Get the average of a group of numbers
679.number1number2...
680. AVERAGEA
681. Get the average of a group of numbers and text
682.value1value2...
683. AVERAGEIF
684. Get the average of numbers that meet criteria
685.rangecriteriaaverage_range
686. AVERAGEIFS
687. Average cells that match multiple criteria
688.avg_rngrange1criteria1range2criteria2...
689. COMBIN
690. Get total number of combinations
691.numbernumber_chosen
692. COUNT
693. Count numbers
694.value1value2...
695. COUNTA
696. Count the number of non-blank cells
697.value1value2...
698. COUNTBLANK
699. Count cells that are blank
700.range
701. COUNTIF
702. Count cells that match criteria
703.rangecriteria
704. COUNTIFS
705. Count cells that match multiple criteria
706.range1criteria1range2criteria2...
707. DEVSQ
708. Get sum of squared deviations
709.number1number2...
710. FREQUENCY
711. Get the frequency of values in a data set
712.data_arraybins_array
713. GEOMEAN
714. Calculate geometric mean
715.number1number2...
716. HARMEAN
717. Calculate harmonic mean
718.number1number2...
719. LARGE
720. Get the nth largest value
721.arrayn
722. MAX
723. Get the largest value
724.number1number2...
725. MAXA
726. Return largest value.
727.value1value2...
728. MAXIFS
729. Get maximum value with criteria
730.max_rangerange1criteria1range2criteria2...
731. MEDIAN
732. Get the median of a group of numbers
733.number1number2...
734. MIN
735. Get the smallest value.
736.number1number2...
737. MINA
738. Return smallest value.
739.value1value2...
740. MINIFS
741. Get minimum value with criteria
742.min_rangerange1criteria1range2criteria2...
743. MODE
744. Get most frequently occurring number
745.number1number2...
746. MODE.MULT
747. Get most frequently occurring numbers
748.number1number2...
749. MODE.SNGL
750. Get most frequently occurring number
751.number1number2...
752. PERCENTILE
753. Get kth percentile
754.arrayk
755. PERCENTILE.EXC
756. Get kth percentile
757.arrayk
758. PERCENTILE.INC
759. Get kth percentile
760.arrayk
761. PERCENTRANK
762. Get percentile rank, inclusive
763.arrayxsignificance
764. PERCENTRANK.EXC
765. Get percentile rank, exclusive
766.arrayxsignificance
767. PERCENTRANK.INC
768. Get percentile rank, inclusive
769.arrayxsignificance
770. PERMUT
771. Get total number of permutations
772.numbernumber_chosen
773. QUARTILE
774. Get the quartile in a data set
775.arrayquart
776. QUARTILE.EXC
777. Get the quartile in a data set
778.arrayquart
779. QUARTILE.INC
780. Get the quartile in a data set
781.arrayquart
782. RANK
783. Rank a number against a range of numbers
784.numberarrayorder
785. RANK.AVG
786. Rank a number against a range of numbers
787.numberreforder
788. RANK.EQ
789. Rank a number against a range of numbers
790.numberreforder
791. SMALL
792. Get the nth smallest value
793.arrayn
794. STANDARDIZE
795. Calculate a normalized value (z-score)
796.xmeanstandard_dev
797. STDEV
798. Get the standard deviation in a sample
799.number1number2...
800. STDEV.P
801. Get standard deviation of population
802.number1number2...
803. STDEV.S
804. Get the standard deviation in a sample
805.number1number2...
806. STDEVA
807. Get standard deviation in a sample
808.number1number2...
809. STDEVP
810. Get standard deviation of population
811.number1number2...
812. STDEVPA
813. Get standard deviation for a population
814.number1number2...
815. TRIMMEAN
816. Calculate mean excluding outliers
817.arraypercent
818. VAR
819. Get variation of a sample
820.number1number2...
821. VAR.P
822. Get variation of population
823.number1number2...
824. VAR.S
825. Get variation of a sample
826.number1number2...
827. VARA
828. Get variation of a sample
829.number1number2...
830. VARP
831. Get variation of a population
832.number1number2...
833. VARPA
834. Get variation of a population
835.number1number2...

836. Text
837. CHAR
838. Get a character from a number
839.number
840. CLEAN
841. Strip non-printable characters from text
842.text
843. CODE
844. Get the code for a character
845.text
846. CONCAT
847. Join text values without delimiter
848.text1text2...
849. CONCATENATE
850. Join text together
851.text1text2text3...
852. DOLLAR
853. Convert a number to text in currency format
854.numberdecimals
855. EXACT
856. Compare two text strings
857.text1text2
858. FIND
859. Get the location of text in a string
860.find_textwithin_textstart_num
861. FIXED
862. Format number as text with fixed decimals
863.numberdecimalsno_commas
864. LEFT
865. Extract text from the left of a string
866.textnum_chars
867. LEN
868. Get the length of text.
869.text
870. LOWER
871. Convert text to lower case
872.text
873. MID
874. Extract text from inside a string
875.textstart_numnum_chars
876. NUMBERVALUE
877. Convert text to number with custom separators
878.textdecimal_separatorgroup_separator
879. PROPER
880. Capitalize the first letter in each word
881.text
882. REPLACE
883. Replace text based on location
884.old_textstart_numnum_charsnew_text
885. REPT
886. Repeat text as specified
887.textnumber_times
888. RIGHT
889. Extract text from the right of a string
890.textnum_chars
891. SEARCH
892. Get the location of text in a string
893.find_textwithin_textstart_num
894. SUBSTITUTE
895. Replace text based on content
896.textold_textnew_textinstance
897. TEXT
898. Convert a number to text in a number format
899.valueformat_text
900. TEXTJOIN
901. Join text values with a delimiter
902.delimiterignore_emptytext1text2...
903. TRIM
904. Remove extra spaces from text
905.text
906. UNICHAR
907. Get Unicode character by number
908.number
909. UNICODE
910. Get number from Unicode character
911.text
912. UPPER
913. Convert text to upper case
914.text
915. VALUE
916. Convert text to a number
917.text
 918. Dynamic array
919. FILTER
920. Filters range with given criteria
921.arrayincludeif_empty
922. RANDARRAY
923. Get array of random numbers
924.rowscolumnsminmaxinteger
925. SEQUENCE
926. Get array of list of seuential numbers
927.rowscolumnsstartstep
928. SINGLE
929. Get single value with implicit intersection
930.value
931. SORT
932. Sorts range or array
933.arraysort_indexsort_orderby_col
934. SORTBY
935. Sorts range or array by column
936.arrayby_arraysort_orderarray/order...
937. UNIQUE
938. Extract unique values from range
939.arrayby_colexactly_once
940. XLOOKUP
941. Find values in range or table
942.lookuplookup_arrayreturn_arraynot_foundmatch_modesearch_mode
943. XMATCH
944. Lookup values in range or table
945.lookup_valuelookup_arraymatch_modesearch_mode

See also: 101 Excel functions you should know.

Top 101 Excel Functions

This guide describes important Excel functions. Click function names for more
information and detailed examples. We also have a large list of example
formulas, a more complete list of Excel functions, and video training. If you are new
to Excel formulas altogether, see this introduction.
Quick navigation

ABS, AGGREGATE, AND, AVERAGE, AVERAGEIF, AVERAGEIFS, CEILING, 
CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, CONCAT, CONCATE
NATE, CONVERT, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, 
DATE, DATEDIF, DAY, EDATE, EOMONTH, EXACT, FIND, FLOOR, GETPIVO
TDATA, HLOOKUP, HOUR, HYPERLINK, IF, IFERROR, IFNA, IFS, INDEX, IN
DIRECT, INT, ISBLANK, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNU
MBER, ISODD, ISTEXT, LARGE, LEFT, LEN, LOOKUP, LOWER, MATCH, MA
X, MAXIFS, MID, MIN, MINIFS, MINUTE, MOD, MODE, MONTH, MROUND, 
NETWORKDAYS, NOT, NOW, OFFSET, OR, PROPER, RAND, RANDBETWEE
N, RANK, REPLACE, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, RO
WS, SEARCH, SECOND, SMALL, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SU
MIFS, SUMPRODUCT, TEXT, TEXTJOIN, TIME, TODAY, TRANSPOSE, TRIM, 
UPPER, VLOOKUP, WEEKDAY, WEEKNUM, WORKDAY, YEAR, YEARFRAC 
Date and Time Functions
Excel provides many functions to work with dates and times. 
NOW and TODAY

You can get the current date with the TODAY function and the current date and time
with the NOW Function. Technically, the NOW function returns the current date and
time, but you can format as time only, as seen below:

TODAY() // returns current date

NOW() // returns current time
Note: these are volatile functions and will recalculate with every worksheet change. If
you want a static value, use date and time shortcuts.
DAY, MONTH, YEAR, and DATE

You can use the DAY, MONTH, and YEAR functions to disassemble any date into its
raw components, and the DATE function to put things back together again.

=DAY("14-Nov-2018") // returns 14
=MONTH("14-Nov-2018") // returns 11
=YEAR("14-Nov-2018") // returns 2018
=DATE(2018,11,14) // returns 14-Nov-2018
HOUR, MINUTE, SECOND, and TIME

Excel provides a set of parallel functions for times. You can use
the HOUR, MINUTE, and SECOND functions to extract pieces of a time, and you
can assemble a TIME from individual components with the TIME function.
=HOUR("10:30") // returns 10
=MINUTE("10:30") // returns 30
=SECOND("10:30") // returns 0
=TIME(10,30,0) // returns 10:30
DATEDIF and YEARFRAC

You can use the DATEDIF function to get time between dates in years, months, or
days. DATEDIF can also be configured to get total time in "normalized"
denominations, i.e. "2 years and 5 months and 27 days".

Use YEARFRAC to get fractional years:

=YEARFRAC("14-Nov-2018","10-Jun-2021") // returns 2.57

EDATE and EOMONTH

A common task with dates is to shift a date forward (or backward) by a given number
of months. You can use the EDATE and EOMONTH functions for this. EDATE
moves by month and retains the day. EOMONTH works the same way, but always
returns the last day of the month.

EDATE(date,6) // 6 months forward

EOMONTH(date,6) // 6 months forward (end of month)
WORKDAY and NETWORKDAYS

To figure out a date n working days in the future, you can use the WORKDAY
function. To calculate the number of workdays between two dates, you can
use NETWORKDAYS.
WORKDAY(start,n,holidays) // date n workdays in future
Video: How to calculate due dates with WORKDAY

 
NETWORKDAYS(start,end,holidays) // number of workdays between dates
Note: Both functions automatically skip weekends (Saturday and Sunday) and will
also skip holidays, if provided. If you need more flexibility on what days are
considered weekends, see the WORKDAY.INTL
function and NETWORKDAYS.INTL function. 
WEEKDAY and WEEKNUM

To figure out the day of week from a date, Excel provides the WEEKDAY
function. WEEKDAY returns a number between 1-7 that indicates Sunday, Monday,
Tuesday, etc. Use the WEEKNUM function to get the week number in a given year.

 
=WEEKDAY(date) // returns a number 1-7
=WEEKNUM(date) // returns week number in year

Engineering
CONVERT

Most Engineering functions are pretty technical...you'll find a lot of functions for
complex numbers in this section. However, the CONVERT function is quite useful for
everyday unit conversions. You can use CONVERT to change units for distance,
weight, temperature, and much more.
=CONVERT(72,"F","C") // returns 22.2

Information Functions
ISBLANK, ISERROR, ISNUMBER, and ISFORMULA

Excel provides many functions for checking the value in a cell,

including ISNUMBER,  ISTEXT, ISLOGICAL, ISBLANK, ISERROR,
and ISFORMULA  These functions are sometimes called the "IS" functions, and they
all return TRUE or FALSE based on a cell's contents.
Excel also has ISODD and ISEVEN functions will test a number to see if it's even or
odd.
By the way, the green fill in the screenshot above is applied automatically with
a conditional formatting formula.
Logical Functions
Excel's logical functions are a key building block of many advanced formulas. Logical
functions return the boolean values TRUE or FALSE. If you need a primer on logical
formulas, this video goes through many examples.
AND, OR and NOT

The core of Excel's logical functions are the AND function, the OR function, and
the NOT function. In the screen below, each of these function is used to run a simple
test on the values in column B:
 =AND(B5>3,B5<9)
=OR(B5=3,B5=9)
=NOT(B5=2)
 Video: How to build logical formulas
 Guide: 50 examples of formula criteria
IFERROR and IFNA

The IFERROR function and IFNA function can be used as a simple way to trap and

handle errors. In the screen below, VLOOKUP is used to retrieve cost from a menu
item. Column F contains just a VLOOKUP function, with no error handling. Column
G shows how to use IFNA with VLOOKUP to display a custom message when an
unrecognized item is entered.
=VLOOKUP(E5,menu,2,0) // no error trapping
=IFNA(VLOOKUP(E5,menu,2,0),"Not found") // catch errors
Whereas IFNA only catches an #N/A error, the IFERROR function will catch any
formula error.
IF and IFS functions

The IF function is one of the most used functions in Excel. In the screen below, IF
checks test scores and assigns "pass" or "fail":

Multiple IF functions can be nested together to perform more complex logical tests.

New in Excel via Office 365, the IFS function can run multiple logical tests
without nesting IFs.
=IFS(C5<60,"F",C5<70,"D",C5<80,"C",C5<90,"B",C5>=90,"A")

Lookup and Reference Functions

VLOOKUP and HLOOKUP

Excel offers a number of functions to lookup and retrieve data. Most famous of all
is VLOOKUP:

=VLOOKUP(C5,$F$5:$G$7,2,TRUE)
More: 23 things to know about VLOOKUP.
HLOOKUP works like VLOOKUP, but expects data arranged horizontally:
=HLOOKUP(C5,$G$4:$I$5,2,TRUE)
INDEX and MATCH

For more complicated lookups, INDEX and MATCH offers more flexibility and

power:

=INDEX(C5:E12,MATCH(H4,B5:B12,0),MATCH(H5,C4:E4,0))
Both the INDEX function and the MATCH function are powerhouse functions that
turn up in all kinds of formulas.
More: How to use INDEX and MATCH
LOOKUP

The LOOKUP function has default behaviors that make it useful when solving certain
problems. LOOKUP assumes values are sorted in ascending order and always
performs an approximate match. When LOOKUP can't find a match, it will match the
next smallest value. In the example below we are using LOOKUP to find the last
entry in a column:

ROW and COLUMN

You can use the ROW function and COLUMN function to find row and column
numbers on a worksheet. Notice both ROW and COLUMN return values for
the current cell if no reference is supplied:
The row function also shows up often in advanced formulas that process data
with relative row numbers.
ROWS and COLUMNS

The ROWS function and COLUMNS function provide a count of rows in a reference.

In the screen below, we are counting rows and columns in an Excel Table named
"Table1".

Note ROWS returns a count of data rows in a table, excluding the header row. By the
way, here are 23 things to know about Excel Tables.
HYPERLINK

You can use the HYPERLINK function to construct a link with a formula. Note
HYPERLINK lets you build both external links and internal links:
=HYPERLINK(C5,B5)
GETPIVOTDATA

The GETPIVOTDATA function is useful for retrieving information from

existing pivot tables.

=GETPIVOTDATA("Sales",$B$4,"Region",I6,"Product",I7)
CHOOSE

The CHOOSE function is handy any time you need to make a choice based on a
number:
=CHOOSE(2,"red","blue","green") // returns "blue"
Video: How to use the CHOOSE function
TRANSPOSE

The TRANSPOSE function gives you an easy way to transpose vertical data to

horizontal, and vice versa.

{=TRANSPOSE(B4:C9)}
Note: TRANSPOSE is a formula and is therefore dynamic. If you just need to do a
one-time transpose operation, use Paste Special instead.
OFFSET

The OFFSET function is useful for all kinds of dynamic ranges. From a starting
location, it lets you specify row and column offsets, and also the final row and column
size. The result is a range that can be respond dynamically to changing conditions and
inputs. You can feed this range to other functions, as in the screen below, where
OFFSET builds a range that is fed to the SUM function:
=SUM(OFFSET(B4,1,I4,4,1)) // sum of Q3
INDIRECT

The INDIRECT function allows you to build references as text. This concept is a bit

tricky to understand at first, but it can be useful in many situations. Below, we are
using INDIRECT to get values from cell A1 in 5 different worksheets. Each reference
is dynamic. If a sheet name changes, the reference will update.

=INDIRECT(B5&"!A1") // =Sheet1!A1
The INDIRECT function is also used to "lock" references so they won't change, when
rows or columns are added or deleted. For more details, see linked examples at the
bottom of the INDIRECT function page.
Caution: both OFFSET and INDIRECT are volatile functions and can slow down
large or complicated spreadsheets.
STATISTICAL Functions
COUNT and COUNTA

You can count numbers with the COUNT function and non-empty cells

with COUNTA. You can count blank cells with COUNTBLANK, but in the screen
below we are counting blank cells with COUNTIF, which is more generally useful.

=COUNT(B5:F5) // count numbers

=COUNTA(B5:F5) // count numbers and text
=COUNTIF(B5:F5,"") // count blanks
COUNTIF and COUNTIFS

For conditional counts, the COUNTIF function can apply one criteria.

The COUNTIFS function can apply multiple criteria at the same time:
=COUNTIF(C5:C12,"red") // count red
=COUNTIF(F5:F12,">50") // count total > 50
=COUNTIFS(C5:C12,"red",D5:D12,"TX") // red and tx
=COUNTIFS(C5:C12,"blue",F5:F12,">50") // blue > 50
Video: How to use the COUNTIF function
SUM, SUMIF, SUMIFS

To sum everything, use the SUM function. To sum conditionally, use SUMIF or

SUMIFS. Following the same pattern as the counting functions,
the SUMIF function can apply only one criteria while the SUMIFS function can apply
multiple criteria.
=SUM(F5:F12) // everything
=SUMIF(C5:C12,"red",F5:F12) // red only
=SUMIF(F5:F12,">50") // over 50
=SUMIFS(F5:F12,C5:C12,"red",D5:D12,"tx") // red & tx
=SUMIFS(F5:F12,C5:C12,"blue",F5:F12,">50") // blue & >50
Video: How to use the SUMIF function
AVERAGE, AVERAGEIF, and AVERAGEIFS

Following the same pattern, you can calculate an average

with AVERAGE, AVERAGEIF, and AVERAGEIFS.

=AVERAGE(F5:F12) // all
=AVERAGEIF(C5:C12,"red",F5:F12) // red only
=AVERAGEIFS(F5:F12,C5:C12,"red",D5:D12,"tx") // red and tx
MIN, MAX, LARGE, SMALL

You can find largest and smallest values with MAX and MIN, and nth largest and
smallest values with LARGE and SMALL. In the screen below, "data" is the named
range C5:C13, used in all formulas.
=MAX(data) // largest
=MIN(data) // smallest
=LARGE(data,1) // 1st largest
=LARGE(data,2) // 2nd largest
=LARGE(data,3) // 3rd largest
=SMALL(data,1) // 1st smallest
=SMALL(data,2) // 2nd smallest
=SMALL(data,3) // 3rd smallest
Video: How to find the nth smallest or largest value
MINIFS, MAXIFS

The MINIFS and MAXIFS. These functions let you find minimum and maximum

values with conditions:
=MAXIFS(D5:D15,C5:C15,"female") // highest female
=MAXIFS(D5:D15,C5:C15,"male") // highest male
=MINIFS(D5:D15,C5:C15,"female") // lowest female
=MINIFS(D5:D15,C5:C15,"male") // lowest male
Note: MINIFS and MAXIFS are new in Excel via Office 365 and Excel 2019.

MODE

The MODE function returns the most commonly occurring number in a range:

=MODE(B5:G5) // returns 1
RANK

To rank values largest to smallest, or smallest to largest, use the RANK function:

Video: How to rank values with the RANK function

MATH Functions
ABS

To change negative values to positive use the ABS function.

=ABS(-134.50) // returns 134.50

RAND and RANDBETWEEN

Both the RAND function and RANDBEWTEEN function can generate random

numbers on the fly. RAND creates long decimal numbers between zero and 1.
RANDBETWEEN generates random integers between two given numbers.

=RAND() // between zero and 1

=RANDBETWEEN(1,100) // between 1 and 100
ROUND, ROUNDUP, ROUNDDOWN, INT

To round values up or down, use the ROUND function. To force rounding up to a

given number of digits, use ROUNDUP. To force rounding down,
use ROUNDDOWN. To discard the decimal part of a number altogether, use the INT
function.
=ROUND(11.777,1) // returns 11.8
=ROUNDUP(11.777) // returns 11.8
=ROUNDDOWN(11.777,1) // returns 11.7
=INT(11.777) // returns 11
MROUND, CEILING, FLOOR

To round values to a the nearest multiple use the MROUND function. The FLOOR

function and CEILING function also round to a given multiple. FLOOR forces
rounding down, and CEILING forces rounding up.

=MROUND(13.85,.25) // returns 13.75

=CEILING(13.85,.25) // returns 14
=FLOOR(13.85,.25) // returns 13.75
MOD

The MOD function returns the remainder after division. This sounds boring and
geeky, but MOD turns up in all kinds of formulas, especially formulas that need to do
something "every nth time". In the screen below, you can see how MOD returns zero
every third number when the divisor is 3:
SUMPRODUCT

The SUMPRODUCT function is powerful and versatile tool when dealing with all
kinds data. You can use SUMPRODUCT to easily count and sum based on criteria,
and you can use it in elegant ways that just don't work with COUNTIFS and SUMIFS.
In the screen below, we are using SUMPRODUCT to count and sum orders in March.
See the SUMPRODUCT page for details and links to many examples.

=SUMPRODUCT(--(MONTH(B5:B12)=3)) // count March

=SUMPRODUCT(--(MONTH(B5:B12)=3),C5:C12) // sum March
SUBTOTAL

The SUBTOTAL function is an "aggregate function" that can perform a number of

operations on a set of data. All told, SUBTOTAL can perform 11 operations,
including SUM, AVERAGE, COUNT, MAX, MIN, etc. (see this page for the full
list). The key feature of SUBTOTAL is that it will ignore rows that have been
"filtered out" of an Excel Table, and, optionally, rows that have been manually
hidden. In the screen below, SUBTOTAL is used to count and sum only the 7 visible
rows in the table:

=SUBTOTAL(3,B5:B14) // returns 7
=SUBTOTAL(9,F5:F14) // returns 9.54
AGGREGATE

Like SUBTOTAL, the AGGREGATE function can also run a number of aggregate

operations on a set of data and can optionally ignore hidden rows. The key differences
are that AGGREGATE can run more operations (19 total) and can also ignore errors.
In the screen below, AGGREGATE is used to perform MIN, MAX, LARGE and
SMALL operations while ignoring errors. Normally, the error in cell B9 would
prevent these functions from returning a result. See this page for a full list of
operations AGGREGATE can perform.
  
=AGGREGATE(4,6,values) // MAX ignore errors, returns 100
=AGGREGATE(5,6,values) // MIN ignore errors, returns 75

TEXT Functions
LEFT, RIGHT, MID

To extract characters from the left, right, or middle of text, use LEFT, RIGHT,

and MID functions:

=LEFT("ABC-1234-RED",3) // returns "ABC"

=MID("ABC-1234-RED",5,4) // returns "1234"
=RIGHT("ABC-1234-RED",3) // returns "RED"
LEN

The LEN function will return the length of a text string. LEN shows up in a lot of
formulas that count words or characters.

FIND, SEARCH

To look for specific text in a cell, use the FIND function or SEARCH function. These
functions return the numeric position of matching text, but SEARCH allows wildcards
and FIND is case-sensitive. Both functions will throw an error when text is not found,
so wrap in the ISNUMBER function to return TRUE or FALSE (example here).

=FIND("Better the devil you know","devil") // returns 12

=SEARCH("This is not my beautiful wife","bea*") // returns 12
REPLACE, SUBSTITUTE

To replace text by position, use the REPLACE function. To replace text by matching,

use the SUBSTITUTE function. In the first example, REPLACE removes the two
asterisks (**) by replacing the first two characters with an empty string (""). In the
second example, SUBSTITUTE removes all hash characters (#) by replacing "#" with
"".

=REPLACE("**Red",1,2,"") // returns "Red"

=SUBSTITUTE("##Red##","#","") // returns "Red"
CODE, CHAR

To figure out the numeric code for a character, use the CODE function. To translate
the numeric code back to a character, use the CHAR function. In the example below,
CODE translates each character in column B to its corresponding code. In column F,
CHAR translates the code back to a character.
=CODE("a") // returns 97
=CHAR(97) // returns "a"
Video: How to use theCODE and CHAR functions
TRIM, CLEAN

To get rid of extra space in text, use the TRIM function. To remove line breaks
and other non-printing characters, use CLEAN.

=TRIM(A1) // remove extra space

=CLEAN(A1) // remove line breaks
Video: How to clean text with TRIM and CLEAN
CONCAT, TEXTJOIN, CONCATENATE

New in Excel  via Office 365 are CONCAT and TEXTJOIN. The CONCAT
function lets you concatenate (join) multiple values, including a range of values
without a delimiter. The TEXTJOIN function does the same thing, but allows you to
specify a delimiter and can also ignore empty values.
=TEXTJOIN(",",TRUE,B4:H4) // returns "red,blue,green,pink,black"
=CONCAT(B7:H7) // returns "8675309"
Excel also provides the CONCATENATE function, but it doesn't offer special
features. I would't bother with it and would instead concatenate directly with the
ampersand (&) character in a formula.
EXACT

The EXACT function allows you to compare two text strings in a case-sensitive

manner.

UPPER, LOWER, PROPER

To change the case of text, use the UPPER, LOWER, and PROPER function

=UPPER("Sue BROWN") // returns "SUE BROWN"
=LOWER("Sue BROWN") // returns "sue brown"
=PROPER("Sue BROWN") // returns "Sue Brown"
Video: How to change case with formulas
TEXT

Last but definitely not least is the TEXT function. The text function lets you apply
number formatting to numbers (including dates, times, etc.) as text. This is especially
useful when you need to embed a formatted number in a message, like "Sale ends on
[date]".
Abbreviate name and words Placeholder

Add business days to a date Date - Add

or Subtract

Add business days to a date changing the days of the weekend Date - Add
or Subtract

Add business days to a date excluding public holidays Date - Add

or Subtract

Add days to a date excluding weekends Placeholder

Add days to a date excluding weekends and changing the days of the weekend Placeholder

Add days to a date excluding weekends and public holidays Placeholder

Add days to a date including weekends Date - Add

or Subtract

Add days to a date not landing on a weekend Date - Add

or Subtract

Add hours to a time Time - Add

or Subtract

Add minutes to a time Time - Add

or Subtract

Add months to a date Date - Add

or Subtract

Add seconds to a time Time - Add

or Subtract

Add weeks to a date Date - Add

or Subtract

Add working days to a date Placeholder

Add working days to a date changing the days of the weekend Placeholder

Add working days to a date excluding public holidays Placeholder

Add years to a date Date - Add

or Subtract

Average cells after subtraction Number -

Average

Average cells that contain numbers Number -

=TEXT(B5,"$#,##0.00")
=TEXT(B6,"000000")
="Save "&TEXT(B7,"0%")
="Sale ends "&TEXT(B8,"mmm d")

HR Metrics

Common Types of Human Resources Metrics

Time to hire.

The average length of time that it takes for you to hire a new employee, from the time of the job posting
to their acceptance of an employment offer. You can calculate this by adding up the time for each
individual hire and dividing it by the number of new hires in a given period.
Cost per hire.

How much does it cost for you to hire a new employee? This includes things like the recruiter's time, the
possible cost of listing a job on a third-party site, time spent interviewing, etc.

Employee turnover.

Every business wants to have low employee turnover rates. If you are frequently losing employees and
having to hire new ones, there might be an issue with your hiring or staffing process. Pay close attention
to this number.

Revenue per employee.

It is very easy to determine your revenue per employee. Just divide that company's total revenue for the
year by the number of employees that you have. You can also use this metric for individual
departments.

Billable hours per employee.

This metric may not be applicable to every business, but it is usually relevant for businesses that offer a
professional service like marketing agencies or legal firms. Not every employee will log hours that are
directly billable to a client, so employees should track their time to allow you to gauge this on an
individual basis. Essentially, the more billable hours per employee you have, the more revenue the
company is making.

Absenteeism.

Absenteeism is the amount of time that your employees are absent from work for any reason (vacation,
sick days, other). Metrics for absenteeism are given as a percentage of the total amount of available
working days.

Cost of HR per employee.

Hiring, training and managing your workforce costs money. If you look at your HR expenditures for the
previous month or year and then factor in the number of employees on your payroll, you can determine
how much each employee costs on average for HR. This will allow you to make adjustments and lower
costs.

Employee engagement.
Employee engagement is one of the most difficult metrics to obtain because it cannot be found using
your financial records. You can issue company-wide surveys to your employees and ask questions rated
one to five about their experience working in the company. The results can be averaged to rate your
level of employee engagement.

Cost of training per employee.

In most situations, the success of a new employee has to do with the quality of their training. Still, that
training has a cost. You need to pay people to spend time training new employees, you may need to
supply them with equipment and materials, and they may need some time before they are ready to
work without assistance. Look at your training expenditures and the number of employees you've
trained to find out how much each new trainee is costing you.

Diversity/EEOC numbers.

The diversity of your workforce is more than an arbitrary number - it could be the key to your success.
Diversity includes race, ethnicity, job type and salary. If your business is required to file an EEO-1 report
with the Equal Employment Opportunities Commission, then you should already have access to these
metrics.

8 Main HR Metrics and Examples of Their Use

https://status.net/articles/hr-metrics-hr-analytics/

1. Annual Turnover
There is a famous American idiom that says, “people vote with their feet.” Places of
employment can be victims of this ideology. When people are unsatisfied, they are likely to
“vote” by leaving. That is why this statistic will give leaders a sense of how well their
company is doing in keeping individuals engaged enough to stay. This metric is usually
calculated by taking the number of employees who left within 12 months and dividing them
by the average actual number of employees during the same period. Some leaders will
compare this number to companies who are in a similar industry.
2. Turnover Cost
The cost of an employee does not disappear if they decide to leave. In fact, it may even
temporarily increase. Companies need to understand the value of keeping employees on staff
because there are several expenses associated with their departure. Leaders can crunch the
numbers by taking the total cost of separation and adding costs for vacancy, recruitment, and
training.
3. Tenure
Since we know high turnover can yield high cost, it is helpful for leaders to find out how long
workers are staying with the company. Many will probably already have a sense of this about
the turnover rate, but leaders may be surprised to see how long the majority of employees are
choosing to stay at the company. If rates are low, this can help facilitate a conversation with
 HR about what may cause workers to want to leave so soon. Are there not enough
opportunities for promotion? Are salaries stagnant? Is a competitor stealing workers away?
Tenure is calculated by taking the average number of years of service across the whole
company. To get even more in-depth insight, leaders should take the tenure of various age
groups to see how the number changes. This can help leaders understand how generational
changes may be impacting the length of service at their organization.
4. Percent of Performance Goals Met or Exceeded
Are employees meeting or exceeding goals and objectives? If this number is high, then
kudos! If it is not, then leaders have to drill into the story behind the numbers to understand
what may be hindering employees. It could be outdated technology, a lack of clear
communication, bad hires, or a simple lack of motivation. This number will help leaders
work with HR to develop a plan to get performance numbers back on track. The formula for
this metric is taking the number of performance goals met or exceeded and dividing it by the
total number of company goals.
5. Engagement or Satisfaction Rating
This is one where leaders can hear directly from employees. HR can capture this metric by
taking the percent of employees engaged or satisfied overall. This statistic will likely tie in
with turnover, tenure, and percent of performance goals met or exceeded. If a large
percentage of employees are disengaged, this will spill into other areas that leaders need to be
aware of. Results will help leaders get a sense for how many initiatives they need to establish
to raise low engagement numbers.
6. Average Days Absent
How often are employees not showing up to work? What are the reasons they have to take
vacation or sick time? This information can be invaluable to leaders because it can reveal a
lot more information. Are there parents who need time off to deal with family obligations?
Are there employees who are juggling school and work? Leaders can work with these
individuals to develop work schedules that better accommodate them and can even suggest
remote work programs. Average days absent is calculated by taking an average of all
employee absent days.
7. Compensation or Benefit Revenue Ratio
How much is the company spending on salaries and benefits in relation to revenue? Can the
company sustain offering raises or a more detailed benefits package? These numbers will
give leaders a benchmark to know what they can offer to new and existing talent. Leaders can
take compensation or benefit costs and divide by total revenue to find the ratio.
8. HR Cost per Employee
For HR, this information can inform the strategy of metric keeping. How much is HR
spending per employee? This calculation can include the software needed to keep the metric,
training and development, and programs. This number will help leaders calculate ROI as
well. Leaders and HR professionals can calculate this number by taking the total number of
HR costs and dividing it by the number of employees.
 PART 3
5 Tips How to Use HR Analytics Effectively

We can discuss the various types of metrics human resource departments should
capture and talk about their importance. However, leaders and departments cannot
yield adequate results if they do not understand how to use this data in a practical
way that informs HR strategy. Here are some ideas that leaders can keep in mind
when using HR metrics to make well-informed decisions.

ProsperForms — Software for Effective Communication

1. Only Track What Aligns with Business Objectives

If the company’s mission is tied to customer service and innovation, then HR should establish
metrics that address these qualities. Leaders need to understand where they need to improve so they
can incorporate metrics that optimize business objectives.

2. Make Sure All Relevant Stakeholders Get the Information They Need

Some leaders may have different needs for specific information, but all stakeholders need to be
satisfied so they can make better business decisions. Even though the metrics are related to HR, the
finance department may need to be aware of costs per employee, and the operations department
might need absentee numbers to know how absences impact production.

3. Combine HR Metrics with Business Metrics

As stated earlier, HR metrics are only part of the story. Combining performance data with personality
assessments can reveal common characteristics that are associated with high (or low) performance.
Data can even show if a salary increase was effective in increasing productivity. Leaders need to
understand how personnel decisions impact overall business activities.

Always Ask “Why”

Leaders have to become inquisitive about HR metrics and the reason why results may come out a
certain way. Why did the salary increase seem to have a limited impact on productivity? Why is
tenure low among workers ages 25 to 35? Why are workers leaving to go to a competitor? Asking
“why” does not always have to accompany a negative issue. Leaders should be equally concerned
about the reason for successes so they can make calculated strategies to keep the progress going.

4. Decide What Is Essential for Your Company’s Culture

Is cost per hire low, but turnover high? Is work-life balance a significant reason for dissatisfaction
even though profit may be high? Leaders have to decide how important these factors are to their
business and determine how they want employees to interact with the organization. To understand the
actions leaders should take to address these issues, they first have to decide what type of company
they want to be. If they have not established values or an informal culture, then this needs to happen
before taking a measured approach regarding personnel issues.
 Human Resources is no longer a field that professionals can “feel” their way through. Leaders have
to encourage human resource professionals to understand the importance of data and strategy. It is
hard to improve what is not measured, and with so many factors impacting how and why workers
decide to stay with an organization, it is even more necessary to have metrics in place to understand
the complexities of a workforce.

Here is how ProsperForms solution helps:
How to use ProsperForms for regular status updates
1. Easily implement regular status updates for your team members by creating a status feed “How
did you contribute to the team’s goals this week?”.
2. No one forgets to fill in status updates because it sends automated reminders according to the
recurrence schedule you chose.
3. Provide guidance and feedback:
Each status update has a separate section for comments, which is used by team members to clarify
information and by leaders to provide guidance and feedback in context.
4. Use status updates for future reference and decrease time and efforts spent on monthly,
quarterly, and yearly reporting thanks to powerful filtering and export features.
5. Optionally, enrich reports with the latest updates automatically added from web apps your
team uses (such as project management tools, version control systems, support systems, financial
applications, CRM, etc.) by connecting these apps to your status feed.
6. Spend less time on meetings by making them more productive because everyone is on the same
page at all times.
7. Sharing: Status updates can be either
— exported to files and printed, or sent by email;
— shared with manager online; or
— shared online as company-wide or team-wide status reports, i.e., all team members share their
progress with each other.

5 Qualities that Make KPIs Effective and 5 Steps to Define a KPI [Business Metrics]

 Business Metrics and KPI DefinitionsPart 1

 5 Main Qualities of Effective KPIsPart 2
 5 Steps to Define a KPIPart 3
 KPI and Business Metrics Best PracticesPart 4
PART 1
Definitions
What Are Business Metrics?

Business metrics are quantifiable measurements that are used to track, measure, and analyze the
impact of business processes.
Each stakeholder has a specific metric that is important to them, and leaders need to remember this
when putting parameters in place to gather data. Business metrics should address interactions and
engagement with various key stakeholders: investors, customers, and multiple types of employees.
It is difficult to improve what is not measured and a businesses’ dedication to measuring the impact
of the business processes will determine how successful it is in meeting goals and objectives. This
brings us to the importance of KPIs, and how business metrics cannot survive without them.

What Is a KPI and What Makes It Useful?

What are your strategic goals? What are those detailed metrics that get to the heart of a problem or
hypothesis? These are Key Performance Indicators (KPIs). KPIs are used to help managers, and their
teams assess how close they are to meeting a goal. They are vital in assisting companies to improve
on specific metrics that will, in turn, improve overall business processes. They are useful because
they allow managers and department leaders to drill down into particular data points that may be a
part of a more significant issue.
Instead of just addressing increased revenue growth as a whole, KPIs will instead look at a segment
of this goal. For example, revenue could be low, and the main problem could be poor customer
service which is driving customers away. A KPI could be the average hold time for customers who
are on the phone, or the percentage of times a customer service representative was able to solve the
problem without needing to refer it to someone else. Tracking these data points gives leaders a
glimpse into what can improve a lagging business performance metric. If revenue does increase, then
managers and leaders can always come back to these metrics to see their impact on customer service
satisfaction.
ProsperForms — Software for Effective Communication
PART 2
5 Main Qualities That Make KPIs Effective

There is a reason why KPIs are now a primary buzzword in most innovative workplaces. However,
they are not only helpful in theory, but they can take another organization to the next level if they are
implemented correctly. KPIs are most effective when they are:

Relevant

Those who need the results most are in charge of managing them. A marketing manager is going to
be concerned with web analytics, while an executive wants to know the ROI on overall business
metrics. The challenge of the leader is to get the right KPIs to the right people.

Strategically Aligned

KPIs are not useful if they do not align with overall business goals and objectives . What is the long-
term strategic plan? KPIs should address this. It does not make sense to measure data points that do
not have a direct impact on the overall goals of the business.

Realistic
Every company should challenge themselves, this is how change and innovation happen. However,
leaders need to make sure that KPI metrics are not impossible. If they are, then it can seriously hurt
morale or de-motivate employees. Companies have to strike the right balance between achievable and
reach. If goals are being met too quickly, then leaders should go back to the drawing board to figure
out how they can challenge departments.

Measure What Is Critical

A long list of KPIs will not do the trick. Leaders need only to measure what is crucial to a specific
department’s success. Many times, companies will only have three to five critical KPI measurements.
This ensures that only what is most important is measured. Too many KPIs can take attention away
from what needs to be the focus.

Predictive

One of the primary purposes of KPIs is to use the results to make projections about correlations.
KPIs “indicate” how specific important measurements are impacting broader business goals. These
results should help leaders make intelligent conjectures about what they can do to create better
outcomes.
PART 3
How to Define a KPI – 5 Steps

Some use KPIs and business metrics interchangeably, but they are markedly different. Business
metrics are typically used to track all areas of the business. It is a broad and general measurement.
KPIs are used to measure particular and critical areas of performance. There are specific ways that
leaders can help departments define KPIs.
Gather key leaders from each department and discuss problems and issues their departments are
facing. Connect these issues to overall business goals and objectives to begin the process of defining
KPIs.
To get a better idea of the problem in each department, have leaders conduct a SWOT
analysis (Strength, Weakness, Opportunities, and Threats).
Begin to decipher how the weaknesses and threats posed by each department impact essential business
topics like revenue or customer satisfaction. A great way to begin to define KPIs is by using the IPA
rule:A. Is the measurement important?
B. Does it have the potential to be improved?
C. Does the department have the authority to change this indicator?If all of these can be checked off,
then the proposed metric can be turned into a KPI.
Also, each KPI should be consistent and reliable enough to produce the same results over time. This
reliability will help leaders also predict based on a correlation between the projected KPIs and overall
business goals.
Use the findings to determine KPIs that align with business goals and overall strategy. They should
be defined as either overall business KPIs or departmental. Each will have a different impact on how the
organization tracks them and applies their results to overall business metrics.
PART 4
KPI and Business Metrics Best Practices
Ease the Concerns of Employees

For some, when leaders want to begin measuring progress, this can be an indicator of management
trying to weed out low-performing employees or those who are not a “fit.” Leaders need to be
transparent with employees about the need for KPIs and how this informs overall business metrics. It
is important to get employees comfortable with the idea of measurement, and how the results can
improve business workflows and make their jobs more effective. The goal is not to overwhelm
workers. This may be the first time they hear about metrics, and some may have never heard about
KPIs. Therefore, it is essential to open the lines of communication and discuss why this is necessary.

Keep Everyone Up-To-Date on Progress

You do not watch a sports game without knowing the score in the upper right (or left) hand corner.
The same is true for KPIs. It is a good idea to have a daily scorecard visible in the office or on the
dashboard of a metrics software system that allows employees and leaders always to know where
their KPIs stand. This makes it easier to address low KPIs a lot sooner. Once workers see that
measurement is an integral part of business management, they will understand the importance of
taking continuous metrics. It will become a part of the business culture.

Don’t Copy KPIs, Pick the Right Ones for Your Purposes

It is easy to think that another company’s or department’s KPIs make sense for every business
processes. Some of them might, but leaders will never know what is best for their company if they do
not drill down and do their own analysis to see what makes sense to measure. Going through the
process of doing a SWOT analysis  or constructing SMART goals will give insight into how the
company is performing. KPIs need to be specific to the company to address its own business goals.

Ensure You Have Accurate Record Keeping

KPIs will not do much good to overall business metrics if they are incorrect. Leaders should try to
decrease the impact of human error, and have the IT team regularly check all software that is used to
collect data to make sure that everything is working smoothly. This may seem like a no-brainer, but it
is easy for programs and coding to malfunction. Technology is not perfect. Therefore, it is critical for
leaders to always have processes in place to check for workers or software that is producing
inaccurate results.

Sit in Your Customer’s Chair

What matters to them? Is the service or good provided meeting their needs? Leaders should listen to
customer satisfaction surveys, and see what customer are saying about the company on social media.
Yes, KPIs should address business needs, but they should also make the customer service process
even more satisfactory and efficient. Are clients receiving the messaging? Could response times be
better? This may be an excellent place to start to begin seeing where the company could improve.
Every department has an impact on the customer regardless of whether they directly interact with
them. Leaders need to remember this when they are designing KPIs and strategizing with
departments. The customer’s experience needs to be a priority and should inform the strategy for
selecting KPIs that impact business metrics.
 1. AIHR_HR_Metrics_Cheat_sheet as pdf & https://www.analyticsinhr.com/blog/14-hr-metrics-
examples/
2.

HR metrics examples in recruitment:

1. Time to hire (time in days)

An important metric for recruitment is the ‘time to hire’.

This is the number of days between a position opening up and a candidate signing the job contract.

It’s an excellent way to measure the efficiency of the recruitment process and provides insight

into the difficulty of filling a certain job position.

2. Cost per hire (total cost of hiring/the number of new hires)

Like the time to hire, the ‘cost per hire’ metric shows how much it costs the company to hire new
employees. This also serves as an indicator of the efficiency of the recruitment process.

3. Early turnover (percentage of recruits leaving in the first year)

This is arguably the most important metric to determine hiring success in a company.
This early leaver metric indicates whether there is a mismatch between the person and the company or
between the person and his/her position. Early turnover is also very expensive. It usually takes 6 to 12
months before employees have fully learned the ropes and reach their ‘Optimum Productivity Level’.
According to a 2014 Oxford Economics report, the lost output cost over this period averages £30,000
($43,700) for new hires.

4. Time since last promotion (avg time in months since last internal promotion)

This rather straightforward metric is useful in explaining why your high potentials leave.

HR metrics examples related to revenue

5. Revenue per employee (revenue/total number of employees)

This metric shows the efficiency of the organization as a whole. The ‘revenue per employee’ metric is an
indicator of the quality of hired employees. Check this Business Insider article to view how the top 12
tech companies in the world score on this metric.

6. Performance and potential (the 9-box grid)

The 9-box grid appears when measuring and mapping both an individual’s performance and potential in
three levels. This model shows which employees are underperformers, valued specialists, emerging
potentials or top talents. This metrics is great for differentiating between, for example, wanted and
unwanted turnover.

In another article, we wrote about the qualitative and quantitative ways to measure employee
performance. Metrics include Net Promoter Score, management by objectives, number of errors, 360-
degree feedback, forced ranking, etc.

7. Billable hours per employee

This is the most concrete example of a performance measure, and it is especially relevant in professional
service firms (e.g. law and consultancy firms). Relating this kind of performance to employee
engagement or other input metrics makes for an interesting analysis. Benchmarking this metrics
between different departments and managers/partners can also provide valuable insights.

8. Engagement rating

An engaged workforce is a productive workforce. Engagement might be the most important ‘soft’ HR
outcome. People who like their job and who are proud of their company are generally more engaged,
even if the work environment is stressful and pressure is high. Engaged employees perform better and
are more likely to perceive stress as an exciting challenge, not as a burden. Additionally, team
engagement is an important metric for a team manager’s success.

Other HR metrics examples

9. Cost of HR per employee (e.g. $ 600)

This metric shows the cost efficiency of HR expressed in dollars.

10. Ratio of HR professionals to employees (e.g. 1:60)

Another measure that shows HR’s cost efficiency. An organization with fully developed analytical
capabilities should be able to have a smaller number of HR professionals do more.

1. Ratio of HR business partners per employee (e.g. 1:80)

A similar metric to the previous one. Again, a set of highly developed analytics capabilities will enable HR
to measure and predict the impact of HR policies. This will enable HR to be more efficient and reduce
the number of business partners.

12. Turnover (number of leavers/total population in the organization)

This metric shows how many workers leave the company in a given year. When combined with, for
instance, a performance metric, the ‘turnover’ metric can track the difference in attrition in high and low
performers.

Preferably you would like to see low performers leave and high performers stay. This metric also
provides HR business partners with a great amount of information about the departments and functions
in which employees feel at home, and where in the organization they do not want to work. Additionally,
attrition could be a key metric in measuring a manager’s success.

13. Effectiveness of HR software

This is a more complex metric. Effectiveness of, for instance, learning and development software are
measured in the number of active users, average time on the platform, session length, total time on
platform per user per month, screen flow, and software retention. These metrics enable HR to
determine what works for the employees and what does not.
14. Absenteeism (absence percentage)

Like turnover, absenteeism is also a strong indicator of dissatisfaction and a predictor of turnover. This
metric can give information to prevent this kind of leave, as long-term absence can be very costly. Again,
differences between individual managers and departments are very interesting indicators of (potential)
problems and bottlenecks.

As you can see there are a lot of different examples of HR metrics. While some metrics are easier to
implement than others, all of them provide insights into the workforce and HR. Combining these insights
will prove vital for making substantiated decisions with proven impact. In a recent blog, we wrote about
HR metrics related to employee retention, absenteeism, and learning & development effectiveness.

Because of the interest in this topic, we decided to create a course on strategic HR metrics. Check it out!

So, what are HR metrics exactly?

Before you start to work with HR metrics, it’s important to make sure you understand how metrics can
work for you. What are HR metrics?

Human Resource metrics are measurements that help you to track key areas in HR data. The most
important areas are listed below. In this list of HR metrics, we included the key HR metrics examples
associated with those areas.

Organizational performance

Turnover percentages

% of regretted loss

Statistics on why personnel is leaving

Absence percentages and behavior

Recruitment (time to fill, number of applicants, recruitment cost)

HR operations

HR efficiency (e.g. time to resolving HR self-service tickets)

HR effectiveness (e.g. perception of HR service quality)

Process optimization
Process optimization helps to analyze how we do what we do in Human Resource Management. The HR
metrics and analytics in this area focus on changes in HR efficiency and effectiveness over time. These
HR metrics and analytics are then used to re-engineer and reinvent what is happening in HR. This helps
to optimize the Human Resource delivery process. Process optimization metrics are next-level. They are
still very rare in modern organizations as they require a very high level of both data maturity and
analytics maturity.

The Top 10 Strategic Talent Acquisition and HR Metrics That CEOs

Want to See

1. Revenue per employee

2. The improvement in the performance of new hires (quality of hire improvement)
3. Performance turnover in key jobs
4. Dollars of revenue lost due to position vacancy days
5. Track a metric covering the highest impact current “hot” talent problem at your firm
6. A contribution to productivity survey to identify which HR programs helped to increase productivity
7. The percentage of HR strategic goals that were met

Strategic Metrics for the Recruiting Function

1. New hire failure rate
2. Applications per employee
3. Diversity hires in customer-impact positions

Impress the CEO using HR analytics

1. Quantify the HR metric area on revenue dollars, so that, it is easily comparable to other Business
metrics and problems.
2. Show a predictive trendline.
3. Give solution based on the problem.

Useful Links

Overall Complete Excel and VBA guide: https://www.techonthenet.com/excel/index.php

VBA complete: https://www.tutorialspoint.com/vba/

#1 excel tutorial with 300 examples: https://www.excel-easy.com/

Power BI tutorials: https://courses.yodalearning.com/courses/enrolled/657168

https://www.guru99.com/vba-tutorial.html

Web scraping using VBA: https://www.guru99.com/data-scraping-vba.html

Excel VBA: https://www.homeandlearn.org/index.html

VBA Code Excel Macro Examples Useful 100+ How Tos for Basic and Advanced Users:
https://analysistabs.com/excel-vba/codes-examples-macros-how-tos-most-useful-basics-advanced/

Download Example VBA Files,Tools, Dashboards for Data Analysis: https://analysistabs.com/downloads/

HR opensource: https://hros.co/templates

https://chandoo.org/wp/free-excel-templates-download/

https://chandoo.org/wp/figuring-out-employee-churn-with-power-query-hr-analytics/

https://chandoo.org/wp/excel-formula-list/