0

I have read the answers to different ways to display zeros as blank in a spreadsheet. I don't want the entire spreadsheet to behave this way so the sheet setting won't do what I need and I don't want to use conditional formatting.

I have seen 0;-0;;@ as an answer and this would work except this column has currency in it and I want to display two decimal places (don't care about a currency sign). If I put in 0.00;-0.00;;@, this always displays 0.00 on blank cells. I supposed this is because I haven't found a decent explanation for what the ;;0 does. Is there a way to display #.## or "" depending on whether there is a number in the cell or not.

Just to clarify, the actual cell is not really blank, it is the result of an if statement on a vlookup returning 0 if N/A on the lookup or else a numerical value, ie. =IF(ISNA(VLOOKUP(A5,name,8,0)), 0, VLOOKUP(A5,name,8,0)). The issue I am running into is if I leave the if statement as is with a 0, I have a bunch of zeros in the column that I don't want. If I replace the 0 with "", then my formula which references that cell (d5) (=c4+d5-e5) returns #VALUE!. Also, (=c4+value(d5)-e5) also returns #VALUE!.

The way I have solved it temporarily is to have the vlookup if return "" and have the formula that references that use an if(d5="",c4-e5, c4+d5-e5) but this is ugly also.

Repeating, I am looking for a way to display a numeric value other than 0 with two decimal places and zero values as blank.

Thanks much for any responses.

2
  • 3
    Change the , 0, in your formula to , "", and it will show a blank. But I would also use IFERROR: =IFERROR(VLOOKUP(A5,name,8,0),"") then use SUM which ignores the text: =SUM(C4,D5,-E5) Jan 21, 2020 at 22:00
  • Thanks for the info. I figured out that sum ignores blanks, so I was able to fix the entire issue by substituting "" for 0 to keep it from displaying and then changing my formula that calls that cell to =sum(c4,d5)-e5. (Same as Scott suggested). Thanks much.
    – rwrjr
    Jan 22, 2020 at 0:19

1 Answer 1

-2

Lots asked here. Let's try to hit most aspects.

1) The 0.00;-0.00;;@ format IS the way to go and it does work as billed. Since you would write the formula then format the cell, or the reverse, THEN copy the whole cell and paste as needed, the format would follow along to all places the formula ended up and would apply to no other cells. So only 0's the formula returns would be formatted this way with the rest of the sheet showing them unless you particularly format elsewhere too.

2) You state that it does not work on blank cells, showng 0.00 for them. Well, this might be the case if you are using a country edition that uses a different separator here than ; but then it wouldn't work in the 0;-0;;@ version so that seems not the case here. You mistate the last item in the format as 0 not @ which COULD affect real blanks... except that Excel will not accept that format so it would never apply it either. Possibly though, you are not noticing the refusal, just punching ENTER and are really applying... nope, that doesn't pan out either and even if it would take it, the display would be a 0 not 0.00.

There is only one real likelihood that occurs to me. Excel has an issue with what the average Joe would think of as precision. Essentially, many calculations that should return 0 do not. They return something like 0.000000000000002. So Excel would see it is not 0 and apply the formatting for positive numbers: 0.00 in this case. Which is what you report. So consider whether the data cell being returned could be the result of calculation, either in your spreadsheet directly, or in the source of the cell's contents if from a different (Excel probably) source. Some other spreadsheet.

If that is the case, then you can address that a variety of ways depending upon whether this is the endpoint for the result here. If so, if it will never be used again, wrap things with a truncation function, use three decimal places so it can be rounded properly. But if the data needs to be used in something further along the way, you'll have to take some different approach from the various ways to handle this possibility.

3) HOWEVER, if you have true blanks, well, anything that LOOKS blank, visually, there could be difficulties using the above format. Why? Consider that your formula evaluates for IFNA(): you trap the #N/A error but allow other kinds of errors. You are not using IFERROR() which would trap ALL errors. Why? Presumably because you expect some or many #N/A errors and feel that all of them are due to an acceptable reason and so do not care to see them. But wish to see and be able to react to any other errors because they would indicate "real" errors, things you need to know about and track down. Well, the above format will show these visual blanks as blanks here as well, just as if they contained a value of 0, an actual entry of 0. But maybe that's not the case for some, or many. Maybe there are plenty of "" values populating the data cells. Or real nulls, emptiness, no entry in those cells ever. Maybe you need to know that. If not, all is cool but if you DO need to know that, like not wanting to miss a #REF! error, then the above format will not do good things for you.

Obviously, wrapping things with the ISBLANK() function wouldn't help either.

Further, whether that's an issue for you or not, if the results of these calculations are then used somewhere else, bear in mind the format of a real 0 is as a number while the format of a visual 0 (all those blanks) is TEXT, not number. So a calculation taking this as an input would fail if it can't handle one o fits inputs being a text item instead of a value. This path can get complicated so your actual situation would be helpful in suggesting ways forward, if this is an issue.

And obviously it IS an issue since you end with talking about dsealing with exactly that kind of problem.

Most visual 0's will have lengths of values other than 1. One could test the lookup's result against that, then test the length of a SUBSTITUTE() function that uses the result as input and replaces 0's with "": use an OR() to do both in the same formula step. OInly if the result evaluated in the 0 family (or no test needed, eh?), and if this testing showed it to be 1 character long, AND that replacing 0's made it 0 characters long would you treat it as a true 0 value. All the failures wouuld be visual 0's but really be lanks and errors that slipped through as blanks in the result. If really a 0, pass it through. If a blank of some kind, use an IF() to make it a real 0. Then all the follow-on formulas using the results here would get a numerical value always, not just most of the time, and would always succeed.

Most important of all though, the only strong reason I can see for the format to "FAIL" is the part above about Excel 0's not always being 0's. So if this data is the result of calculations, currently, or in the data's past, my money is on that 0.000000000000002 kind of result in the data cells. (If you have 100,000 rows and 23,000 show 0.00 raather than blank when the formula is applied, this is NOT the explanation. But if you have 100,000 rows and you have rare 0's, it could easily be so.)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .