Blank and 0's in Cells

[Klee]

I feel like this may be a really dumb question but here goes. I've been
using Excel for several months now to creat various spreadsheets. In order to
avoid formula errors or to end up with 0's on all the unused lines I have
been using If formulas for example:

=IF(L67="",IF(N67="","",-N67),IF(N67="",L67,IF(N67-L67=0,"",L67-N67)))
as you can see I'm doing little more than subtracting one cell from another
but it becomes this big long mess

When I am working on longer formulas it becomes a real pain to then go back
and add all the above in to accomodate cells without a number in them.

Please tell me that there is an easier way to do this??

Thanks in advance,

Kris

 

[vezerid]

Note that a blank cell will not produce an error when used in an
arithmetic operation. It will simply count as 0. I.e., if A1 is blank,
the following formula will not produce an error but just give -B1

=A1-B1

By contrast, an explicit empty string literal in a formula WILL cause
the #VALUE! error as in the following:

=""-B1 <<< returns #VALUE!

From your formula attempt I understand that you display "" also if

both numbers are nonzero but equal hence producing a 0. If so, the
following simple formula:

=IF(L67-N67=0,"",L67-N67)

Another thing you can do is simply use

=L67-N67

and then select the cells, Format>Conditional Formatting... and set a
white color font.

You can also make 0's appear as blank from Tools>Options>View> Zero
Values but this will hold for the entire sheet.

HTH
Kostis Vezerides

 

[Klee]

This is very helpful, thank you. I especially like the Tools/Options/Zero
Value. I think it will work great. Do you know if there is a way to set it so
that it automatically uses that option every time you open Excel so I don't
have to change each time I open a spreadsheet?

Thanks again, I appreciate it.

 

[JMB]

Another alternative is to use a custom number format. Format/Cells/Custom

#,###;(#,###);

 

[Klee]

Thank you JMB, this is the coolest thing ever. I put the formula in then made
it my default woorkbook. Now I don't have to do those long nasty formulas for
it anymore. I could just kiss you! You've made my day.

 

[JMB]

You're welcome and thanks for the feedback. There's some more information on
custom number formats here:

http://mcgimpsey.com/excel/conditional6.html

Also, see XL help about custom number formats for more info.