Search results
In this PDF, I am sharing the most useful 102+ Excel formulas with syntax and examples. B. N.: I did not include here the specialized formulas for Engineering, Statistical, Web, etc. uses. Excel Formulas with Examples in an Excel Sheet (Free Download .xlsx File) I have documented all the above Excel formulas in a single Excel sheet so that you ...
- 2MB
- 45
- Section A – Is Functions
- Section B – Conditional Functions
- Section C – Mathematical Functions
- Section D – Find and Search Functions
- Section E – Lookup Functions
- Section F – Reference Functions
- Section G – Date and Time Functions
- Section H – Miscellaneous Functions
- Section I – Rank Functions
- Section J – Logical Functions
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 the 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 list or 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, from left to right (not case-sensitive).
45. SUBSTITUTE
=SUBSTITUTE(text, old_text, new_text, [instance_num]) Replaces the old text with new text in a text string, with the optional value announcing what repetition of the old text to replace.
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. Obsolete in new versions of Excel, 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 the 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 earlier. 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. 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], …) Returns FALSE only when all arguments are FALSE.
Jun 21, 2023 · Download our Formulas tutorial workbook. We've put together a Get started with Formulas workbook that you can download. If you’re new to Excel, or even if you have some experience with it, you can walk through Excel’s most common formulas in this tour.
Sometimes, you will need to enter a formula as array formula. In Excel 365/Excel 2021, all formulas are treated as Array formula, hence you need not enter any formula as Array formula. Only for older versions of Excel, you might need to enter a formula as Array formula. Array Formula is not entered by pressing ENTER after entering your formula ...
To use functions in Excel, select a cell, and type "=" followed by the function name and "(". For example, type "=IF(" and you'll see the syntax appear for the IF Function. You can also press the small fx button next to the formula bar. This will open up the Formula Wizard.
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. AND, OR and NOT The core of Excel’s logical functions are the AND function, the OR func-tion, and the NOT function.
People also ask
Is there a 'Excel formulas workbook'?
Can I download the PDF & Excel files with 102 Excel functions?
How many Excel formulas are there?
Can I upload a formula in Excel?
Can I copy a PDF from exceldemy?
How to calculate Q1 & Q2 in Excel?
Apr 5, 2017 · Chapter 9: Using Formulas with Conditional Formatting 4. Conventions in This Book 1. What the icons mean 5. About the Sample Files 2. Chapter 1: Introducing Excel Formulas 1. Creating and Editing Excel Formulas 1. Methods for entering formulas 2. Editing a formula 2. Using Formula Operators 1. Understanding the order of operator precedence 2 ...
