Blank Cell returning #VALUE error when used in a formula

Ok, this is the strangest thing I've ever encountered.

I've used a vlookup formula to populate a field of cells, using "=IF(ISBLANK..." to ensure that blank cells remain blank, instead of returning zeros. (I need to keep them blank for the purpose of easy reading of the document.)

I'm attempting to use these returned values in another simple multiplication formula on the sheet, however the formula doesn't work for the blank cells, and returns the #VALUE! error.

I tried copying and pasting the whole field as values, and I still get the #VALUE! error. I saw that all the blank cells now had apostrophes, so I copied and pasted the whole field to another worksheet as values, which removed the apostrophes, and then returned it to the original sheet. The simple multiplication formula STILL returned the #VALUE! error. I saw that the cell was a General Format cell, and formatted it as Number, and still the same error.

I then (for kicks) selected an as-of-yet unused blank cell on the same worksheet, outside of the cut-and-pasted field, and applied the multiplication formula, and rightfully got a zero.

Then, I was finally able to get the multiplication formula to work for that blank cell by double clicking it to get a cursor. I didn't type anything in there, I only made sure there wasn't a space. Once I exited the cell and applied the formula to that cell, it worked, and instead of a #VALUE! error I got a zero.

So the question is, why would one blank cell (with no apostrophe, no space, and formatted for numbers) behave differently than my cut-and-pasted cell (also, no apostrophe or space, and formatted for numbers)? And why would double clicking a cell change how that cell ultimately behaved?

Any help would be greatly appreciated!

Well, the multiplication isn't working because you are trying to multiply with a text string. Blanks in a cell that are returned as the result of a formula "" are treated as text.

- Moo

You could try this: =IF(ISNUMBER(A1),A1*B1,0)

Where A1 is the cell with the ISBLANK formula. And B1 is the cell/value/condition you want to multiply with.

- Moo

Thank you Moo, that makes sense. Lesson learned: formatting a cell with "text" in it as a Number doesn't change the value in the cell to a number.