So the beauty (yes I said beauty) of Excel is that there is more than one way to do everything. And no matter how long you have been getting your spreadsheet on, there is often a more simple and efficient way of doing something.

The “function” of this newsletter is IFERROR, and I cannot stop talking about it 🙂 Thank you Sam Welt!!

You know the drill… you are putting together a nice little summary spreadsheet with some simple calculations, but errors such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL keep showing up in some of your cells. This can be due to various reasons, but one thing is for sure, this can seriously crush what would have been an otherwise zen spreadsheet! Well guess what, IFERROR is here to give your spreadsheet a little botox.

The function is:

=IFERROR(original formula, desired result instead of an error message)

Let’s do a quick example where we are viewing actual employee sales versus their target sales.

– In Column E we are dividing the Deviation from Target from the Target.

The formula in cell E4 is =D4/B4

The formula is copied down for cells E5:E12

– Steve and Will never handed in their targeted numbers (slackers), but they are going to get you their Targets in the next day or so. For the time being, their Targets are blank, and since you cannot divide by 0, their % Deviation is an error: #DIV/0!

– Your boss wants to see your summary today of course… so you need to get rid of the Error Messages in cells E11 and E12, but you still want the formulas in the summary for later. This is the perfect time to use IFERROR!!

– Place the IFERROR function in the first cell E4 even though it does not yield an error.

– You will nest your current formula (D4/B4) inside of the IFERROR formula and enter what you would like to see instead of the error notification.

– So, if you want your errors to show the number 0, instead of #DIV/0!, your new formula will read as follows: =IFERROR(D4/B4,0)
– If you want your errors to show up as “TBD”, the new formula is, =IFERROR(D4/B4,”TBD”)

– If you want your errors to show up as a blank cell, the new formula is, =IFERROR(D4/B4,””)

In this case, let’s go with “TBD”, so your boss knows the numbers are coming… but it is not pending you! Thus you will place the formula =IFERROR(D4/B4,”TBD”) and copy it down the rest of column E.

You catch my drift… It is so easy!!

Just to clarify the above examples, if you want your error notifications to be replaced with text (such as “TBD”) or a blank cell, you will need to put quote marks around the desired result.

No more conditional formatting or nested IF/ISERROR, or manually deleting cells to get rid of errors. IFERROR is making it rain… Unfortunately just for PC 2007 and MAC 2008 spreadsheeters.

So go IFERROR your little bottom off so you don’t forget!

Small Biz Spotlight

It all started when my parents said they were sending my husband a Toffee Taboo Tree to work. Whatever that means right? They know that Jesse loves presents and since we do not have a doorman everything gets shipped to him @ work. The plan was for Jesse to get the tree, bring it home, and we could then break off this glorious toffee at our leisure.

Once the Toffee Taboo Tree arrived at his office, it was “accidentally” unwrapped and put out for public consumption. Like a pack of wild wolves closing in on the kill, my husband’s work buddies demolished this toffee tree as though it would be their last meal… Legend has it, in 25 minutes flat! Now, for those of you who are unfamiliar with office life, this is big deal, considering all that food crap that offices have sitting around during the holidays. I even received the below shameful email from one of his colleagues who should remain nameless (sorry Jack Green), “OK, OK…I have to admit it. I hoarded the last few pieces of the chocolate tree and hid them in various spots around the office so I have something to look forward to coming back to. Best Christmas snack ever!”

Needless to say I did not get any toffee from that tree… but needless to say, I was intrigued. Did my parents get it right this time around??

After they shipped a couple of more boxes of Toffee Taboo at work, Jesse was finally able to avoid the ravenous co-workers and smuggle some home for me. All I can say is… LIGHTS OUT! I am obsessed with Toffee Taboo.

While I have always had self-proclaimed impeccable taste is desserts, it was not until now that I found the end all treat… for not only those who love their chocolat but for those who tend to stay on the savory said as well… as the taboo contains the finest Belgian chocolate finished with a cascade of toffee-encrusted almonds and cashews and a hint of white chocolate drizzled over the top.

Enter Toffee Taboo, from Bob Sendall’s All in Good Taste Productions, which hails from the Mexican War Streets in the ‘burgh (that’s Pittsburgh obviously).

Being the fabulous daughter that I am, I caught my father’s not so subtle hints that there is in fact a Toffee Taboo cake (with a gluten free option) that is available for purchase also. Within 5 days of knowing about the cake, my father mentioned, emailed and texted me about this masterpiece a combined total of 27 times. Thus, Mark indeed had his Toffee Taboo birthday cake and he certainly ate it too! He deemed it the best bday gift ever… gold star for the Excel Rain Diva!

Have no fear if you do not live near the blessed Three Rivers, Toffee Taboo can be purchased online AND has an ever-growing list of retailers who carry these delicious treats (Chelsea Market Baskets, Barneys, Shadyside Market, …).

So order yourself some taboo to see what I mean. Serve it alone, sprinkle it on a cake, top it on a sundae or put it in special hiding places so no one can find it

And you don’t have to share.

Great find mom and dad!!

If Excel is bumming you out, just Submit a Request and we will show that spreadsheet of yours who’s boss.

Also, check out Excel Rain Man’s latest press:
While Steeler Nation is still recovering from Super Bowl XLV, the Excel Rain Man team got a little love from Amex OPEN Forum for the Super Bowl Matrix.

Excel Rain Man is all about the iMentor program (more on that later), and I am looking forward to being matched with my mentee. In the meantime, Excel Rain Man donated our Video Tutorials to the iMentor staff.

Don’t forget to become a fan of Excel Rain Man on Facebook and follow us on Twitter.

Until next time,
Jen Portland

The Excel Rain Diva 🙂