Posts

Working with excel tables can significantly improve your spreadsheet solutions. A total row is one very beneficial table feature that will help you see quick summaries of the columns in your table such as counts, sums, and averages. The totals in the total row are generated with SUBTOTAL formulas and will automatically update when any information in the table is added or updated. When you create a new Excel table the total row is disabled and you will need to enable this useful feature for each table....

A common technique used in Excel is to format zero values as blanks. This can help improve the readability and comprehensibility of the spreadsheet. It’s difficult to quickly identify which cells contain zero values when you have a lot of data. When you signify zero values as blanks, it gives you a visual indication and makes it simpler to spot patterns in the data. A zero value in your data might also mean that no data exists....

This formula uses the named ranges “amounts” (D5:D104) and “dates” (C5:C104). But we don’t want to hard-code dates, we want Excel to generate these dates for us. Normally, this is a pain, because if you add month names as text (i.e. “January”, “February”, “March”, etc.) in column F you have to go to extra trouble to create dates you can use for criteria. However, in this case, we use a simple trick to make things easier: In column F, instead of typing month names, we add actual dates for the first of each month (1/1/2016, 2/1/2016, 3/1/2016, etc....

where “data” is the named range B5:B13. Note: this is an array formula, and must be entered with control + shift + enter. The ISNUMBER function returns TRUE for numeric values, and FALSE for other values (including blanks), and the ROW function returns row numbers, so the result of this operation is an array row numbers that correspond to numeric entries: This array goes into the LARGE function with the array constant {1,2,3} for k....

which guarantees the result will never exceed 100%. Although MIN is frequently used to find the minimum value in a larger set of numbers, it also works fine with just two values. Inside MIN, the first value is hardcoded as 1, the equivalent of 100% when formatted as a percentage. The second value is the result of B5 is divided by C5. The MIN function simply returns the smaller of the two values:...

Context Let’s say you have a list of text strings in the range B5:B8, and you want to find out if these cells contain all of the words in another range, E5:E7. You could build a formula that uses nested IF statements to check for each item, but that won’t scale well if you have a lot of things to look for. Each time you add an word to look for you’ll need to add another nested IF and adjust parentheses....

where data is the named range B5:B14, and exclude is the named range D5:D7. This is an array formula and must be entered with control + shift + enter, except in Excel 365. Note: This formula is complex. See below for a more streamlined version of the formula based on the REDUCE function. Note: This formula is complicated by the “contains” requirement. If you just need a formula to count cells that do not equal many things, you can use a more straightforward formula based on the MATCH function....

In the example shown, the formula in H6 is: Which returns 1 since the value “Osborne” does not appear in B6:B11. For range, we give COUNTIF the named range list1 (B6:B11), and for criteria, we provide the named range list2 (F6:F8). Because we give COUNTIF more than one criteria, we get more than one result in a result array that looks like this: {2;1;0} We want to count only values that are missing, which by definition have a count of zero, so we convert these values to TRUE and FALSE with the “=0” statement, which yields: {FALSE;FALSE;TRUE} Then we force the TRUE FALSE values to 1s and 0s with the double-negative operator (–), which produces: {0;0;1} Finally, we use SUMPRODUCT to add up the items in the array and return a total count of missing values....

where date is the named range B5:B16. Note: In older versions of Excel, you can count unique items with the COUNTIF function, or the FREQUENCY function, as explained below. In the example shown, each row in the table represents a stock trade. On some dates, more than one trade is performed. The goal is to count trading days – the number of unique dates on which some kind of trade occurred....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

In the example shown, the formula in C6, copied down, is: where named ranges are “cost” = G4, “salvage” = G5, and “life” = G6. Fixed-declining balance calculation To get a rate to use to calculate depreciation based on fixed-declining balance, Excel uses the following formula: To calculate depreciation in each year, Excel uses a formula like this: However, depreciation for the first and last year is calculated differently to account for the month argument....

The LOG function takes two arguments, number and base. The value provided for number should be a positive real number. The base argument represents the base of the logarithm. Base is optional and defaults to 10 if not provided. Examples The logarithm of 16 with base 2 (the power to which 2 must be raised to equal 16) is 4: The logarithm of 100 with base 10 (the power to which 10 must be raised to equal 100) is 2: Because the base argument defaults to 10, the formulas below are equivalent: The LOG10 function also returns the base 10 logarithm of a number:...

QUARTILE.INC vs QUARTILE.EXC Percentiles can be defined as “greater than or equal to” (inclusive) or “greater than” (exclusive). The QUARTILE.INC function is inclusive and has “greater than or equal to” behavior. QUARTILE.EXC is exclusive, and has “greater than” behavior. The screen below shows how QUARTILE.INC and QUARTILE.EXC return different results for the same data. Note QUARTILE.EXC cannot be used to get the minimum or maximum value like QUARTILE.INC. Note: Starting with Excel 2010, the QUARTILE....

Click any function above for details and many examples. What’s different? Although these functions are widely used, they operate differently in three key ways you may not have noticed In the article below, I explain these differences in some detail. Once you understand how these functions work, you can more easily get them to do what you want. In addition, you’ll have a better idea about when you should explore alternatives....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

A1 –> $A$1 –> A$1– > $A1– > A1 This is much faster and easier than typing the $ character manually. To convert an existing formula, enter cell edit mode, place the cursor in or next to the reference you’d like to convert, then use the shortcut. Note: In earlier versions of Excel on the Mac, this shortcut is exclusively Command + T. In Mac Excel 365, fn + F4 also works, inline with the Windows version of Excel....

Example In the example shown, there are 11 numeric values in two groups, A and B. The count of values in each group are the inverse of each other. There are four 1’s in group A, four 5’s in group B, etc. The formula in cell F12 returns a positive skew: The formula in J12 returns a negative skew: Excel also contains the SKEW function, which measures sample skewness. The difference in calculation is related to an adjustment (n-1) made when data represents a sample versus the entire population....

The SQRT function takes one argument, number, which must be a numeric value. If number is not numeric, SQRT returns a #VALUE! error. If number is negative, SQRT returns a #NUM! error. Examples Negative numbers The SQRT function will return a #NUM! error when number is negative: To get the square root of a negative number (as if the number was positive), wrap the number in the ABS function like this:...

This is an array formula and must be entered with Control + Shift + Enter. After you enter the formula in the first cell, drag it down and across to fill in the other cells. The gist of this formula is this: we are using the SMALL function to generate a row number corresponding to an “nth match”. Once we have the row number, we simply pass it into the INDEX function function, which returns the value at that row....