How To Troubleshoot Formulas In Mac Numbers

Hi, this is Gary with MacMost.com. Let's take a look at how you can troubleshoot formulas in Mac Numbers. 

Now when you're working with formulas in Mac Numbers it's important to know how to troubleshoot them when there is trouble. For instance here in this sheet you can see I've got two errors here. So there are two formulas that aren't working. The first thing you should do when you see one of these error indicators here is click on it to see what the error is. It's not enough to just click on the cell. That won't actually tell you what the error is. You need to click on the icon here, itself, and then you get a little description of the error. We can look here at this error here and see a similar error. 

So now that we know what these error messages are we can examine the formula inside the cell. So if you double click on the cell there you get this little floating window here that shows you the formula. But more importantly you also see the references. Notice the two references inside the formula are color coded. They match references here in the table. This helps you figure out where the problem is. In this case we know that the error was telling us that one of the references was a string and we can clearly see that's true here. The first one references oranges. We want to multiply price times amount to get a total. But it looks like we're multiplying product times price to get the total. So now we know where to fix the problem. You can actually click on the reference here in the formula and then click on the cell to change it. I'll click on this reference here and click on that cell to change it. Now, I get the correct result with no error. 

If you're ever having trouble with the cell references because they are using the column and row Headers, such as the word Price and the word Amount, and the dates there. You can switch things to see the exact cell references by going to Numbers and then Settings. Then look for Use Header Names as Labels. Switch that Off. Now you'll see C3 times D3 instead of the column and row Header names. With a lot of tables this could be the best way to do it especially if you're repeating values in the Headers. 

Now if we look at this error here it's telling us basically the same thing as the previous error, that it expects a number but the cell amount and 43 gives us a string. But to you it may look like a number. That doesn't mean it is. For instance here there is a kind of a give away. You can see space between the two digits. If I select the cell here you can look at the bottom left corner of the Numbers window, and you can see there an indicator of what type of data it is. In this case it says Text. Notice if I select the other cells with numbers it says Actual in them. But if I select anything with text, like here, it shows text. So in this case it thinks that this isn't the value 80 but is text 8 0, or in this case 8 space 0. This can often come up if you import data from a different file type. Sometimes it looks like numbers to you but actually comes across as text and you would see text down here. In that case what you need to do is go in and correct them. If you don't know what's wrong you could just simply retype it, like that. Now you could see it correctly sees that is an Actual value of 80, not text. It's common to get this with dates as well. It sees the date as text rather than an actual date and you need to reformat it or retype that text for it to  work. 

If you find these videos valuable consider joining the more than 3000 others that support MacMost through Patreon. You get exclusive content, course discounts, and more. You can read about it at macmost.com/patreon. 

Now another issue with having the Headers as the references for cells is it's hard to figure out what to type. What exactly do you type to get price 4226 here. Well, if you were typing you would type the cell reference. So C3 times D3, like that, and you a see it shows you the Header values here now. But that can be confusing sometimes. Especially if you are using cells from multiple tables. It's hard to do. So it's better to never type a cell reference. Instead click to do it. So click here, then type say an asterisk for multiplication, and then click here. That way you now you're getting the correct cell. 

Here's another example of this. I want to multiply the price times the amount but then add sales tax to it. So I'm going to type equals to start a formula here. Then I'm going to click on this cell and then asterisk for multiplication, then this cell. Then I wouldn't be sure what to type to reference this cell in a different table. But I don't have to. I can click on it. What I"m going to do is multiply and then the left paren (1+ and then the percentage here. So I'm going to click here and you can see it knows what to insert. So sales tax colon colon A 1 for that and then I can close the parenthesis. This will give me the price times the amount times 1 or 100% plus this percentage to give me the total after tax. You can see that works. 

Now to get this working in the other cells I should be able to Copy it and then Paste it in these and it should give me the correct answer. But it is going to give me an Error. So I want to see what that error is and it says this formula contains an invalid reference. Now this may be hard to figure out at first. Let's go and use the formula here to see the reference. I can see that this value matches here. This value matches here. But this value doesn't match anything. However, if I go back to my original formula that did work I can see that this value and this value matches as expected. This value matches here. So what's happening when I Copy and Paste to the next row below, is it is moving everything down one row. So these two references move down to these two, and this reference also moves down to the next one. But there is only one cell here. As a matter of fact if I were to expand this table and then Copy and then Paste I can see that it is referencing this cell right here, which isn't what I want. So the problem here is absolute versus relative cell references.  So, in this formula  here this first cell is a relative reference. As I Copy and Paste the formula down it's going to move down. The same thing for here. But this one I don't want that. I want it to stay in Row 1. So I'm going to click the little down arrow next to the formula here and say that I want to Preserve the Row. You'll see it puts a dollar sign in front of the 1. In other words keep row 1 no matter where the formula is copied and pasted. So now when I copy this cell and paste it here the references for these two move down but this one does not. I can paste it here as well and these two references move down but this one does not. 

Absolute versus Relative referencing is probably the number one reason for formula errors. So be sure you understand it. 

Now another problem is when you're using a function and you get the function wrong. So in this case I just want to take these amounts and convert them to euros. So if I look at the formula here I'm taking the amount and multiplying it by the currency exchange rate. I'm using currency to do that. I discovered that by searching here on the right and looking for currency. Then I simply typed USD and Euros. But it is giving me an error. The error itself just says Syntax error. It doesn't give me much information. So what you want to do here is you want to look up the function and read, very carefully, what it says. In this case you can see it's currency 1, currency 2 and the third parameter is actually optional. So far it looks like we're doing the right thing. Then we want to scroll further down here and look at the Notes. You can see currency codes are defined by, and then it gives you a standard. So you could search for that to see if the codes are right. You'll find out that Euros is not the right code. It's Eur. If we search down further we'll see examples and we'll see, oh these have to be in quotes. As a matter of fact way up here at the top it says the currency code for the currency, you can see here it says, a string enclosed in quotation marks. So let's make the corrections based on what we've carefully read there. I'll add quotation marks around that. I'll add quotation marks around this and I will change it to match the Standard, like that. Now the error goes away and I get the right result. 

When somebody asked me a question about a function that isn't working properly I'd say 90-95% of the time the answer was there in the description of the function all along. 

But what about when you have an even more complex formula with lots of different functions and references and it's not working right. There are a couple of strategies  you should use to fix it. So, as an example here, I've got this table that gives me prices. I've got products here on the left and then days of the week in columns. The idea is every product has a different price on each day. Then I've got some Sales here. So I've got the product. I want to get the price from this table. So, it should use the Date to figure out the day of the week. It should take the Name of the product and look it up. In this case April 1st was a Wednesday and this is apples. So 52cents. It gets it right. This is the formula for it. You can see it is pretty complex. So what if it was getting it wrong? Let me show you some strategies for dealing with that. 

First, when you've got something complex like this you should build it step-by-step. Or if you already have it built and it's not working you should deconstruct it. So the Index function is going to take two numbers. In this case it is going to take 2, the row number, and 5, the column number to get 52 cents. How's it going to get those numbers?  Well it is going to use Match Functions. It's going to match the product name, you can see how the color matches here, with column A here in this table. So Apples, Oranges, Peaches. It is going to match it here to row 2. Let's make sure that is working by removing everything except that one match there. We're going to simplify it. Just match the product to the list here that's the Header column. We can see here 2. It's getting it right! So that's good. Now we can just use Command Z to Undo. 

Now let's go back in here. Let's get rid of everything else except the second match. The second match is a little more complex. It's going to take the Date here and run it through the DateName function and then it's going to take that dayname and then it's going to match it against the Header Row there. Let's check the dayname out first to make sure it is doing it right. So we go down to the smallest piece here and it returns Wednesday. So we know that part's working. So I'll Undo again and now I'm going to checkout the entire Match part here, like that and see if the Wednesday, we know this gives us, will indeed give us a 5 from here. It does! So we can now, again, Command Z to Undo and we know this entire thing will work. 

But if there was a problem we would have spotted it by deconstructing it like that or by building it one step at a time and checking the result each time. But here's another technique you can use for this. If you suspect something isn't working you could just try replacing a piece with a hard coded value. So, for instance, to rule out whether or not dayname is working correctly I can just type Wednesday there. So if dayname wasn't working right I can now get the right result. If I had the wrong result with that in there we know that the problem lies within that part of the formula. You can do it with this whole Match section here. Basically say 5 and then this one as well, say 2. So we can check whether or not the Index Function is working by specifically giving it this range here and 2 and 5 and see if it returns the right value. We can go in and test other values as well. What if it was Thursday, 2 and 6. We can see it gives us that result. 

So there are a whole bunch of different techniques for trouble shooting formulas. Remember there are actually types of errors. One where you actually see an error mark on your spreadsheet and you can see what the error message is and work from there. The other type of error is when it actually gives you a value. But it is the wrong value. Use the same techniques to figure out what's wrong with those kinds of problems as well. I hope you found this useful. Thanks for watching.