Here’s the answer to a question about THE Excel Functions To Know.

The request was for the top 10 but 10 is too many. After 3 or 4 functions, they get difficult to rank because they fall into areas of infrequent use or exclusive use. Things like INDIRECT, INDEX, RAND, and CHAR are powerful but an ordinary Excel user might go months between uses. Also, some functions are useless on their own and signal that you’re doing something layered and complex. (I’ve never used INDEX by itself but have used INDEX and MATCH many times)

The 5 Functions that I chose for the Top 5 are ones that will give you serious mileage in building Excel Formulas and allow you the read formulas that someone else built. So, here we go!

Top 5 Excel Functions (according to Oz)

1. IF
   • IF allows us to work based on conditions
     • IF a person attended at least 2 of 5 sessions
     • IF a product is over 2 lbs AND perishable
     • IF the City equals EITHER Baltimore, Philadelphia, Miami, OR, Indianapolis
   • Setting these conditions allows us to dig out the information we need. Otherwise, we might have a separate set of data for everything we want to do, and that becomes a big error-prone mess.
2. VLOOKUP
   • HOT DAMN! For many of Excel users, VLOOKUP by itself is worth the price of admission. Simple example:
     • You have 2 lists. List A is 488 rows, List B is 506 rows. Uh oh! They’re supposed to be the same. How do we find out what’s on List B that’s NOT on List A, and vice versa? Answer: VLOOKUP.
     • In 5 minutes you can have the answer. Compare that to eyeballing those 994 rows!
3. Operators allow us to create sophisticated conditions and combine formulas for precision
   • AND
   • OR
4. IFERROR
   • Showed up in Excel 2007 and allows us to keep spreadsheets uncluttered when we know that an error will be the result. It also tells Excel what to do with an unexpected error.
   • Example: If there’s an error, enter “assign to Kevin.” This will be an alert for entries that Kevin will have to research.
5. SUMIFS
   • Another 2007 newbie. Let’s say you’ve built a budget and want to create summaries out of a massive block of data. SUMIFS allows us to create multiple criteria. It’s nice to know, say, October’s overall expenses, but we might want to know something more specific. Examples:
     • October, TRAVEL expenses
     • October, Uncategorized expenses, that are amounts over $25, AND were online transaction
   • Prior to 2007, there were complicated workarounds for accessing that granular information. SUMIFS makes this so easy.

WHAT? Excel Formulas & Basics

(This may seem like a basic place to start but a lot of people who type answers into cells after they’ve done all of the math with a pencil and calculator.)
Calculating the Hours Volunteered Let’s say that we’re adding the hours volunteered by Li (7), George (16) and Margo (3).

Getting the most out of Excel requires the ability to write formulas. Formulas start with ‘=’ and can be basic math

=7+16+3

That is surely a formula but it’s not very helpful because it’s static. It will always be 26. It would be better to write a formula like:

=B2+B3+B4

The variables allow us to change the numbers and the result will automatically change. If we discover that Li volunteered an extra 2 hours, we can replace the 7 with a 9 and the total updates. Thus, writing formulas and thinking in terms of variables is much more useful. So let’s summarize why not just type 26 into the cell?

• It’s not dynamic
• We don’t know where the numbers came from
  • 26 shows that 26 hours were volunteered but how many did Margo do? We don’t know.
  • Where is the math? Was it calculated (possibly mis-calculated) in someone’s head? Is it scrawled on the back of a grocery receipt?
• We can’t troubleshoot the data if there’s a complaint
  • Margo says that she volunteered 5 hours. Whomever typed in the 26 did mean to type 28 and fortunately remembers that it should have been 28.

SYNTAX

Formulas have syntax. Think of this as ‘proper grammatical structure.’ This is critical in making Excel formulas work. As formulas get more advanced and formulas get nested inside other formulas, the syntax is often what generates errors and makes people crazy. But if you can develop intimate familiarity with formulas … OMG! This is where Excel gets juicy! You get to do the wildest tasks that increase efficiency and allow you access to analysis (i.e., knowledge) that isn’t handed to you all nice and neat.

This is where it helps to think in English. Develop your own language. Let’s look at the IF function:

As we go through the Top 5 Excel Formulas, I’ll include examples and discussion of the syntax. Let’s jump in!

The next post will cover the IF function.

matchsticks photo credit: marfis75 via photopin cc
dice photo credit: @Doug88888 via photopin cc
typeset 5 photo credit: Leo Reynolds via photopin cc
graffiti photo credit: Zlatko Unger via photopin cc
5th floor photo credit: boklm via photopin cc