No Links were listed yet. Go ahead and share!
How to Avoid #VALUE Errors in Excel Totals
Hey, welcome back to the Mr. Excel netcast, I'm Bill Jelen. Basically we start out with massive amounts of data and how’re we going to analyze this. Well, let’s fire up the pivot table and see if we can solve this problem.
Hey welcome back to Mr. Excel netcast. I’m Bill Jelen, Great question sent in by David. David set up the spreadsheet where he’s trying to add up numbers for various weeks and he says, “Why am I getting a value error here. I get a value error anytime that everything is not filled in.”
So first thing I did is I came up here and said, “Alright, let’s go look at the cell, C8+ C14 + C20 and came back here to see 8 totals. Alright and I see that he is doing something clever here. He is trying to make the spreadsheet look really nice and saying, “Hey if there were no hours so far this week then put in quote space quote. Well, that’s the problem because that is a space and we’re trying to do three some text and it doesn’t work.
So what I'm going to suggest is that instead of doing quote space quote then we go in there and say that we want to put a zero and I can already hear David say, “Oh, we’ve done that all. I don’t want the zeroes hang out there. It looks really bad but what we’re going to do there is we’re going to use a custom number format so I’m going to select that cell going to Ctrl-1 and we’ll use that format for positive numbers but we’re going to say hey anytime that we have negative numbers I want to show nothing and also zeroes will show nothing.
So put two semicolon on top of that, click okay and then you see what happens is when there’s a number it shows up. When there’s nothing, well it adds up to zero then here we’re going to have a three zero. It’s not displaying but it still appears and if you go through into that in all the cells there are reference down to those formats on the right and your value error will disappear.
Well, I thank David for sending that question in. See you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services