The idea is to enter a new weight each day, and calculate the difference from the previous day. Extra credit Looking for more of a challenge? Here’s the same result, with a custom number format applied. What’s the number format? I swiped this from Mike Alexander on his now-defunct Bacon Bits blog. There are really good proposed solutions below, including a very compact and elegant solution by Panagiotis Stathopoulos.
The Problem We have a list of alphanumeric codes. Each code consists of a single letter (A, B, C, etc.) followed by a 3-digit number. These codes should appear in alphabetical order, but sometimes they are out of sequence. We want to flag out-of-sequence codes. is out of sequence? Download the worksheet below and take the challenge! Hint -This videoshows some tips for how to solve a problem like this....
One problem that comes up a lot in Excel is counting or summing based on multiple OR conditions. This can be surprisingly tricky, so naturally it makes a good challenge! The challenge The data below represents orders, one order per row. There are three separate challenges. The result is a single column array each time MATCH is used. SUMPRODUCT then sums the array and returns the result.
you could download the worksheet below. So many great formulas! Thanks to everyone who took the time to submit an answer. Below are my rambling thoughts on the problem and some of the solutions below. Note: I never clarified how month boundaries should be handled. I was just following another worksheet as an example. In many cases, IF is combined with OR or AND to keep formulas compact. See formulas by , Arun, and David below....
In a world where everyday is Saturday or Sunday…. This formula works fine, but it’s overly complicated. What I love about this example is how an initially complicated formula “collapses” into a simple solution. Excel is full of hidden gems like this that can drastically simplify your work. More formula info
The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not susceptible to changes, like a normal cell reference. It will continue to evaluate to the same location regardless of changes to the worksheet. Sheet names Formulas with sheet names must follow standard rules. This happens because the reference is entered as text and therefore is not automatically updated like a normal reference....
On this sheet, Table1 contains employee data. Let’s run through some examples. To start off, you’re free to use theROWS functionto count table rows. This is the count of data rows only. you could see we have 19 people in the list. you’ve got the option to use theCOLUMNS functionto count columns. To get a total count of table cells, you could use aformulawith both functions. You could use a function like COUNTBLANK to count empty cells....
The FORMULATEXT can be used to extract a formula as text from a cell reference. Once text is extracted with FORMULA text, it can be handled as text in another formula. FORMULATEXT takes just one argument,reference, which is normally a cell reference like A1. If you use FORMULATEXT on a cell that doesn’t contain a formula, it returns #N/A. FORMULATEXT will handle formulas up to 8192 characters. InExcel 365, the FORMULATEXT function will return more than one result when given arangethat contains formulas....
FREQUENCY returns the distribution as avertical arrayof numbers that represent a “count per bin”. The FREQUENCY function always returns an array withone more itemthan bins in thebins_array. This is by design, to catch any values greater than the largest value in thebins_array. The general pattern for FREQUENCY is: wheredata_arrayandbins_arrayare typicallyrangeson the worksheet. Note: the curly braces added by Excel automatically when entered withcontrol + shift + enter. Horizontal results The FREQUENCY function always returns a verticalarrayof results....
Excel supports both full column, andfull rowreferences. A full column reference is a reference that refers to anentire column. Like other ranges, a full column reference can include multiple columns. Any value, no matter how far down the worksheet, will be included. Charles Williams, an Excel performance expert, has published sometest results here. If your goal is to create a dynamic range, anExcel Tableis a good choice. If you need more flexibility, you could create adynamic named range....
Excel supports bothfull column, and full row references. A full row reference is a reference that refers to anentire row. Like other ranges, a full row reference can include multiple rows. Any value, no matter how far down the worksheet, will be included.
An Excel function is a pre-built formula with a specific purpose. Most Excel functions require specific inputs, calledfunction arguments. Formulas vs Functions When you use a function in Excel, you are using a formula. So, by definition, all functions are formulas. However, it’s possible for you to create formulas in Excel without any functions at all. For example, this formula returns the sum of A1 and A2 without any functions.
A function argument is a specific input to a function. In Excel, optional arguments are denoted with square brackets.
