Same Formula - Different apparent Result

I have a workbook consisting of 4 worksheets. Cell B5 in each of three of these sheets has the exact same formula (copy/pasted) referring to data in the other worksheet. In one of the three similar sheets cell B5 shows up as a blank. In the other two, this cell shows up as a zero.
Since the formula in each of these cells is identical, I figured the difference in the way the result showed up was a matter of cell formatting. The cell format for each of the three is indicated as ‘general’. However, with one of these, the ‘sample’ shown when you view the cell format shows as a blank while the other two show the ‘sample’ as a zero (0). How can I go about making all three cells appear the same, namely a blank?

Can you post the formula?

Originally Posted by MrShorty

Can you post the formula?

=IF(ISERROR(Data!E7),Data!G7,IF(OR(Data!E7=0,Data!E7=""),Data!G7,Data!E7))

It appears that your formula looks at E7. If E7 is an error, a 0, or an empty string, it returns what is in G7. Otherwise it returns the value in E7.

That begs the question, what is in G7 and E7?

My first thought as to what might be different: Is E7 blank in any of the cases? Note that a cell with an empty string is not blank. Blank means nothing in the cell. If E7 contains an empty string, and the "value_if_false" argument is returned, the formula will return an empty string. If E7 is blank (nothing), it will return 0.

Thanks for your responses Mr. Shorty. Cell E7 contains the result of the following formula---------=IF(VLOOKUP(LEFT(C7,4),'Club #'!$C$3:'Club #'!$E$202,2,FALSE)=2,"",VLOOKUP(LEFT(C7,4),'Club #'!$C$3:'Club #'!$E$202,3,FALSE))--------When cell C7 is blank, cell E7 shows a zero (0).

However, my dilemma is that I cannot see why, regardless of what is in any of these cells, the exact same formula used on different worksheets in the same workbook referencing the same cells shows a different result.

That's what debuggins is all about -- trying to figure out why something isn't working. That's also why debugging can be so frustrating. It seems that when I'm debugging problems, once I say, "I can't see why..." I develop this mental block that prevents me from seeing the problem.

FWIW, I can't see why it's not working either, but part of that is because I can't see the formulas in their entire context.

At this point, I think the best thing you could do would be to prepare a sample worksheet with the problem, zip it up and attach it. I can't download spreadsheet attachments, but many other users can and then they can look at the formula in its context in the spreadsheet and perhaps they can see where it's going wrong.

Hi,

You can use a custom cell format to set zero values to anything you want.

Click Format, Cells, Number, Custom #,##0;-#,##0;;

What this does is sets positive numbers to 1,000 and separated by ; the next part sets negative numbers to -1,000 and sets zero values to an empty cell. If you don't want thousands separated by a comma (1000), remove the comma and if you want two decimal places, put .00 on then end.

Otherwise you can start your formula with IF(cell reference=0,"", and put a close bracket at the end of the rest of the formula.

HTH,


Roly