Below are over 200 Excel functions to help you become a world-class financial analyst.
| DATE | Create a valid date from year, month, and day |
year
month
day
|
| DATEDIF | Get days, months, or years between two dates |
start_date
end_date
unit
|
| DATEVALUE | Convert a date in text format to a valid date |
date_text
|
| DAY | Get the day as a number (1-31) from a date |
date
|
| DAYS | Get days between dates |
end_date
start_date
|
| DAYS360 | Get days between 2 dates in a 360-day year |
start_date
end_date
method
|
| EDATE | Get the same date in future or past months |
start_date
months
|
| EOMONTH | Get the last day of the month in future or past months |
start_date
months
|
| HOUR | Get the hour as a number (0-23) from a time |
serial_number
|
| ISOWEEKNUM | Get ISO week number for a given date |
date
|
| MINUTE | Get the minute as a number (0-59) from a time |
serial_number
|
| MONTH | Get the month as a number (1-12) from a date |
date
|
| NETWORKDAYS | Get the number of working days between two dates |
start_date
end_date
holidays
|
| NETWORKDAYS.INTL | Get work days between two dates |
start_date
end_date
weekend
holidays
|
| NOW | Get the current date and time |
-
|
| SECOND | Get the second as a number (0-59) from a time |
serial_number
|
| TIME | Create a time with hours, minutes, and seconds |
hour
minute
second
|
| TIMEVALUE | Get a valid time from a text string |
time_text
|
| TODAY | Get the current date |
-
|
| WEEKDAY | Get the day of the week as a number |
serial_number
return_type
|
| WEEKNUM | Get the week number for a given date |
serial_number
return_type
|
| WORKDAY | Get a date n working days in the future or past |
start_date
days
holidays
|
| WORKDAY.INTL | Get date n working days in future or past |
start_date
days
weekend
holidays
|
| YEAR | Get the year from a date |
date
|
| YEARFRAC | Get the fraction of a year between two dates |
start_date
end_date
basis
|
| ACCRINT | Get the accrued interest for a security that pays interest on a periodic basis |
issue
first
interest
settlement
rate
par
frequency
basis
calc_method
|
| ACCRINTM | Get the accrued interest for a security that pays interest on maturity |
issue
settlement
rate
par
basis
|
| CUMIPMT | Get the cumulative interest paid on a loan |
rate
nper
pv
start_period
end_period
type
|
| CUMPRINC | Get the cumulative principal paid on a loan |
rate
nper
pv
start_period
end_period
type
|
| DB | Calculate the depreciation of an asset |
cost
salvage
life
period
month
|
| DISC | Calculate the rate of discount for a bond |
settlement
maturity
pr
redemption
basis
|
| DOLLARDE | Converts a dollar value in fractional notation into a dollar value expressed in decimal notation |
fractional_dollar
fraction
|
| DOLLARFR | Convert a dollar value in decimal notation into a dollar value in fractional notation |
decimal_dollar
fraction
|
| DURATION | Get the duration of a security |
settlement
maturity
coupon
yield
frequency
basis
|
| EFFECT | Get the annual interest rate and the number of compounding periods per year |
nominal_rate
npery
|
| FV | Get the future value of an investment |
rate
nper
pmt
pv
type
|
| FVSCHEDULE | Get the future value of an investment with a variable interest rate |
principal
schedule
|
| INTRATE | Get the interest rate for a security |
settlement
maturity
investment
redemption
basis
|
| IPMT | Get principal for given period |
rate
per
nper
pv
fv
type
|
| IRR | Get the Internal Rate of Return for a given cash flow |
values
guess
|
| ISPMT | Get the interest paid during a specific period of Investment |
rate
per
nper
pv
|
| MDURATION | Get the duration of a security using the Modified Macauley method |
settlement
maturity
coupon
yld
frequency
basis
|
| MIRR | Get he modified internal rate of return for given periodic cash flows |
values
finance_rate
reinvest
rate
|
| NOMINAL | Get the nominal annual interest rate |
effect_rate
npery
|
| NPER | Get number of periods for loan or investment |
rate
pmt
pv
fv
type
|
| NPV | Get the Net Present Value (NPV) for periodic cash flows |
rate
value1
value2
|
| PDURATION | Get the time or specific number of periods required for an investment to reach a particular value |
rate
pv
fv
|
| PMT | Get the periodic payment for a loan |
rate
nper
pv
fv
type
|
| PPMT | Get the payment on the principal for an investment or a loan |
rate
per
pv
fv
type
|
| PRICE | Get the price of a bond per $100 face value |
settlement
maturity
rate
yld
redemption
frequency
basis
|
| PRICEDISC | Get the price of a bond per $100 face value of a discounted security |
settlement
maturity
discount
redemption
basis
|
| PRICEMAT | Get the price of a bond that pays interest at maturity per $100 face value |
settlement
maturity
issue
rate
yld
basis
|
| PV | Get the present value of an investment |
rate
nper
pmt
fv
type
|
| RATE | Get the interest rate per period of an annuity |
nper
pmt
pv
fv
type
guess
|
| RECEIVED | Get the amount received at maturity for a fully invested security |
settlement
maturity
investment
discount
basis
|
| SLN | Get the straight-line depreciation of an asset |
cost
salvage
life
|
| SYD | Get the sum-of-years' digits depreciation of an asset |
cost
salvage
life
per
|
| TBILLPRICE | Get the fair market value of a Treasury bill |
settlement
maturity
discount
|
| TBILLEQ | Get the bond-equivalent yield of a Treasury bill |
settlement
matirity
discount
|
| TBILLYIELD | Get the yield on a Treasury bill |
settlement
maturity
pr
|
| VDB | Get the depreciation of an asset using the Double Declining Balance method |
cost
salvage
life
start_period
end_period
factor
no_switch
|
| XIRR | Get the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic |
values
dates
guess
|
| XNPV | Get the Net Present Value (NPV) for a series of cash flows that may not be periodic |
rate
values
dates
|
| YIELD | Get the yield on a security |
settlement
maturity
rate
pr
redemption
frequency
basis
|
| YIELDDISC | Get the annual yield for a discounted security |
settlement
maturity
pr
redemption
basis
|
| YIELDMAT | Get the annual yield on a security |
settlement
maturity
issue
rate
pr
basis
|
| CONVERT | Convert measurement units |
number
from_unit
to_unit
|
| CELL | Get information about a cell |
info_type
reference
|
| ERROR.TYPE | Test for a specific error value |
error_val
|
| INFO | Get information about current environment |
type_text
|
| ISBLANK | Test if a cell is blank |
value
|
| ISERR | Test for any error but #N/A |
value
|
| ISERROR | Test for any error |
value
|
| ISEVEN | Test if a value is even |
value
|
| ISFORMULA | Test if a cell contains a formula |
reference
|
| ISLOGICAL | Test if a value is logical |
value
|
| ISNA | Test for the #N/A error |
value
|
| ISNONTEXT | Test for a non-text value |
value
|
| ISNUMBER | Test for numeric value |
value
|
| ISODD | Test if a value is odd |
value
|
| ISREF | Test for a reference |
value
|
| ISTEXT | Test for a text value |
value
|
| N | Convert a value to a number |
value
|
| NA | Create an #N/A error |
-
|
| TYPE | Get the type of value in a cell |
value
|
| AND | Test multiple conditions with AND |
logical1
logical2
...
|
| FALSE | Generate the logical value FALSE |
-
|
| IF | Test for a specific condition |
logical_test
value_if_true
value_if_false
|
| IFERROR | Trap and handle errors |
value
value_if_error
|
| IFNA | Returns a specified alternate value if a formula results in an #N/A error |
value
value_if_na
|
| IFS | Test multiple conditions, return first true |
test1
value1
test2
value2
...
|
| NOT | Reverse arguments or results |
logical
|
| OR | Test multiple conditions with OR |
logical1
logical2
...
|
| SWITCH | Evaluates a given expression and returns a result corresponding to the first matching value |
expression
value1
result1
default
value2
result2
...
|
| TRUE | Generate the logical value TRUE |
-
|
| XOR | Perform exclusive OR |
logical1
logical2
...
|
| ADDRESS | Create a cell address from a given row and column |
row_num
col_num
abs_num
a1
sheet
|
| AREAS | Get the number of areas in a reference |
reference
|
| CHOOSE | Get a value from a list based on position |
index_num
value1
value2
|
| COLUMN | Get the column number of a reference |
-
reference
|
| COLUMNS | Get the number of columns in an array or reference |
array
|
| FORMULATEXT | Get the formula in a cell |
reference
|
| GETPIVOTDATA | Get data from a pivot table in a formula |
data_field
pivot_table
field1
item1
...
|
| HLOOKUP | Look up a value in a table by matching on the first row |
value
table
row_index
range_lookup
|
| HYPERLINK | Create a clickable link |
link_locaation
friendly_name
|
| INDEX | Get a value in a list or table based on location |
array
row_num
col_num
area_num
|
| INDIRECT | Create a reference from text |
ref_text
a1
|
| LOOKUP | Look up a value in a one-column range |
lookup_value
lookup_vector
result_vector
|
| MATCH | Get the position of an item in an array |
lookup_value
lookup_array
match_type
|
| OFFSET | Create a reference offset from given starting point |
reference
rows
cols
height
width
|
| ROW | Get the row number of a reference |
-
reference
|
| ROWS | Get the number of rows in an array or reference |
array
|
| RTD | Get real-time data from a COM automation server |
progID
server
topic1
topic2
...
|
| TRANSPOSE | Flip the orientation of a range of cells |
array
|
| VLOOKUP | Lookup a value in a table by matching on the first column |
value
table
col_index
range_lookup
|
| ABS | Find the absolute value of a number |
number
|
| ARABIC | Convert a Roman numeral to an Arabic numerals |
roman_text
|
| BASE | Converts a number into the supplied base |
number
radix
min_length
|
| CEILING | Round a number up to the nearest specified multiple |
number
multiple
|
| CEILING.MATH | Round a number to the nearest integer or multiple of significance |
number
significance
mode
|
| CEILING.PRECISE | Round up a number to the nearest integer or multiple of significance |
number
significance
|
| COMBIN | Calculate the total number of combinations |
number
number_chosen
|
| COMBINA | Calculate the total number of combinations along with repetitions |
number
number_chosen
|
| DEGREES | Convert radians to degrees |
angle
|
| EVEN | Round a number up to the next even integer |
number
|
| ISO.CEILING | Get a number rounded up to the nearest integer or multiple of significance |
number
signficance
|
| MOD | Get the remainder from division |
number
divisor
|
| MROUND | Round a number to the nearest specified multiple |
number
multiple
|
| ODD | Round a number up to the next odd integer |
number
|
| PI | Get the value of π |
-
|
| RAND | Get a random number between 0 and 1 |
-
|
| RANDBETWEEN | Get a random integer between two values |
bottom
top
|
| ROUND | Round a number to a given number of digits |
number
num_digits
|
| ROUNDDOWN | Round a number down to a given number of digits |
number
num_digits
|
| ROUNDUP | Round a number up to a given number of digits |
number
num_digits
|
| SQRT | Find the positive square root of a number |
number
|
| SUBTOTAL | Get a subtotal in a list or database |
function_num
ref1
ref2
...
|
| SUM | Add numbers together |
number1
number2
number3
...
|
| SUMIF | Sum numbers in a range that meet supplied criteria |
range
criteria
sum_range
|
| SUMIFS | Sum cells that match multiple criteria |
sum_range
range1
criteria1
range2
criteria2
...
|
| SUMPRODUCT | Multiply, then sum arrays |
array1
array2
...
|
| TRUNC | Truncate a number to a given precision |
number
num_digits
|
| AVERAGE | Get the average of a group of numbers |
number1
number2
...
|
| AVERAGEA | Get the average of a group of numbers and text |
value1
value2
...
|
| AVERAGEIF | Get the average of numbers that meet criteria |
range
criteria
average-criteria
|
| AVERAGEIFS | Average cells that match multiple criteria |
avg_rng
range1
criteria1
range2
criteria2
...
|
| BINOM.DIST | Get the binomial distribution probability for the number of successes from the trials |
number_s
trials
probability_s
cumulative
|
| BINOM.DIST.RANGE | Get the binomial distribution probability for a given number of successes |
trials
probability_s
number_s
number_s2
|
| CHISQ.DIST | Get the chi-square distribution |
x
deg_freedom
cumulative
|
| CHISQ.DIST.RT | Get the right-tailed probability of a chi-square distribution |
x
deg_freedom
|
| CHISQ.INV | Get the inverse of the left-tailed probability of the chi-square distribution |
probability
deg_freedom
|
| CHISQ.INV.RT | Get the inverse of the right-tailed probability of a chi-square distribution |
probabilty
deg_freedom
|
| CHISQ.TEST | Get the chi-square distribution of two provided datasets |
actual_range
expected_range
|
| CONFIDENCE | Get the confidence interval for a population mean |
alpha
standard_dev
size
|
| CONFIDENCE.NORM | Get the confidence interval for a population mean |
alpha
standard_dev
size
|
| CONFIDENCE.T | Get the confidence value for the confidence interval of a population mean |
alpha
standard_dev
size
|
| CORREL | Calculate the correlation coefficient between two variables |
array1
array2
|
| COUNT | Count numbers |
value1
value2
...
|
| COUNTA | Count the number of non-blank cells |
value1
value2
...
|
| COUNTBLANK | Count cells that are blank |
range
|
| COUNTIF | Count cells that match criteria |
range
criteria
|
| COUNTIFS | Count cells that match multiple criteria |
range1
criteria1
range2
criteria2
...
|
| COVAR | Get the covariance of two sets of values |
array1
array2
|
| COVARIANCE.P | Get the covariance of two given set of values |
array1
array2
|
| COVARIANCE.S | Get the sample covariance for two sets of values |
array1
array2
|
| CRITBINOM | Get the smallest value that is greater than or equal to the criterion value in a Cumulative Binomial Distribution |
trials
probability_s
trials
|
| DEVSQ | Get the sum of the squared deviations from the sample mean |
number1
number2
|
| F.DIST | Calculate the probability density function |
x
deg_freedom1
deg_freedom2
cumulative
|
| F.DIST.RT | Calculate the (right-tailed) F Probability Distribution |
x
deg_freedom1
deg_freedom2
|
| F.INV | Calculate the inverse of the Cumulative F Distribution for a supplied probability |
x
deg_freedom1
deg_freedom2
|
| F.INV.RT | Calculate the inverse of the (right-tailed) F probability distribution |
probability
deg_freedom1
deg_freedom2
|
| FORECAST | Get the future value using existing values |
x
known_y’s
known_x’s
|
| FORECAST.LINEAR | Get the future value using linear progression |
x
known_y’s
known_x’s
|
| FREQUENCY | Get the frequency of values in a data set |
data_array
bins_array
|
| F.TEST | Get the result of an F-test for two given arrays or ranges |
array1
array2
|
| GAUSS | Get the probability that a member of a standard normal population will be between the mean and a specified number of standard deviations |
z
|
| GROWTH | Get the predicted exponential growth using existing data |
known_y’s
known_x’s
new_x’s
const
|
| LARGE | Get the nth largest value |
array
n
|
| LOGNORM.DIST | Get the cumulative log-normal distribution function at a given value of x |
x
mean
standard_dev
cumulative
|
| LOGNORM.INV | Get the inverse of the lognormal cumulative lognormal distribution function at a given value of x |
probability
mean
standard_dev
|
| MAX | Get the largest value |
number1
number2
...
|
| MAXA | Get the largest value |
value1
value2
...
|
| MAXIFS | Get maximum value with criteria |
max_range
range1
criteria1
range2
criteria2
...
|
| MEDIAN | Get the median of a group of numbers |
number1
number2
...
|
| MIN | Get the smallest value |
number1
number2
...
|
| MINA | Get the smallest value |
value1
value2
...
|
| MINIFS | Get minimum value with criteria |
min_range
range1
criteria1
range2
criteria2
...
|
| MODE | Get the mode of a group of numbers |
number1
number2
...
|
| MODE.MULT | Get a vertical array of statistical modes within a list of given numbers |
number1
number2
|
| MODE.SNGL | Get the most frequently occurring number in a numeric data set |
number1
number2
|
| NEGBINOM.DIST | Get the negative binomial distribution probability |
number_f
number_s
probabilty_s
cumulative
|
| NORMDIST | Get the normal distribution for a stated mean and standard distribution |
x
mean
standard_dev
cumulative
|
| NORM.DIST | Get the probability that variable x falls below or at a specified value |
x
mean
standard_dev
cumulative
|
| NORMINV | Get the inverse of a normal cumulative distribution |
probability
mean
standard_dev
|
| NORM.INV | Get the inverse of a normal cumulative distribution |
probability
mean
standard_dev
|
| NORM.S.DIST | Get the standard normal distribution function for a given value |
z
cumulative
|
| NORM.S.INV | Get the inverse of the normal cumulative distribution for a given probability value |
probability
|
| PEARSON | Get the Pearson Product-Moment Correlation Coefficient for two sets of values |
array1
array2
|
| PERCENTILE | Get the k-th percentile of the values in a range |
array
k
|
| PERCENTILERANK | Get the rank of a value in a data set |
array
x
significance
|
| PERMUT | Get the total number of permutations |
number
number_chosen
|
| PERMUTATIONA | Get the total number of permutations, with repetitions |
number
number_chosen
|
| PHI | Get the value of the density function for a standard normal distribution |
x
|
| POISSON.DIST | Get the Poisson probability mass function |
x
mean
cumulative
|
| PROB | Get he probability that is associated with a given range |
x_range
prob_range
lower_limit
upper_limit
|
| QUARTILE | Get the quartile in a data set |
array
quart
|
| QUARTILE.EXC | Get the quartile of a given data set based on percentile values from 0.1 exclusive |
array
quart
|
| QUARTILE.INC | Get the quartile of a given data set based on percentile values from 0.1 inclusive |
array
quart
|
| RANK | Rank a number against a range of numbers |
number
array
order
|
| SLOPE | Get the slope of a linear regression line |
known_y's
Known_x’s
|
| SMALL | Get the nth smallest value |
array
k
|
| STANDARDIZE | Get a normalized value (z-score) based on the mean and standard deviation |
x
mean
standard_dev
|
| STDEV | Get the standard deviation in a sample |
number1
number2
...
|
| STDEVA | Get the standard deviation in a sample |
number1
number2
...
|
| STDEVPA | Get the standard deviation of an entire population given as arguments |
value1
value2
|
| STDEV.P | Get the standard deviation in a population |
number1
number2
...
|
| STDEV.S | Get the standard deviation in a sample |
number1
number2
...
|
| T.DIST | Get the student's t-distribution |
x
deg_freedom
tails
|
| T.DIST.RT | Get the right-tailed student's t-distribution |
x
deg_freedom
|
| T.INV | Get the left-tailed student's t-distribution |
probability
deg_freedom
|
| T.INV.2T | Get the two-tailed Student's T-Distribution |
probability
deg_freedom
|
| T.TEST | Get the probability associated with a student’s t-test |
array1
array2
tails
type
|
| TREND | Get the linear trend line to the arrays of known_y’s and known_x’s |
known_y’s
known_x’s
new_x’s
const
|
| VARA | Get the sample variance of a set of values |
number1
number2
...
|
| VAR.P | Get the variance of a set of values (population) |
number1
number2
...
|
| VAR.S | Get the variance of a sample |
number1
number2
...
|
| WEIBULL.DIST | Get the Weibull distribution for a supplied set of parameters |
x
alpha
beta
cumulative
|
| Z.TEST | Get the one-tailed P-value of a Z-test |
array
x
sigma
|
| CHAR | Get a character from a number |
number
|
| CLEAN | Strip non-printable characters from text |
text
|
| CODE | Get the code for a character |
text
|
| CONCAT | Join text values without delimiter |
text1
text2
...
|
| CONCATENATE | Join text together |
text1
text2
text3
...
|
| DOLLARDE | Converts a dollar value in fractional notation into a dollar value in decimal notation |
fractional_dollar
fraction
|
| EXACT | Compare two text strings |
text1
text2
|
| FIND | Get the location of text in a string |
find_text
within_text
start_num
|
| FIXED | Rounds and converts a supplied number to text |
number
decimal
no_commas
|
| LEFT | Extract text from the left of a string |
text
num_chars
|
| LEN | Get the length of text |
text
|
| LOWER | Convert text to lower case |
text
|
| MID | Extract text from inside a string |
text
start_num
num_chars
|
| NUMBERVALUE | Converts a text string into a number in a locale-independent manner |
text
decimal_separator
group_separator
|
| PROPER | Capitalize the first letter in each word |
text
|
| REPLACE | Replace text based on location |
old_text
start_num
num_chars
new_text
|
| REPT | Repeat text as specified |
text
number_times
|
| RIGHT | Extract text from the right of a string |
text
num_chars
|
| SEARCH | Get the location of text in a string |
find_text
within_text
start_num
|
| SUBSTITUTE | Replace text based on content |
text
old_text
new_text
instance
|
| T | Tests if the value provided is a text or not |
value
|
| TEXT | Convert a number to text in a number format |
value
format_text
|
| TEXTJOIN | Join text values with a delimiter |
delimiter
ignore_empty
text1
text2
...
|
| TRIM | Remove extra spaces from text |
text
|
| UNICHAR | Get the Unicode character for a given number |
number
|
| UNICODE | Get the code point for a given character |
text
|
| UPPER | Convert text to upper case |
text
|
| VALUE | Convert text to a number |
text
|