Search results
Apr 14, 2021 · HOUR, MINUTE, SECOND, and TIME Excel provides a set of parallel functions for times. You can use the HOUR, MINUTE, and SECOND functions to extract pieces of a time, and you can assemble a TIME from individual components with the TIME function. Did you know? Excel dates are serial numbers that start in the year 1900. Excel times are fractions
- 5MB
- 35
Excel formulas. 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)
- 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.
To calculate the number of years, months and days between two dates (more than a year apart) you can use this formula: =DATEDIF(Start_date,End_date,”y”) & “ years, “ & DATEDIF(Start_date,End_date,”ym”) & “ months, “ & DATEDIF(Start_date,End_ date,”md”) & “ days”.
- 1MB
- 124
May 17, 2022 · When you’re first learning how to use them, it’s easy to get a little overwhelmed – luckily, this Excel formulas cheat sheet is here to help you make sense of it all! Let’s take a look at the basics of formulas, some common functions you’ll use with them, and how to keep them organized no matter how complex they get.
Nov 6, 2018 · How and why should you use a particular function in your formulas? Excel Formulas and Functions For Dummies offers thorough but easy-to-read coverage of powerful Excel functions. With...
People also ask
What is time function in Excel?
How to calculate day of week from a date in Excel?
How does excel calculate a date?
How do you calculate sales in Excel?
Excel® 2013 Formulas and Functions Contents at a Glance Introduction Part I Mastering Excel Ranges and Formulas 1 Getting the Most Out of Ranges..... 5 2 Using Range Names.....37
