This is the third post in a series about spreadsheets, called Spreadsheets 101. Click if you missed the first (Spreadsheets are Powerful. Here’s How) or second (How to Use Formulas) post.
Mistakes happen. But boy they can be frustrating, especially if you’re learning something new or not feeling so confident with your skills. In terms of spreadsheets, these mistakes can show up in one of two ways: a value that doesn’t make sense or an error message. In this post, you’ll get the ins and outs of diagnosing and fixing these problems.
Errors in spreadsheets are almost always user-generated. In other words, you can’t blame the developer or your computer or Mercury rising. This is both good and bad news — and the process for identifying and fixing these issues is very similar to working your way out of a vexing math problem. Don’t let that worry you. Instead, think of it this way: you have a great opportunity to deal with two difficulties at once.
Bad Numbers
When numbers matter, it’s always a good idea to check everything carefully. This might feel like a real drag, but this little habit can save you time, money and heartache in the long run. Take a look at the spreadsheet below. Can you spot the questionable values?
Did you notice the numbers that are out of whack? They’re in E12 and F12: $36,926 and $34,076. It would be awesome to earn that much dough from the sale of 1,000 ebooks, but with a net of $3.69 per book, that doesn’t make any sense at all.
There’s a problem with one of the formulas — probably in E12 or F12. So, let’s take a closer look. If this were your spreadsheet, you could double-click on E12, showing the formula.
Now, this time, I’ve also included the tool bar over the table itself. Notice that the formula is listed to the right of fx. You can also see it in the cell itself. This formula says that you want to multiply the value in D12 by 10,000. (Count the zeros, if you can’t quite read it.) In other words, this is the net, if you were to sell 10,000 ebooks. But what you’re looking for is the net on 1,000 sales. Ta-da! That’s the problem.
Next, you want to fix the error and see if that solves the problem in F12.
Yep, it did. See what happens there? If you make a mistake in one cell, it can carry to other cells. It pays to be diligent.
In this case, I had simply typed too many zeros. Another easy mistake is referencing the wrong cell (typing C12 instead of D12, which would be a tougher mistake to find). It’s also fairly common to accidentally add a cell name to a formula, by clicking the cell before closing up the formula.
Finally, errors in using the order of operations are really easy to make. If you need to add before multiplying, be sure to put the addition step in parentheses. Otherwise, the computer will follow PEMDAS — multiplying before adding. Take a look:
=((D12+E12)*5) means: Add the values in D12 and E12 and then multiply by 5
=(D12+E12*5) means multiply the value in E12 by 5 and then add the value found in D12
Big difference!
The Formula You Typed Contains an Error
Sometimes your spreadsheet program might give you an error message, like the one below.
Of course the error message isn’t helpful at all. But look closely at the formula in the cell. Instead of typing “1000”, I’ve typed “1,000”. And that’s a big no-no. Take away that comma separating the 1 from the 0, and all will be right with the world. Other characters you want to avoid include dollar signs ($) and percentage signs (%). Stick to the symbols outlined in the previous post on building formulas.
The key here is not to ever make mistakes but to identify them, if you do. Review all of your formulas before trusting their outcomes. Check that you’ve included the correct cell names, operations and used parentheses where necessary (so that your order of operations is correct. If you get an error message, look for symbols or letters that shouldn’t be in the formulas.
With a little attention to detail, you can be sure that the data generated by formulas is good to go!
What steps do you take to troubleshoot your spreadsheets? Do you have some advice to share or questions you need answers for? Talk to me in the comments section! There’s one more post coming up soon. Later this week, I’ll teach you how to make pretty graphs using spreadsheets. So easy, you won’t believe it!