All about formulas and functions
The IF Function is used to evaluate a logical test, then if TRUE do something or if FALSE do something else. A familiar construct. The IF Function has three arguments: IF(logical_test, [value_if_true], [value_if_false]) Argument 1 The first argument, logical_test, is required. It’s any value or expression that can be evaluated to TRUE or FALSE. This …
A Single-cell Array formula is powerful, yet can be hard to understand. The Evaluate Formula dialog box helps by revealing array values Excel holds in internal memory. In the spreadsheet you see pictured, a single-cell array formula for the Cumulative Plan is located in cell B2. It summarizes the historical and current day’s Plan data. …
Arrays are difficult to understand because Excel holds them internally and it’s hard to conceptualize how operations are performed on the data. I had a vexing problem that was solved by using a single-cell array formula and wanted to share it with you. However, because of the subject nature of Arrays, I’m going to show …
Zero has a special meaning in the Excel date system and can be used in the DATE Function to return the last day of the month. The simplest way to show this is to use a formula to find the last day of February in the coming years. How to Find in Excel the Last Day …
Each week I download the over-under calories report from Lose It! and dump the data into a spreadsheet. I just created my first Sparkline graphic to show the last 7 days of this data. For this example I’ll use an OFFSET Function inside a named range, which was created in my last couple of posts. …
In the last post I used the OFFSET Function inside the AVERAGE Function to return the last 7 days of a range. Here I’ll create a Named Range using a formula with the OFFSET Function. I use a worksheet to inform me of the Last 7 Day Average for Over-Under Calories data, as shown below. …
In this example I’m going to use the OFFSET Function to return a range reference to the last 7 cells in a column. Of course, it will be inside the AVERAGE Function so I can see an average for the last week. Sound complicated? Not really, it’s just an extension of something I did on …
The OFFSET Function is a difficult one to learn. I’ve decided to become the OFFSET function and tell a simple story of how I give you a value for the last cell in a range by using an example. The example data is a spreadsheet I use to log my over-under calories to goal each …
Once upon a time I was working with an unfamiliar Excel spreadsheet and came across a formula that used a Name, which I figured for a constant value and thought it hidden somewhere in the worksheet, yet couldn’t find it anywhere. It never dawned on me that a value doesn’t have to reside in a …