Intermediate level information
The simplest Nested IF Function is using one IF Function inside another. When you have more than a few choices, nesting more IF Functions can quickly get complicated and, quite frankly, there are better ways to make decisions with Excel. Having said that, I have a simple method to account for the different choices that …
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 …
I recently loaded Excel 2003 onto a new computer and when trying to open a file created with Excel 2007 I received the warning shown above. Clicking Yes took me to Microsoft Download Center, where I could download the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats. The overview section stated: Users …
There are some things you can do in the VBA Editor that won’t trigger the dreaded warning about opening a file with a macro. Hiding a worksheet so nobody can find it is one of those things. Excel worksheets have a visible property that can take on one of three values. xlSheetVisible xlSheetHidden xlSheetVeryHidden By …
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 …
In the year 2000, I began to use Microsoft Query to pull data from corporate client’s database tables, using their raw data to create PivotTable reports. Each table required a different query, each linked by using VLOOKUP formulas. And of course the data had to be restricted to Excel’s 65,536 rows. Soon I was having …
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 …