Excel Formulas Cheat Sheet, Lecture notes of MS Microsoft Excel skills

Download Excel Formulas Cheat Sheet and more Lecture notes MS Microsoft Excel skills in PDF only on Docsity! Excel Formulas Cheat Sheet Page 1 of 7 Basic Formulas Formula Structure Explanation AVERAGE =AVERAGE(A2:A10) Returns a mathematical average of a given cell range COUNT =COUNT(A2:A10) Returns the count of the numbers in given cell range MAX =MAX(A2:A10) Finds the largest value in a given cell range MEDIAN =MEDIAN(A2:A10) Returns the median value, or middle value, in a given cell range MIN =MIN(A2:A10) Finds the smallest value in a given cell range SUM =SUM(A2:A10) Totals numbers in a given cell range Cell range A2:A10 is used above to indicate that each formula uses a cell range as it arguments Time Formulas Formula Structure Explanation TODAY =TODAY() Volatile – takes no arguments – returns today’s date NOW =NOW() Volatile – takes no arguments – returns today’s date and time DATEDIF =DATEDIF(Start Date, End Date, Unit) Returns the number of years, months or days between two dates  Start Date – date furthest in the past  Unit could be “Y” for years, “M” for months or “D” for days  Units must be in double quotes  This formula is NOT in the function library YEAR =YEAR(Date)  Returns the year portion of date  Example =YEAR(7/16/2005) would return 2005 MONTH =MONTH(Date)  Returns the month portion of date  Example =MONTH(7/16/2005) would return 7 DAY =DAY(Date)  Returns the day portion of date  Example =DAY(7/16/2005) would return 16 Use a time formula and get an answer you didn’t expect? If you got a date and were expecting a number, remember to change the formatting from date to number. If you got a number and were expecting a date, change the formatting to date. Excel Formulas Cheat Sheet Page 2 of 7 Logical Formulas Formula Structure Explanation IF =IF(Logical Test, TRUE, FALSE)  Evaluates the statement in the logical test to determine if it is TRUE or FALSE  A Logical test compares the value of one cell to another, or a cell value to a constant value, using a comparison operator such as: o Equal  = o Less than  < o Greater than  > o Less than or equal to  <= o Greater than or equal to  >= o Not equal to  <>  TRUE – this part of the IF function will only execute when the logical test is TRUE  FALSE – this part of the IF function will only execute when the logical test is FALSE  Both TRUE and FALSE can be a word, a formula or a constant value  To return a blank cell use two double quotes  “” OR =OR(Logical Test 1, Logical Test 2, ...)  This formula can only return TRUE or FALSE  Only one test in the group must return TRUE for the formula to return TRUE AND =AND(Logical Test 1, Logical Test 2, ...)  This formula can only return TRUE or FALSE  Only one test in the group must return FALSE for the formula to return FALSE Lookup Formulas Formula Structure Explanation VLOOKUP =VLOOKUP (Lookup Value, Table Array, Col Index, Range Lookup)  Lookup Value - What the function is looking for in the table array  Table Array - The table defined as a cell range  Col Index - The column in the table that forms the return  Range lookup - False for exact match, True or blank for near match  Notes: o A vlookup can only search vertically through the left most column of a table array for near or exact matches o In most cases you will want to use absolute cell referencing when indicating a table array o If you omit the Range Lookup, Excel will assume “True” and look for a near match Excel Formulas Cheat Sheet Page 5 of 7 Formula Structure Explanation COUNTIF =COUNTIF(range, criteria)  Answers the question “How many of something (criteria) exist within specific set of cells (range)?  =COUNTIF(Where do you want to look?, What do you want to look for?)  Range – Required – Sets cells to be included in the count  Criteria – Required – Tells formula what to look for COUNTIFS =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)  criteria_range1 – Required. o The first range in which to evaluate the associated criteria.  criteria1 – Required. o The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as:  32,  ">32",  B4,  "apples", or  "32"  criteria_range2, criteria2, ... o Optional. o Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.  Important: o Each additional range must have the same number of rows and columns as the criteria_range1 argument. o The ranges do not have to be adjacent to each other Excel Formulas Cheat Sheet Page 6 of 7 Formula Structure Explanation AVERAGEIF =AVERAGEIF(range, criteria, [average_range])  Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria  Range – Required. o One or more cells to average, including numbers or names, arrays, or references that contain numbers.  Criteria – Required. o The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as:  32,  ">32",  B4,  "apples", or  "32"  Average_range – Optional. o The actual set of cells to average. o If omitted, range is used Excel Formulas Cheat Sheet Page 7 of 7 Formula Structure Explanation AVERAGEIFS = AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)  Returns the average (arithmetic mean) of all cells that meet multiple criteria  Average_range – Required o One or more cells to average, including numbers or names, arrays, or references that contain numbers.  Criteria_range1 – Required, subsequent criteria_ranges are optional (up to 127 ranges)  criteria_range2, criteria_range3,… – Optional  Criteria1 – Required, subsequent criteria are optional o Criteria can be in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as:  32,  ">32",  B4,  "apples", or  "32"  criteria2, criteria3,...  If you have a Criteria_Range, you must have a corresponding Criteria