Hi, this is Wayne again with a topic “Fixing Common Excel Errors – Part 3: REF and VALUE”.
This is fixing common, Excel errors, part 3. This is the third video in a three part series on how to fix common, Excel errors. In this particular video we’re going to focus on two different common errors, one is the ref error and the other is the value error. You can see the spreadsheet we’re going to use. This is an inventory with a list of supplies where they’re stored and some other details about them, like the number of packages, the quantity per package, and then we have a formula to calculate okay. If there are 20 packages of batteries and six batteries per package, we have 120 total batteries and, like I say, that’s calculated automatically with this formula now to illustrate the ref error. Let’S say that I decide that you know what this data here is really not necessary.
I don’t need to know the quantity per package. I just need to know the total quantity. So what if I just go in and delete columns CMD I just clicked and dragged from C to D.
It selected the entire columns and I’ll just delete. You can see what happened. I got a ref error and the reason for the ref error is the formula referred to cells that no longer exist in this formula.
Those cells are completely gone. I deleted the entire columns. Those cells are gone, I’m gon na click away and undo that with ctrl Z, to bring it back, and you can see that solves the problem.
Now. What if I just delete one cell I right-click on the cell, I click delete Excel. Is gon na shift cells around to try to replace the cell, I’m destroying, and you can see what it did it ruined the formula here? It can’t find the cell that it was referring to it’s gone, and so it gives us a ref error for reference.
Basically, so how do you fix that? Well, you can either undo you can realize. That was a mistake. I can’t just delete this data or, if you really have to delete the data. That’S okay, but just going to your formula by clicking on the cell with the ref error and you can double click on it and make the changes right there in the cell or you can just click once and go up here to the formula bar.
Whichever way you want to do it, you need to go in and fix the formula, so this is where the error is. I can just delete that and say: okay C, 3 multiplied by D 3 tap enter that fixed the reference. Now the references are valid and it’s good. Now, let’s move on to the value, error and you’ll get the value error. When you use the wrong function, argument or often what it is, is you’re trying to combine different types of data that really don’t go together. So like a number plus a text or maybe you’re multiplying a word by a number that won’t make sense in most cases.
So let’s look at our first example of mistakes that might lead to the value error. Let’S say over here on the right: I’m trying to multiply the number of packages by the quantity per package like I did here successfully, but let’s say instead of c2. What if I were to put b2 so now, I’m multiplying the third floor closet by 6. Let’S see how that goes, I tap enter and I get the value error. Why? Because I’m combining two types of data that just don’t go together: text multiplied by a number value. Another example: that’s kind of common is let’s say, as I’m typing in the number 11. What, if I’m in a hurry – and I accidentally put a space in there – it still looks pretty good. It looks like 11 to me, but I tap enter on the keyboard and I get a value error that extra space just doesn’t compute as a number another example down here.
Let’S say again I’m in a hurry and as I’m typing in the number 30. Maybe I accidentally type in 3. Oh instead of 3-0. Let’S say all caps is on and that looks like 30, but I tap enter again, I’m combining things that just don’t go together: 3o x, 4 that doesn’t make any sense, and so I get a value error.
Another fairly common mistake that will lead to the value error is sometimes when you put symbols in a cell with numbers. Excel doesn’t like that sometimes, and you’ll get a value error now watch what happens. If I put a dollar sign in there. Normally, you wouldn’t do that. You wouldn’t just put a dollar sign in.
Instead, you would select the cell and go up here on the home ribbon in the number group and click on dollar sign. That’S the proper way to show that these are dollars, but from time to time, people will just go ahead and type a dollar sign. Now that actually works, it doesn’t cause an error, but other symbols often do cause errors. For example, let’s say I put a hashtag in there, so hashtag 28, maybe I’m trying to say number 28, but I tap enter on the keyboard and I get a value error so watch out whenever you’re putting in symbols like these and you’re putting numbers. Also in the same cell, that will often lead to a value error. So when you get these value errors, take notice of what is in the cells and if there’s mistakes you need to fix them. Obviously I can go in and change it from b2 to c2 that’ll fix that one.
In this case I can fix the oh and change it to zero, and in this case I can get rid of the symbols. Now. Some of you may be noticing that my formulas are all messed up a little bit. That’S because of my previous example of the ref error and how I deleted a cell notice that it moved everything up and that’s why my formulas are a little bit off. But the point remains that if you try to combine different types of data together in formulas, if they don’t go together, you will probably get the value error thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow and subscribe and when you subscribe click the bell so you’ll be notified. Whenever I post another video and if you’d like to support my channel, become a supporter of mine through my patreon account and you’ll see a link to that in the description below .