Search results
Apr 14, 2021 · 101 EXCEL FUNCTIONS 11 Logical Functions 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 ...
- 5MB
- 35
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
particular Excel functions. There are also a number of sample worksheets which are simple models of common applications, such as Timesheet and Date Calculations.
Jul 2, 2024 · Excel Data for Practice Free Download Excel Practice Exercises PDF with Answers Explore these categories you will find out more data entry practice here: Data Entry Practice Test & Quiz Interview Questions with Excel Topics MCQ Questions on MS Excel. To develop your skill from beginner to advanced level you can explore our Learn Excel page. Regards
- 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.
CHAPTER 17 Analyzing data with tables 379 CHAPTER 18 Analyzing data with PivotTables 399 CHAPTER 19 Using Excel’s business modeling tools 417 CHAPTER 20 Solving complex problems with Solver 439 Index 461 A01_McFerdies_FM_pi-xxviii.indd 5 21/01/22 3:34 PM
vii. Contents. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
