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.)
, 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)