Same Formula - Different apparent Result

[nebb]

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?

[MrShorty]

Can you post the formula?

[roly]

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

[nebb]

Can you post the formula?

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

[MrShorty]

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.

[nebb]

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.

[MrShorty]

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.

[nebb]

I found a simpler way of looking at this problem. In one worksheet, cell A1 contains the formula =A5. When I put a blank in A5 (by deleting the contents of the cell), cell A1 shows a blank. When I enter a zero (0) in cell A5, cell A1 continues to show a blank.
On the other worksheet, cell A1 again contains the formula =A5. On this worksheet, when cell A5 contains a blank, cell A1 shows a aero (0). And when cell A5 is 0, cell A1 again shows a 0.
All characters other than 0 and blank work as you would expect.
I am using Excel 2000. The fact that 0 and blank show up the same in cell A1 could be a bug in this revision. But why does the other worksheet show a different result. All cells are formatted as General.

[MrShorty]

Have you checked the tools->option dialog for a "display zero (0) value" check box? I would have expected that to be a global option, but perhaps it is behaving as a workbook/worksheet specific option for you.

[nebb]

Mr. Shorty-You hit the nail right on the head with your last suggestion. I checked the tools/options/zero value box and sure enough the sheets were checked differently. Eliminated the check mark for those that were checked and all three worksheet work the same. My sincere thanks for your help.