excel formulas exceljet
TRANSCRIPT
-
7/25/2019 Excel Formulas Exceljet
1/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 1/19
LoginCart
Quick, clean, and to the point
Courses Videos Blog Functions Shortcuts
Search...
Searchfor functions here Filter Show all
143 Excel Functions for the MinimalistYes, sometimes less is more. Required arguments in dark gray, optional
arguments in white. Also see our list of formula examples, which show these
functions in action.
Date and time
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
151Shares
53
45
30
18
5
https://exceljet.net/excel-functions/excel-date-functionhttps://exceljet.net/https://exceljet.net/https://exceljet.net/excel-functions/excel-day-functionhttps://exceljet.net/excel-functions/excel-datevalue-functionhttps://exceljet.net/excel-functions/excel-datedif-functionhttps://exceljet.net/excel-functions/excel-date-functionhttps://exceljet.net/formulashttps://exceljet.net/keyboard-shortcutshttps://exceljet.net/excel-functionshttps://exceljet.net/bloghttps://exceljet.net/excel-tipshttps://exceljet.net/cataloghttps://exceljet.net/https://exceljet.net/carthttps://exceljet.net/user/login -
7/25/2019 Excel Formulas Exceljet
2/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 2/19
Get the day as a number (1-31) from a date
date
DAYS360
Get days between 2 dates in a 360-day year
start_date start_date method
EDATE
Get the same date in future or past months
start_date months
EOMONTHGet 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
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.INTLGet work days between two dates
start_date end_date weekend holidays
https://exceljet.net/excel-functions/excel-networkdays.intl-functionhttps://exceljet.net/excel-functions/excel-networkdays-functionhttps://exceljet.net/excel-functions/excel-month-functionhttps://exceljet.net/excel-functions/excel-minute-functionhttps://exceljet.net/excel-functions/excel-hour-functionhttps://exceljet.net/excel-functions/excel-eomonth-functionhttps://exceljet.net/excel-functions/excel-edate-functionhttps://exceljet.net/excel-functions/excel-days360-function -
7/25/2019 Excel Formulas Exceljet
3/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 3/19
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
TIMEVALUEGet a valid time from a text string
time_text
TODAY
Get the current date
WEEKDAYGet the day of the week as a number
serial_number return_type
WEEKNUM
Get the week number for a given date
serial_num 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 paststart_date days weekend holidays
https://exceljet.net/excel-functions/excel-workday.intl-functionhttps://exceljet.net/excel-functions/excel-workday-functionhttps://exceljet.net/excel-functions/excel-weeknum-functionhttps://exceljet.net/excel-functions/excel-weekday-functionhttps://exceljet.net/excel-functions/excel-today-functionhttps://exceljet.net/excel-functions/excel-timevalue-functionhttps://exceljet.net/excel-functions/excel-time-functionhttps://exceljet.net/excel-functions/excel-second-functionhttps://exceljet.net/excel-functions/excel-now-function -
7/25/2019 Excel Formulas Exceljet
4/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 4/19
YEAR
Get the year from a date
date
YEARFRAC
Get the fraction of a year between two dates
start_date end_date basis
Engineering
CONVERT
Convert measurement units
number from_unit to_unit
Financial
FV
Get the future value of an investment
rate nper pmt pv type
NPER
Get the number of periods for an investment
rate pmt pv fv type
PMT
Get the periodic payment for a loan
rate pmt pv fv type
PVGet the present value of an investment
rate nper pmt fv type
https://exceljet.net/excel-functions/excel-pv-functionhttps://exceljet.net/excel-functions/excel-pmt-functionhttps://exceljet.net/excel-functions/excel-nper-functionhttps://exceljet.net/excel-functions/excel-fv-functionhttps://exceljet.net/excel-functions/excel-convert-functionhttps://exceljet.net/excel-functions/excel-yearfrac-functionhttps://exceljet.net/excel-functions/excel-year-function -
7/25/2019 Excel Formulas Exceljet
5/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 5/19
RATE
Get the interest rate per period of an annuity
nper pmt pv fv type guess
Information
CELL
Get information about a cell
info_type reference
ERROR.TYPE
Test for a specific error value
error_val
ISBLANK
Test if a cell is empty
value
ISERR
Test for any error but #N/A
value
ISERROR
Test for any error
value
ISEVEN
Test if a value is even
value
ISLOGICAL
Test if a value is logical
value
https://exceljet.net/excel-functions/excel-islogical-functionhttps://exceljet.net/excel-functions/excel-iseven-functionhttps://exceljet.net/excel-functions/excel-iserror-functionhttps://exceljet.net/excel-functions/excel-iserr-functionhttps://exceljet.net/excel-functions/excel-isblank-functionhttps://exceljet.net/excel-functions/excel-errortype-functionhttps://exceljet.net/excel-functions/excel-cell-functionhttps://exceljet.net/excel-functions/excel-rate-function -
7/25/2019 Excel Formulas Exceljet
6/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 6/19
ISNA
Test for the #N/A error
value
ISNONTEXTTest for a non-text value
value
ISNUMBER
Test for numeric value
value
ISODD
Test if a value is odd
value
ISREF
Test for a referencevalue
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
https://exceljet.net/excel-functions/excel-type-functionhttps://exceljet.net/excel-functions/excel-na-functionhttps://exceljet.net/excel-functions/excel-n-functionhttps://exceljet.net/excel-functions/excel-istext-functionhttps://exceljet.net/excel-functions/excel-isref-functionhttps://exceljet.net/excel-functions/excel-isodd-functionhttps://exceljet.net/excel-functions/excel-isnumber-functionhttps://exceljet.net/excel-functions/excel-isnontext-functionhttps://exceljet.net/excel-functions/excel-isna-function -
7/25/2019 Excel Formulas Exceljet
7/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 7/19
Logical
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
NOT
Reverse arguments or results
logical
OR
Test multiple conditions with OR
logical1 logical2 ...
TRUE
Generate the logical value TRUE
Lookup and reference
ADDRESS
https://exceljet.net/excel-functions/excel-address-functionhttps://exceljet.net/excel-functions/excel-true-functionhttps://exceljet.net/excel-functions/excel-or-functionhttps://exceljet.net/excel-functions/excel-not-functionhttps://exceljet.net/excel-functions/excel-iferror-functionhttps://exceljet.net/excel-functions/excel-if-functionhttps://exceljet.net/excel-functions/excel-false-functionhttps://exceljet.net/excel-functions/excel-and-function -
7/25/2019 Excel Formulas Exceljet
8/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 8/19
Create a cell address from a given row and column
row_num col_num abs _num a1 s heet
AREAS
Get the number of areas in a reference.
reference
CHOOSE
Get a value from a list based on position
index_num value1 value2 ...
COLUMNGet the column number of a reference.
reference
COLUMNS
Get the number of columns in an array or reference.
array
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_location friendly_name
INDEX
Get a value in a list or table based on location
array row_num col_num area_num
INDIRECTCreate a reference from text
ref_text a1
https://exceljet.net/excel-functions/excel-indirect-functionhttps://exceljet.net/excel-functions/excel-index-functionhttps://exceljet.net/excel-functions/excel-hyperlink-functionhttps://exceljet.net/excel-functions/excel-hlookup-functionhttps://exceljet.net/excel-functions/excel-columns-functionhttps://exceljet.net/excel-functions/excel-column-functionhttps://exceljet.net/excel-functions/excel-choose-functionhttps://exceljet.net/excel-functions/excel-areas-function -
7/25/2019 Excel Formulas Exceljet
9/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 9/19
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
TRANSPOSE
Flip the orientation of a range of cells
array
VLOOKUPLookup a value in a table by matching on the first column
value table col_index range_lookup
Math
ABS
Find the absolute value of a number
https://exceljet.net/excel-functions/excel-abs-functionhttps://exceljet.net/excel-functions/excel-vlookup-functionhttps://exceljet.net/excel-functions/excel-transpose-functionhttps://exceljet.net/excel-functions/excel-rows-functionhttps://exceljet.net/excel-functions/excel-row-functionhttps://exceljet.net/excel-functions/excel-offset-functionhttps://exceljet.net/excel-functions/excel-match-functionhttps://exceljet.net/excel-functions/excel-lookup-function -
7/25/2019 Excel Formulas Exceljet
10/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 10/19
number
CEILING
Round a number up to the nearest specified multiple
number multiple
COS
Get the cosine of an angle
number
DEGREES
Converts an angle into degrees
angle
EVEN
Round a number up to the next even integer
number
EXPFind the value of e raised to the power of a number
number
FACT
Find the factorial of a number
number
FLOOR
Round a number down to the nearest specified multiple
number multiple
GCD
Get the greatest common divisor of two or more numbersnumber1 number2 ...
https://exceljet.net/excel-functions/excel-gcd-functionhttps://exceljet.net/excel-functions/excel-floor-functionhttps://exceljet.net/excel-functions/excel-fact-functionhttps://exceljet.net/excel-functions/excel-exp-functionhttps://exceljet.net/excel-functions/excel-even-functionhttps://exceljet.net/excel-functions/excel-degrees-functionhttps://exceljet.net/excel-functions/excel-cos-functionhttps://exceljet.net/excel-functions/excel-ceiling-function -
7/25/2019 Excel Formulas Exceljet
11/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 11/19
INT
Get the integer part of a decimal by rounding down
number
LCM
Get the least common multiple or two or more numbers
number1 number2 ...
LOG
Get the logarithm of a number
number base
LOG10
Get the base-10 logarithm of a number
number
MOD
Get the remainder from division
number divisor
MROUND
Round a number to the nearest specified multiple
number multiple
ODDRound a number up to the next odd integer
number
PI
Get the value of
RADIANSConverts an angle into radians
angle
https://exceljet.net/excel-functions/excel-radians-functionhttps://exceljet.net/excel-functions/excel-pi-functionhttps://exceljet.net/excel-functions/excel-odd-functionhttps://exceljet.net/excel-functions/excel-mround-functionhttps://exceljet.net/excel-functions/excel-mod-functionhttps://exceljet.net/excel-functions/excel-log10-functionhttps://exceljet.net/excel-functions/excel-log-functionhttps://exceljet.net/excel-functions/excel-lcm-functionhttps://exceljet.net/excel-functions/excel-int-function -
7/25/2019 Excel Formulas Exceljet
12/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 12/19
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
ROUNDDOWNRound 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
SIGN
Get the sign of a number.
number
SIN
Get the sine of an anglenumber
SQRT
Find the positive square root of a number
number
SUBTOTAL
Get a subtotal in a list or database
function_num ref1 ref2 ...
https://exceljet.net/excel-functions/excel-subtotal-functionhttps://exceljet.net/excel-functions/excel-sqrt-functionhttps://exceljet.net/excel-functions/excel-sin-functionhttps://exceljet.net/excel-functions/excel-sign-functionhttps://exceljet.net/excel-functions/excel-roundup-functionhttps://exceljet.net/excel-functions/excel-rounddown-functionhttps://exceljet.net/excel-functions/excel-round-functionhttps://exceljet.net/excel-functions/excel-randbetween-functionhttps://exceljet.net/excel-functions/excel-rand-function -
7/25/2019 Excel Formulas Exceljet
13/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 13/19
SUM
Add numbers together
number1 number2 number3 ...
SUMIFSum 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 ...
TAN
Get the tangent of an angle.
number
TRUNC
Truncate a number to a given precision
number num_digits
Statistical
AVERAGE
Get the average of a group of numbers
number1 number2 ...
AVERAGEA
Get the average of a group of numbers and text
https://exceljet.net/excel-functions/excel-averagea-functionhttps://exceljet.net/excel-functions/excel-average-functionhttps://exceljet.net/excel-functions/excel-trunc-functionhttps://exceljet.net/excel-functions/excel-tan-functionhttps://exceljet.net/excel-functions/excel-sumproduct-functionhttps://exceljet.net/excel-functions/excel-sumifs-functionhttps://exceljet.net/excel-functions/excel-sumif-functionhttps://exceljet.net/excel-functions/excel-sum-function -
7/25/2019 Excel Formulas Exceljet
14/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 14/19
value1 value2 ...
AVERAGEIF
Get the average of numbers that meet criteria
range criteria average_range
AVERAGEIFS
Average cells that match multiple criteria
avg_rng range1 criteria1 range2 criteria2 ...
COUNT
Count numbers
value1 value2 ...
COUNTA
Count the number of non-blank cells
value1 value2 ...
COUNTBLANKCount cells that are blank
range
COUNTIF
Count cells that match criteria
range criteria
COUNTIFS
Count cells that match multiple criteria
range1 criteria1 range2 criteria2 ...
FREQUENCY
Get the frequency of values in a data setdata_array bins_array
https://exceljet.net/excel-functions/excel-frequency-functionhttps://exceljet.net/excel-functions/excel-countifs-functionhttps://exceljet.net/excel-functions/excel-countif-functionhttps://exceljet.net/excel-functions/excel-countblank-functionhttps://exceljet.net/excel-functions/excel-counta-functionhttps://exceljet.net/excel-functions/excel-count-functionhttps://exceljet.net/excel-functions/excel-averageifs-functionhttps://exceljet.net/excel-functions/excel-averageif-function -
7/25/2019 Excel Formulas Exceljet
15/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 15/19
LARGE
Get the nth largest value
array n
MAX
Get the largest value
array
MEDIAN
Get the median of a group of numbers
number1 number2 ...
MIN
Get the smallest value.
array
MODE
Get the mode of a group of numbers
number1 number2 ...
RANK
Rank a number against a range of numbers
number array order
SMALLGet the nth smallest value
array n
STDEV
Get the standard deviation in a sample
number1 number2 ...
STDEV.P
Get standard deviation of population
https://exceljet.net/excel-functions/excel-stdev.p-functionhttps://exceljet.net/excel-functions/excel-stdev-functionhttps://exceljet.net/excel-functions/excel-small-functionhttps://exceljet.net/excel-functions/excel-rank-functionhttps://exceljet.net/excel-functions/excel-mode-functionhttps://exceljet.net/excel-functions/excel-min-functionhttps://exceljet.net/excel-functions/excel-median-functionhttps://exceljet.net/excel-functions/excel-max-functionhttps://exceljet.net/excel-functions/excel-large-function -
7/25/2019 Excel Formulas Exceljet
16/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 16/19
number1 number2 ...
STDEV.S
Get the standard deviation in a sample
number1 number2 ...
STDEVP
Get standard deviation of population
number1 number2 ...
Text
CHAR
Get a character from a number
number
CLEAN
Strip non-printable characters from text
text
CODE
Get the code for a character
text
CONCATENATE
Join text together
text1 text2 text3 ...
DOLLAR
Convert a number to text in currency format
number decimals
EXACT
https://exceljet.net/excel-functions/excel-exact-functionhttps://exceljet.net/excel-functions/excel-dollar-functionhttps://exceljet.net/excel-functions/excel-concatenate-functionhttps://exceljet.net/excel-functions/excel-code-functionhttps://exceljet.net/excel-functions/excel-clean-functionhttps://exceljet.net/excel-functions/excel-char-functionhttps://exceljet.net/excel-functions/excel-stdevp-functionhttps://exceljet.net/excel-functions/excel-stdevs-function -
7/25/2019 Excel Formulas Exceljet
17/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
https://exceljet.net/excel-functions 17/19
Compare two text strings
text1 text2
FIND
Get the location of text in a string
find_text within_text start_num
LEFT
Extract text from the left of a string
text num_chars
LENGet the length of text.
text
LOWER
Convert text to lower case
text
MID
Extract text from inside a string
text start_num num_chars
PROPER
Capitalize the first letter in each word
text
REPLACE
Replace text based on location
old_text start_num num_chars new_text
REPTRepeat text as specified
text number_times
https://exceljet.net/excel-functions/excel-rept-functionhttps://exceljet.net/excel-functions/excel-replace-functionhttps://exceljet.net/excel-functions/excel-proper-functionhttps://exceljet.net/excel-functions/excel-mid-functionhttps://exceljet.net/excel-functions/excel-lower-functionhttps://exceljet.net/excel-functions/excel-len-functionhttps://exceljet.net/excel-functions/excel-left-functionhttps://exceljet.net/excel-functions/excel-find-function -
7/25/2019 Excel Formulas Exceljet
18/18
12/26/2015 143 Excel Functions for the Minimalist | Exceljet
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
TEXT
Convert a number to text in a number format
value format_text
TRIM
Remove extra spaces from text
text
UPPER
Convert text to upper case
text
VALUEConvert text to a number
text
Can we ask you a favor? Can you please share this page if it's useful to you?
Thanks!
Already I have found your hints and tips and videos a
https://exceljet.net/excel-functions/excel-value-functionhttps://exceljet.net/excel-functions/excel-upper-functionhttps://exceljet.net/excel-functions/excel-trim-functionhttps://exceljet.net/excel-functions/excel-text-functionhttps://exceljet.net/excel-functions/excel-substitute-functionhttps://exceljet.net/excel-functions/excel-search-functionhttps://exceljet.net/excel-functions/excel-right-function