MrExcel's Learn Excel #767 - #VALUE Errors

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 30, 2009.
A question asks how to avoid #VALUE errors when a formula refers to a cell with a blank text value. In Episode 767, I will take a look at how these blank cells come to be, how to work around the #VALUE error and even a different approach to prevent the root cause of the problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Now, this question came up in a seminar, and I wish I'd gotten the person's name because when I came back and tested it, it was really tough for me to duplicate the problem that he said that he had.
He had one worksheet here with some data on it and he was basically using formulas to copy this data to another worksheet.
So, here, on my secondary worksheet, I have the headings and basically, you know, to set up a link between one sheet and another, it's fairly easy.
Type the = sign, use the mouse, go back and click on the right cell, and when we copy that across, all the values come through -- except for the frustrating issue of blanks.
The blanks come through as 0s all the time, and so this person said that he had rewritten those statements to be an IF statement.
=IF is blank (, and then he had his text.
If it's blank, he was putting in “” to make sure that nothing showed up, otherwise he wants the cell, and copy that down, and this is a common trick, but he said the big problem with that is, now, when he creates formulas that point into those cells -- so maybe we'll have a TOTAL here and this TOTAL needs to be just Q1+Q2 --what is going to happen is because there's blanks, we’re going to get value errors all the way through, okay, except for this case where both numbers happen to be numeric, but you're going to have some value numbers show through, and he said, you know, I don't know what to do about this.
[ =IF(ISBLANK(Sheet1!B4),“” ] Well, you know, a couple of things, when I first tried this, I couldn't replicate the problem because I was using the SUM function out here.
=SUM.
I want to SUM this cell, E4, and this, I4, and the SUM function is going to handle this perfectly, converting the labels to 0, and it'll work.
So, that's one solution.
The other solution is we could go backwards just to the original formula.
So, here, we just have the formulas that have lots of 0s filled in.
Let's go and set up a custom number format.
FORMAT CELLS, and we'll come to CUSTOM, and, basically, what we want to do is say, for positive numbers, we want to show 0, a ;, for negative numbers, we want to show – and a 0, and then, for 0, we don’t want to show anything.
So, that last ;, we're now in the third zone, the 0 zone, and we say we don't show anything for zero, click OK, and, sure enough, that's a very easy way to change all of the 0s to blanks.
Well, there you have it, a great way to make sure those 0s cells show up as blanks and still allow the formulas to work.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top