excel: EXCEL FORMULAS

This page provides examples of how to use excel formulas to perform many common tasks. Although many people use the term 'Excel Formulas' to refer to the Excel Built-In Functions, the term 'Excel Formula' can encompass a wider range of operations in Excel. Therefore on this site, when we use the term 'Excel Formulas' (or 'Excel Formulae'), we are generally referring to any combination of Excel Operators and/or Excel Functions. This may be as simple as a basic addition (eg. "=A1+B1"), or it could be a complex combination of Excel Operators and multiple nested Excel Functions.

Web : excel

Count

Filter:

Formula	Related Functions
Count cells between dates	DATE COUNTIFS
Count cells between two numbers	COUNTIF COUNTIFS
Count cells equal to case sensitive	SUMPRODUCT EXACT
Count cells equal to either x or y	COUNTIF
Count cells equal to one of many things	SUMPRODUCT COUNTIF
Count cells greater than	COUNTIF
Count cells less than	COUNTIF
Count cells not equal to	COUNTIF
Count cells not equal to x or y	SUMPRODUCT COUNTIFS
Count cells that are blank	COUNTA
Count cells that are not blank	COUNTA
Count cells that begin with	COUNTIF
Count cells that contain either x or y	ISNUMBER SUMPRODUCT COUNTIF FIND
Count cells that contain errors	ISERR ISERROR SUMPRODUCT
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	MOD SUMPRODUCT
Count cells that contain positive numbers	COUNTIF
Count cells that contain specific text	ISNUMBER SUMPRODUCT COUNTIF FIND
Count cells that contain text	ISTEXT SUMPRODUCT COUNTIF
Count cells that do not contain	COUNTIF
Count cells that do not contain errors	ISERROR NOT SUMPRODUCT
Count cells that end with	COUNTIF
Count if row meets internal criteria	SUMPRODUCT
Count if row meets multiple internal criteria	SUMPRODUCT
Count if two criteria match	SUMPRODUCT COUNTIFS
Count matches between two columns	SUMPRODUCT
Count sold and remaining	COUNTA
Count total matches in two ranges	SUMPRODUCT COUNTIF

Sum

Filter:

Formula	Related Functions
3D SUMIF for multiple worksheets	INDIRECT SUMIF SUMPRODUCT
Calculate running total	SUM
Subtotal by color	SUMIF
Subtotal invoices by age	SUMIF
Sum bottom n values	INDIRECT ROW SUM SUMPRODUCT SMALL
Sum by group	SUMIF
Sum by month	EOMONTH
Sum columns based on adjacent criteria	SUMPRODUCT
Sum entire column	SUM
Sum every nth column	COLUMN MOD SUMPRODUCT
Sum if begins with	SUMIF
Sum if between
Sum if by year	DATE
Sum if cell contains text in another cell	SUMIF
Sum if cells are equal to	SUMIF
Sum if cells are not equal to	SUMIF
Sum if cells contain an asterisk	SUMIF
Sum if cells contain both x and y
Sum if cells contain either x or y	ISNUMBER SUMPRODUCT FIND SEARCH
Sum if cells contain specific text	SUMIF
Sum if date is between	DATE
Sum if date is greater than	DATE SUMIF
Sum if ends with	SUMIF
Sum if equal to either x or y	SUMIF SUMPRODUCT
Sum if greater than	SUMIF
Sum if less than	SUMIF
Sum if multiple criteria
Sum if not blank	SUMIF
Sum top n values	INDIRECT ROW SUM SUMPRODUCT LARGE
Sum visible rows in a filtered list	SUBTOTAL

Average

Formula	Related Functions
Average numbers	AVERAGE
Average numbers ignore zero	AVERAGEIF
Average top 3 scores	AVERAGE LARGE
Weighted average	SUM SUMPRODUCT

Min and Max

Filter:

Formula	Related Functions
Maximum if multiple criteria	IF MIN
Maximum value
Maximum value if	IF
Minimum if multiple criteria	IF MIN
Minimum value	MIN
Minimum value if	IF MIN
nth largest value	LARGE
nth largest value with criteria	LARGE
nth smallest value	SMALL
nth smallest value with criteria	SMALL

Conditional Formatting

Filter:

Formula	Related Functions
Highlight blank cells	ISBLANK LEN
Highlight cells that begin with	IFERROR COUNTIF FIND
Highlight cells that contain	ISNUMBER FIND SEARCH
Highlight cells that end with	COUNTIF EXACT LEN RIGHT
Highlight cells that equal	EXACT
Highlight column differences	NOT EXACT
Highlight dates between	DATE AND
Highlight dates greater than	DATE
Highlight dates in same month and year	DATE TEXT
Highlight dates in the next N days	TODAY AND
Highlight dates that are weekends	WEEKDAY OR
Highlight duplicate rows	SUMPRODUCT COUNTIF COUNTIFS
Highlight duplicate values	COUNTIF
Highlight entire rows
Highlight every other row	ISEVEN ISODD ROW MOD
Highlight integers only	MOD
Highlight rows that contain	FIND SEARCH
Highlight rows with dates between	DATE AND
Highlight unique values	COUNTIF
Highlight values between	AND
Highlight values greater than
Highlight values not between X and Y	AND NOT
Highlight values that don't exist	COUNTIF

Lookup

Filter:

Formula	Related Functions
Approximate match with multiple criteria	IF INDEX MATCH
Basic INDEX MATCH approximate	INDEX MATCH
Basic INDEX MATCH exact	INDEX MATCH
Calculate grades with VLOOKUP	VLOOKUP
Calculate shipping cost with VLOOKUP	VLOOKUP
Dynamic lookup table with INDIRECT	INDIRECT VLOOKUP
Exact match lookup with INDEX and MATCH	INDEX MATCH EXACT
Exact match lookup with SUMPRODUCT	SUMPRODUCT EXACT
Extract multiple matches into separate columns	IFERROR COLUMNS INDEX MATCH ROW SMALL
Faster VLOOKUP with 2 VLOOKUPS	NA VLOOKUP
Find missing values	MATCH VLOOKUP COUNTIF
Get address of lookup result	CELL INDEX MATCH
Get employee information with VLOOKUP	VLOOKUP
Get first match cell contains	ISNUMBER INDEX MATCH SEARCH
Get first non-blank value in a list	ISBLANK INDEX MATCH
Get first partial match in a range	INDEX MATCH
Get first text value in a list	VLOOKUP
Get nth match with INDEX / MATCH	INDEX MATCH SMALL
Get nth match with VLOOKUP	VLOOKUP COUNTIF
Group arbitrary text values	VLOOKUP
Group numbers with VLOOKUP	VLOOKUP
Lookup up cost for product or service	VLOOKUP
Map inputs to arbitrary values	CHOOSE VLOOKUP
Map text to numbers	VLOOKUP
Match first error	ISERROR MATCH
Max if criteria match	IF
Partial match against numbers with wildcard	MATCH TEXT
Partial match with VLOOKUP	VLOOKUP
Position of max value in list	MATCH
Related info for max value in range	INDEX MATCH

If

Filter:

Formula	Related Functions
If cell begins with x, y, or z	SUM COUNTIF
If cell contains	IF
If cell equals	IF
If cell is blank	ISBLANK IF
If cell is greater than	IF
If cell is not blank	ISBLANK IF NOT
If cell is this OR that	IF OR
If cell is x or y and z	AND IF OR
If else	IF
If NOT this or that	IF NOT OR
If this AND that	AND IF
Nested IF function example	IF

Round

Filter:

Formula	Related Functions
Get decimal part of a number	TRUNC
Get integer part of a number	INT TRUNC
Round a number	ROUND
Round a number down	ROUNDDOWN
Round a number down to nearest multiple	FLOOR
Round a number to n significant digits	ABS INT LOG10 ROUND
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 time to nearest 15 minutes	MROUND
Round to nearest 1000	ROUND
Round to nearest 5	CEILING FLOOR MROUND

Date And Time

Filter:

Formula	Related Functions
Add business days to date	NETWORKDAYS WORKDAY
Add workdays to date custom weekends	NETWORKDAYS
Calculate elapsed work time	IF MOD
Calculate years between dates	YEARFRAC INT
Convert date string to date time	DATEVALUE TIMEVALUE LEFT MID
Convert date to Julian format	DATE YEAR TEXT
Convert date to text	TEXT
Convert hours to time
Convert minutes to time
Convert seconds to time
Convert text to date	DATE LEFT MID RIGHT
Convert time to time zone	MOD
Count day of week between dates	WEEKDAY INDIRECT ROW SUMPRODUCT
Custom weekday abbreviation	WEEKDAY CHOOSE
Date is same month	MONTH
Date is same month and year	MONTH YEAR
Date is workday	WORKDAY
Days in month	DAY EOMONTH
Display the current date	TODAY
Display the current date and time	NOW
Extract date from a date and time	INT TRUNC
Extract time from a date and time	MOD
Get age from birthday	TODAY YEARFRAC INT
Get day from date	DAY
Get day name from date	WEEKDAY CHOOSE
Get days before a date	TODAY
Get days between dates	DAYS TODAY
Get days between dates ignoring years	DATEDIF
Get first day of month	DAY
Get first day of previous month	EOMONTH

Date Series

Formula	Related Functions
Series of dates by day
Series of dates by month	DATE DAY EOMONTH MONTH YEAR
Series of dates by weekends	WEEKDAY IF
Series of dates by workdays	WEEKDAY WORKDAY IF
Series of dates by year	DATE DAY MONTH YEAR

Text

Filter:

Formula	Related Functions
Add a line break with a formula	CHAR
Add line break based on OS	INFO CHAR
Cell contains all of many things	ISNUMBER SUMPRODUCT COUNTA SEARCH
Cell contains any numbers	COUNT FIND
Cell contains one of many things	ISNUMBER SUMPRODUCT SEARCH
Cell contains some words but not others	AND COUNT SEARCH
Cell contains specific text	ISNUMBER FIND SEARCH
Cell contains which things	ISNUMBER FIND SEARCH
Cell equals one of many things	SUMPRODUCT
Clean and reformat telephone numbers	SUBSTITUTE
Compare two strings	EXACT
Count line breaks in cell	ISBLANK CHAR LEN SUBSTITUTE
Count specific characters in a cell	LEN SUBSTITUTE UPPER
Count specific characters in a range	SUMPRODUCT LEN SUBSTITUTE UPPER
Count specific words in a cell	LEN SUBSTITUTE UPPER
Count specific words in a range	SUMPRODUCT LEN SUBSTITUTE UPPER
Count total characters in a cell	LEN
Count total characters in a range	SUMPRODUCT LEN
Count total words in a cell	ISBLANK LEN SUBSTITUTE TRIM
Count total words in a range	SUMPRODUCT LEN SUBSTITUTE TRIM
Double quotes inside a formula	CHAR
Extract nth word from text string	LEN MID REPT SUBSTITUTE TRIM
Get first word	ISERROR FIND LEFT
Get last word	REPT RIGHT SUBSTITUTE TRIM
Position of 2nd 3rd etc instance of character	FIND SUBSTITUTE
Remove last n characters from text	LEFT LEN VALUE
Remove leading and trailing spaces from text	CLEAN SUBSTITUTE TRIM
Remove text by matching	SUBSTITUTE
Remove text by position	REPLACE
Remove text by variable position	FIND REPLACE

Finalcial

Formula	Related Functions
Calculate compound interest	FV
Calculate simple interest

Workbook

Formula	Related Functions
Get full workbook name and path	CELL
Get sheet name only	CELL FIND MID
Get workbook name and path without sheet	CELL FIND LEFT SUBSTITUTE
Get workbook name only	CELL FIND MID
Get workbook path only	CELL FIND LEFT

Internet

Formula	Related Functions
Get domain from email address	FIND LEN RIGHT
Get domain name from URL	FIND LEFT
Get name from email address	FIND LEFT
Get top level domain (TLD)	FIND LEN RIGHT SUBSTITUTE

Names

Formula	Related Functions
Get first name from Last, First	FIND LEN RIGHT
Get first name from name	FIND LEFT
Get last name from Last, First	LEFT LEN
Get last name from name	FIND LEN RIGHT SUBSTITUTE
Get middle name from full name	LEN MID TRIM
Join first and last name	CONCATENATE
Put names into proper case	PROPER TRIM

Percentage

Filter:

Formula	Related Functions
Calculate percent variance
Decrease by percentage
Get amount with percentage
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

Range

Filter:

Formula	Related Functions
Address of first cell in range	CELL ADDRESS COLUMN ROW
Address of last cell in range	ADDRESS COLUMN COLUMNS ROW ROWS
All cells in range are blank	SUMPRODUCT
First column number in range	ROWS MIN
First row number in range	ROW MIN
Get relative column numbers in range	COLUMN
Get relative row numbers in range	ROW
Last column number in range	COLUMN COLUMNS MIN
Last row number in range	ROW ROWS MIN
Range contains a value not in another range	ISNA MATCH SUMPRODUCT
Range contains numbers	ISNUMBER SUMPRODUCT
Total cells in a range	COLUMNS ROWS
Total columns in range	COLUMNS
Total rows in range	ROWS

Miscellaneous

Filter:

Formula	Related Functions
Build hyperlink with VLOOKUP	HYPERLINK VLOOKUP
Calculate a ratio from two numbers	GCD
Change negative numbers to positive	ABS
Extract unique items from a list	INDEX MATCH COUNTIF
Flag first duplicate in a list	COUNTIF
Flip table rows to columns	TRANSPOSE
Get value of last non-empty cell	LOOKUP
Increment a calculation with ROW or COLUMN	COLUMN ROW
List contains duplicates	SUMPRODUCT COUNTIF
Longest winning streak	IF FREQUENCY
Mixed reference for multiplication table
Pad a number with zeros	REPT TEXT
Random date between two dates	WORKDAY RANDBETWEEN
Random number between two numbers	RANDBETWEEN
Random text values	CHOOSE RANDBETWEEN
Randomly assign data to groups	CHOOSE RANDBETWEEN
Range contains one of many substrings	ISNUMBER SUMPRODUCT SEARCH
Range contains one of many values	ISNUMBER SUMPRODUCT SEARCH
Range contains specific text	COUNTIF
Range contains specific value	MATCH COUNTIF
Reverse a list or range	INDEX ROW COUNTA
Transpose table without zeros	IF TRANSPOSE

excel

Chủ Nhật, 12 tháng 6, 2016

EXCEL FORMULAS

Sum

Average

Min and Max

Conditional Formatting

Lookup

If

Round

Date And Time

Date Series

Text

Finalcial

Workbook

Internet

Names

Percentage

Range

Miscellaneous

Không có nhận xét nào:

Đăng nhận xét