November 2010

Switch Between Relative and Absolute References in Excel for Mac

November 29, 2010 Beginner

I’m a longtime user of Excel for Windows, but recently a newbie with Excel 2008 for Mac where I found that out the Windows shortcut F4 doesn’t work to switch between relative and absolute references when editing a formula. Thankfully Excel 2011 has a Switch Reference toolbar icon on the Formulas Ribbon, as shown below. [...]

Get the full story …

My Spreadsheet Challenge Picture

November 26, 2010 General Update

Here’s the picture I entered for the Spreadsheet Challenge. I’ll point out the obvious and not so obvious stuff in this picture. Me Two monitors showing Excel 2007, Excel 2003, and Excel 2010 spreadsheets, and PDF versions of Excel 2007 Formulas, Excel 2003 Power Programming with VBA, and Excel 2010 Bible A MacBook Pro showing [...]

Get the full story …

Convert Pace to MPH and Back Again in Excel

November 24, 2010 Formulas
Thumbnail image for Convert Pace to MPH and Back Again in Excel

Excel is a great tool for figuring stuff out, like for conversions that aren’t easy to do in your head. Here I’m converting Pace to MPH and then reversing the process, converting MPH to Pace, to create a conversion chart. My Conversion Problem I track my Average Pace when out walking for exercise by using [...]

Get the full story …

Feed Problems

November 23, 2010 General Update

I normally like to have the entire post show up in the Feed or email you get, but here recently they’ve been truncated and I’m not sure why. This, of course, requires you to click through to my site to read the rest of the article. I subscribe to many, many feeds and abhor having [...]

Get the full story …

Convert Seconds with the TIME Function in Excel

November 22, 2010 Beginner

I often download an iTunes playlist just to see the time increments for each song, but iTunes only gives you the number of seconds for each song duration. In a previous post I spent a great deal of time showing how to use a combination of various formulas to convert these seconds, when it’s really [...]

Get the full story …

Grade Formulas in Excel

November 20, 2010 Formulas
Thumbnail image for Grade Formulas in Excel

There are several ways to turn student scores into letter grades. I recently came across a nested IF formula that did the trick, but it seemed rather complicated. A better solution would be to use a VLOOKUP formula with a Grade Lookup Table, but then it occurred to me that the VLOOKUP formula could stand [...]

Get the full story …

Nested IF Functions in Excel

November 18, 2010 Formulas

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 [...]

Get the full story …

The IF Function in Excel

November 16, 2010 Beginner

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 [...]

Get the full story …

Single-Cell Array Formula in Excel

November 13, 2010 Advanced
Thumbnail image for Single-Cell Array Formula in Excel

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. [...]

Get the full story …

Multi-Cell Array Formula in Excel

November 11, 2010 Formulas

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 [...]

Related Posts Plugin for WordPress, Blogger...
Get the full story …

← Previous Entries