Ad
related to: excel formulas pdfudemy.com has been visited by 1M+ users in the past month
Learn Excel formula and function. Find the perfect course for you! Join millions of learners from around the world already learning on Udemy.
Search results
People also ask
How do you create formulas in Excel?
What are some tips for using formulas in Excel?
What do Excel formulas do?
Apr 14, 2021 · 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.
- 5MB
- 35
102+ Useful Excel Formulas Cheat Sheet PDF + Free Download Excel Sheet. Version: 1.0. Publish date: 22 Sep 2021. Changelog: n/a. This PDF is made and distributed by ExcelDemy and intended only for personal use, not for commercial use.
- 2MB
- 45
Calculate complex formulas one term at a time. In the formula bar, select the expression you want to calculate and then select F9. Excel converts the expression into its value. Make sure that you select the Esc key when you’re done to avoid entering the formula with just the calcu-lated values.
- 1MB
- 77
- A. Is Functions
- B. Conditional Functions
- C. Mathematical Functions
- D. Find & Search Functions
- E. Lookup Functions
- F. Reference Functions
- G. Date & Time Functions
- H. Miscellaneous Functions
- I. Rank Functions
- J. Logical Functions
- GeneratedCaptionsTabForHeroSec
1. ISBLANK
=ISBLANK(value) If a cell is blank, it returns TRUE. If a cell is not blank, it returns FALSE.
2. ISERR
=ISERR(value) Checks whether a value is an error (#VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) excluding #N/A, and returns TRUE or FALSE
3. ISERROR
=ISERROR(value) Checks whether a value is an error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!), and returns TRUE or FALSE
13. AVERAGEIF
=AVERAGEIF(range, criteria, [average_range]) Finds average (arithmetic mean) for the cells specified by a given condition or criteria
14. SUMIF
=SUMIF(range, criteria, [sum_range]) Adds the cells specified by a given condition or criteria
15. COUNTIF
=COUNTIF(range, criteria) Counts the number of cells within a range that meet the given condition
22. SUM
=SUM(number1, [number2], [number3], [number4], …) Adds all the numbers in a range of cells
23. AVERAGE
=AVERAGE(number1, [number2], [number3], [number4], …) Returns the average (arithmetic means) of its arguments, which can be numbers or names, arrays, or references that contain numbers
24. AVERAGEA
=AVERAGEA(value1, [value2], [value3], [value4], …) Returns the average (arithmetic means) of its arguments, evaluating text and FALSE in arguments as 0; TRUE evaluates as 1. Arguments can be numbers, names, arrays, or references.
43. FIND
=FIND(find_text, within_text, [start_num]) Returns the starting position of one text string within another text string. FIND is case-sensitive
44. SEARCH
=SEARCH(find_text, within_text, [start_num]) Returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive)
45. SUBSTITUTE
=SUBSTITUTE(text, old_text, new_text, [instance_num]) Replaces existing text with new text in a text string
47. MATCH
=MATCH(lookup_value, lookup_array, [match_type]) Returns the relative position of an item in an array that matches a specified value in a specified order
48. LOOKUP
=LOOKUP(lookup_value, lookup_vector, [result_vector]) Looks up a value either from a one-row or one-column range or from an array. Provided for backward compatibility
49. HLOOKUP
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Looks for a value in the top row of a table or array of values and return the value in the same column from a row you specify
51. ADDRESS
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Creates a cell reference as text, given specified row and column numbers
52. CHOOSE
=CHOOSE(index_num, value1, [value2], [value3], …) Chooses a value or action to perform from a list of values, based on an index number
53. INDEX
Array Form: =INDEX(array, row_num, [column_num]) Return the value of a specified cell or array of cells Reference Form: =INDEX(reference, row_num, [column_num], [area_num]) Returns a reference to specified cells
56. DATE
=DATE(year, month, day) Returns the number that represents the date in Microsoft Excel date-time code
57. DATEVALUE
=DATEVALUE(date_text) Converts a date in the form of text to a number that represents the date in the Microsoft Excel date-time code
58. TIME
=TIME(hour, minute, second) Converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format
67. AREAS
=AREAS(reference) Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell
68. CHAR
=CHAR(number) Returns the character specified by the code number from the character set for your computer
69. CODE
=CODE(text) Returns a numeric code for the first character in a text string, in the character set used by your computer
84. RANK
=RANK(number, ref, [order]) This function is available for compatibility with Excel 2007 and others. Returns the rank of a number in a list of numbers: its size relative to other values in the list
85. RANK.AVG
=RANK.AVG(number, ref, [order]) Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned
86. RANK.EQ
=RANK.EQ(number, ref, [order]) Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned
87. AND
=AND(logical1, [logical2], [logical3], [logical4], …) Checks whether all arguments are TRUE, and returns TRUE when all arguments are TRUE
88. NOT
=NOT(logical) Changes FALSE to TRUE, or TRUE to FALSE
89. OR
=OR(logical1, [logical2], [logical3], [logical4], …) Checks whether any of the arguments is TRUE, and returns TRUE or FALSE. Returns FALSE only when all arguments are FALSE
Download a free PDF with 102+ Excel formulas and examples for personal use. Learn how to use IS, IF, SUM, AVERAGE, VLOOKUP, and more functions with syntax and tips.
In Microsoft Excel Formulas and Functions (Office 2021 and Microsoft 365), I demystify the building of worksheet formulas and present the most useful of Excel’s many functions in an accessible, jargon-free way.
Over 500 working Excel formulas with detailed explanations, videos, and related links. Includes key functions like VLOOKUP, XLOOKUP, INDEX & MATCH, FILTER, RANK, ROUND, AVERAGE, COUNTIFS, SUMIFS, UNIQUE, SORT, TEXTSPLIT, and more.
As formulas are copied either the column reference increases or the row number depending on the direction of the copy. If copying to the right through the spreadsheet, the column reference will increase; if copying down through the spreadsheet, the row references will increase.
