Office & Productivity Software forum

General discussion

Display an empty cell instead of zero

by wait3264 / June 15, 2007 9:42 AM PDT

I have a spreadsheet that uses the values in several other cells to calculate a value for a cell. This is an example from one cell: K5=E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3. How do I get the result cell, in this case K5, to display nothing instead of 0.00 when the cells used to create the formula have no values? I thought about the IF function, but that seems like it would make a long formula even more complicated. Any suggestions? Thanks, Kay

Discussion is locked
You are posting a reply to: Display an empty cell instead of zero
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Display an empty cell instead of zero
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: Excel format
by Kees Bakker / June 15, 2007 9:32 PM PDT

Kay,

This depends on what you REALLY want. Some close reading shows a contradiction, you know. I'll explain it later. It might well be that - in the context of your application - the contradiction doesn't arise, but you don't tell about that, so I assume the worst case.

You can easily format the cell to display nothing when the result of the calculation is zero. Just use a customized format 0.00,-0.00,. The first is used if the value is >0, the second when it's <0 and the last (plain nothing) when the value =0.

The problem is that this has effect for EVERY value of zero. If, for example, E5, F5, G5 and H5 are all 0 the result is zero, and you'll see nothing. And if they are empty, you'll see nothing also.

Your requirements can be interpreted as:
1. show nothing if ALL cells are empty (as asked in your post), or
2. show nothing if at least ONE of the cells is empty (not really asked, but different from #1), or
3. show nothing also if there's no empty cell, but the outcome of the formula is zero (as asked in your subject line)

If you want #1 or #2 you must use an IF-function, combined with ISEMPTY and AND or OR.
If you want #3, use the formatting I explained above.

Hope this helps.


Kees

Collapse -
display nothing
by wait3264 / June 15, 2007 9:49 PM PDT
In reply to: Re: Excel format

what I want is that when no values are entered in the cells that are used to calculate the value for the cell with the result of the calculation, I don't want 0.00 to be displayed as it does now. I only want a value displayed in the result cell if the value is greater than 0.00. Does that make it any clearer? Thanks, Kay

Collapse -
Use IF
by R. Proffitt Forum moderator / June 15, 2007 9:58 PM PDT
In reply to: display nothing

=IF(A1=0;"";2)

I use IF to accomplish this.

Bob

Collapse -
As I said ...
by Kees Bakker / June 15, 2007 11:48 PM PDT
In reply to: display nothing

use a custom format for the target cell:
0.00,-0.00,
if you only want to see values other than 0 and not want to use IF.

The way to do it with IF is somewhat more complicated, as you already wrote: =IF(expression<>0,expression,"")

There's no way without an IF to only see values > 0. You need an IF to suppress negative values. But you can use the above custom format to suppress the zero only.

Hope this helps.


Kees

Kees

Collapse -
In your case
by MarkFlax Forum moderator / June 15, 2007 11:54 PM PDT
In reply to: display nothing

the formula would be;

=IF(K5=E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3=0,"",K5=E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3).

What this does;

The "" are two quotes, (on my UK machine thats Shift + 2, twice). This denotes an empty string, or an empty cell.

So, if "K5=E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3" results in a 0, the result will be "" or an empty cell.
However, if K5=E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3 is > 0, then the result of the calculation
K5=E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3 displays in the cell.

May I suggest...

In algebraic expressions the rule is, carry out multiplication and/or division first, then addition and/or subtraction. But sometimes you may want to add something then multiply the result. So in such cases brackets are used for individual expressions to make it clear what is to be done, the expression in brackets is carried out first.

Excel doesn't need you to enclose anything in brackets in your expression above, but if you wanted E5 x (E3 + F5), then Excel would not calculate that. It would instead calculate E5 x E3, then add F5.

In your expression I would change it to this;
K5=(E5*$E$3)+(F5*$F$3)+(G5*$G$3)+(H5*$H$3), so the new formula will look like;

=IF(((E5*$E$3)+(F5*$F$3)+(G5*$G$3)+(H5*$H$3))=0,"",((E5*$E$3)+(F5*$F$3)+(G5*$G$3)+(H5*$H$3)))

It's a good idea to use brackets even though they may not be needed, because you will likely forget to use them when they are.

Mark

Collapse -
Minor correction.
by Kees Bakker / June 16, 2007 6:16 AM PDT
In reply to: In your case

Mark,

You write
=IF(K5=E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3=0,"",K5=E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3)
but of course it should be
=IF(E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3=0,"",E5*$E$3+F5*$F$3+G5*$G$3+H5*$H$3)

x=y=z seems a valid expression to excel, but whatever I fill in, it evaluates to FALSE. So this IF-statement would show FALSE also.

Your rewritten expression, with brackets, is correct.

Kees

Collapse -
I agree
by MarkFlax Forum moderator / June 16, 2007 7:39 PM PDT
In reply to: Minor correction.

I got it right in the final version of the formula, but not the first.

Thanks Kees.

Mark

Collapse -
I need help with my formula
by Holla9177 / December 3, 2009 11:49 PM PST
In reply to: Re: Excel format

I have a formula which equals a cell from sheet 1 on sheet 2. I want that cell to display nothing if the cell on sheet 1 has no value in it. How do I do that exactly???

Collapse -
Re: displaying
by Kees Bakker / December 4, 2009 4:41 AM PST

Use the isempty function in an IF?

Kees

Collapse -
Another IF option
by MarkFlax Forum moderator / December 4, 2009 10:22 PM PST

I would use something like, IF(Sheet1!A1="","",Sheet!A1)

Not sure of the correct naming for other sheets in a formula, but what that IF statement does is this.

If A1 in Sheet1 is empty, then the cell in which 'this' formula is placed will also show empty. However, if A1 is not empty, the cell with this formula displays the contents of A1).

Hope that helps.

Mark

Collapse -
Display void cell instead of zero
by Swartswaan / June 16, 2007 5:44 AM PDT

Hallo wait,
Two methods for your idea. Select the cells you want to display as void when zero. Click on the comma in the Standard Toolbar to format the cell as a currency type. This will display a small hyphen when the cell vlue is zero. Or hit Alt-o-d for a Conditional Format, follow the boxes to set the font color to something as near the background color as you can (you can also edit that colour to be similar to what you want in Rightclick Desktop > Properties > Appearances > Advanced). This will make the cell really look void.

Collapse -
Display void cell instead of zero
by wait3264 / June 16, 2007 9:14 AM PDT

Thank you, the comma is exactly what I was looking for. I don't mind the little hyphen in the cell, I just didn't like the long column of zeroes as my spread sheet will have over 100 rows.

Collapse -
Difference between formatting and IF.
by Kees Bakker / June 16, 2007 11:14 PM PDT

Using any formatting solutions (two have been presented) the value in the cell will still BE zero and only LOOK empty. So it's counted in the count function and taken into account in the average function.

Using the IF to replace the 0 by a space or an empty string, will cause the value in the cell to BE a space or an empty string. So it's not counted in the count function, and not taken into account in the average function.

Depending on the requirements, one of these two solutions might be discarded, because it doesn't meet them.

Kees

Collapse -
difference
by wait3264 / June 17, 2007 8:38 PM PDT

That is a good point, one that would not have occured to me. I do have another spreadsheet which uses the IF function in which a calculation if performed only if a cell has a positive value. However, I set the value of the cell to 0 as the default. I suppose in the cases you have mentioned, you could spend quite a bit of time trying to figure out why you were not getting the correct results on your spreadsheet. Thanks, Kay

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

FALL TV PREMIERES

Your favorite shows are back!

Don’t miss your dramas, sitcoms and reality shows. Find out when and where they’re airing!