Posts

Explanation The CHOOSE function provides the framework for this formula. Choose takes a single numeric value as its first argument (index_number), and uses this number to select and return one of the values provides as subsequent arguments, based on their numeric index. In this case, we are using four values: Red, Blue, Green, and Pink, so we need to give CHOOSE a number between 1 and 4. To generate this number, we use RANDBETWEEN, a function that returns a random integer between a bottom and top value....

Explanation The FORMULATEXT is fully automatic. When given the reference of a cell that contains a formula, it will return the entire formula as text. In the example as shown, the formula: =FORMULATEXT(C5) returns the text “=IF(B5>=70,“Pass”,“Fail”)”. Dealing with errors The FORMULATEXT function will return the #N/A error when a cell does not contain a formula. To trap this error and display nothing when a cell does not contain a formula, you can use the IFERROR function like this:...

Explanation The core of this formula is SUMIF, which is used to lookup the correct values for F and H. Using SUMIF to lookup values is a more advanced technique that works well when the values are numeric , and there are no duplicates in the “lookup table”. The trick in this case is that the criteria for SUMIF is not a single value, but rather an array of values in the range C5:G5:...

If you’ve ever wondered whether learning Excel formulas is worth your time, this list is for you! Formulas are the heart of spreadsheets, so your skill with them can help you in many ways. Read below for 10 reasons you should take time to increase your skill with formulas. Formulas are important in many jobs. In a recent survey we conducted about formulas, over 90% of respondents said that Excel formulas were “important”, “very important”, or “vital” to their job....

Explanation In this example, the goal is to perform a case-sensitive lookup on the color in the “Color” column. In other words, a lookup value of “RED” must return a different result from a lookup value of “Red”. By default, Excel is not case-sensitive and this applies to standard lookup formulas like VLOOKUP , XLOOKUP , and INDEX and MATCH . These formulas will simply return the first match, ignoring case....

Explanation In this example, the goal is to perform a horizontal lookup to determine the correct quantity-based discount. The range G6:L7 contains quantity-based discounts. Column B contains random quantities used when testing the formula. This problem can be easily solved with the XLOOKUP function or the HLOOKUP function. Both methods are explained below. XLOOKUP formula One of XLOOKUP’s nice benefits is its ability to work with vertical or horizontal data. The syntax used for horizontal lookups is the same as for vertical lookups....

Transcript Resources to learn Excel shortcuts 200 Excel shortcuts for Win and Mac - online & updated frequently Laminated quick reference cards - handy reference, no wifi required Excel shortcuts on the Mac - important if you use Excel on a Mac Shortcut video training - learn 200 Excel shortcuts with bite-sized videos Transcript In this video, we’re going to take a quick tour of over 30 popular Excel shortcuts ....

Explanation The ADDRESS function creates a reference based on a given row and column number. In this case, we want to get the first row and the first column used by the named range data (B5:D14). To get the first row used, we use the ROW function together with the MIN function like this: MIN(ROW(data)) Because data contains more than one row, ROW returns an array of row numbers: {5;6;7;8;9;10;11;12;13;14} This array goes directly to the MIN function, which returns the smallest number:...

Purpose Return value Syntax =AVEDEV(number1,[number2],...) number1 - First value or reference. number2 - [optional] Second value or reference. Using the AVEDEV function The Excel AVEDEV function calculates the average of absolute deviations from the mean in a given set of data. Variance and standard deviation functions deal with negative deviations by squaring deviations before they are averaged. AVEDEV handles negative values by working only with absolute values. This average is called the average absolute deviation....

Explanation This formula simply uses COUNTIF with a criteria of “x” (not quotation marks) to count x’s in each row, where “x” represents “present” and an empty cell represents “absent”: =COUNTIF(C5:L5,"x") // count present The count absent days, the worksheet uses COUNTIF again, configured to count empty cells: =COUNTIF(C5:L5,"") // count absent Explanation In this example, the IFERROR function is used to trap and suppress the #DIV/0! error that occurs when there is no value for Orders (column D)....

Purpose Return value Syntax =BIN2OCT(number,[places]) number - The binary number you want to convert to octal. places - [optional] If omitted uses the least number of characters required to represent the number, otherwise pads the result with zeros up to the specified number of places. Using the BIN2OCT function The input number must contain only zeros and ones and be less than 10 characters long, otherwise the function returns the #NUM!...

Broadcasting is an array calculation behavior in Excel formulas. It refers to a step during formula calculation where a smaller array is “filled” with duplicate values to create an array with the same dimensions as a larger array in the formula. In essence, Excel resizes arrays to be the same size. In the example show, cells F5:F7 contain this multi-cell array formula: =B5:B7*D5 The first range results in an array with 3 items....

Explanation For this example, we want to calculate cumulative interest over the full term of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up CUMIPMT like this: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: =C6/12 nper - the total number of payment periods for the loan, 60, from cell C8....

Explanation The MEDIAN function has no built-in way to apply criteria. Given a range, it will return the MEDIAN (middle) number in that range. To apply criteria, we use the IF function inside MEDIAN to “filter” values. In this example, the IF function filters by group like this: IF(group=E5,data) This expression compares each value in the named range “group” against the value in E5 (“A”). Because the criteria is applied to an array with multiple values, the result is an array of TRUE FALSE values like this:...

Explanation The goal in this example is to calculate a cost of living (COLA) adjustment for each of the eleven years shown, starting on the second year. The actual adjustment percentage is entered in cell F6, which is the named range cola . Each year, the adjustment should be applied to the previous base amount in column C, starting with the base amount in cell C5. The formula in cell C6, copied down is:...

Explanation In this example, the goal is to count unique values that meet one or more specific conditions. In the example shown, the formula used in cell H7 is: =SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0)) At the core, this formula uses the FILTER function to apply criteria, and the UNIQUE function to extract the unique values that remain. Working from the inside out, the FILTER function is used to apply criteria and extract only names that are associated with the “Omega” project:...

Explanation Data validation rules are triggered when a user adds or changes a cell value. This custom validation formula uses the WEEKDAY function to get a numeric value, 1-7, corresponding to a week beginning Monday (1) and ending Sunday (7). To get a number for a Monday-based week, the return_type argument for WEEKDAY is provided as 2. The WEEKDAY result is then compared to 6. Any value less than 6 is a weekday, so the expression returns TRUE and validation succeeds....

Explanation Data validation rules are triggered when a user adds or changes a cell value. The AND function takes multiple arguments (logicals) and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because we want to allow only dates in the month of June 2016, we give AND with two logicals. The first logical tests that input to C5 is greater than or equal to June 1, 2016:...

This chart is an example of a column chart that uses a “floating bar” technique to plot daylight hours on the chart in way that makes the bars look like the are floating above the horizontal axis. The trick in this case is to create three helper columns that do not exist in the original data: daylight, evening, and hrs. The video here walks through this process. Original data Data with helper columns One of the charts you’ll see around is a so called “floating column chart”, where columns rise up off the horizontal axis to depict some sort of value range....

Purpose Return value Syntax =DAYS360(start_date,end_date,[method]) start_date - The start date. end_date - The end date. method - [optional] Day count method. FALSE (default) = US method, TRUE = European method. Using the DAYS360 function The DAYS360 function returns the number of days between two dates, based on a year where all months have 30 days. Both dates must be valid Excel dates or text values that can be parsed as dates....