Hide zero if there is no value

[neokragu]

Hi friends I cant find solution for this, So pls let me know.


. A B C
1 20 15 5
2 10 10 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0


Pls look above,

The formula entered in C1 is A1-B1 and the cell C1 is fill down up to C10
I have entered values in rows only A and B. And when fill down C all the cells are showing zero. If I hide zero values the cell C2 that also hidden. I want the cell C2 should show zero because it has value. The other rows from 3 to 10 doesn't have values ans it should hide the zero from C3 to C10. I will input values in row 3 to 10 later.

Please any one explain me the solution

[pjwhitfield]

when you say "hide zeros" do you mean hide the rows or leave the rows existing but show blank in the cells?

[AlanY]

how about change C1 to =if(A1=0,"",A1-B1)

[neokragu]

leave the rows existing but it should show blank cell

[neokragu]

Thanks a lot... worked what I expected partially

Even column B have any value the result is blank

I need the result should be shown in C1 if any of one cell either A1 or B1 have values.

[popipipo]

=if(count(A1:B1)=0,"",A1-B1)

Try this

[ELeGault]

If A or B is 0 th3n be blank otherwise get the difference of A - B

Please Login or Register  to view this content.

[martindwilson]

=if(or(a1="",b1=""),"",a1-b1)

[ELeGault]

MartinDWilson : "

Please Login or Register  to view this content.

"

Agreed, if they have an actual 0 in place it will ignore it so A = 10 and B = 0 C should = 10 where my previous suggestion blanks it out - good catch!

Martin's update is the one you want!

Please Login or Register  to view this content.

[newdoverman]

In light of the OP's statement "I need the result should be shown in C1 if any of one cell either A1 or B1 have values", this is my interpretation:

Please Login or Register  to view this content.

	A	B	C
1	1	1	0
2		1	-1
3	0
4	0	0
5		1	-1
6	1		1
7

[ELeGault]

In light of the OP's statement "I need the result should be shown in C1 if any of one cell either A1 or B1 have values", this is my interpretation:

SMH - you would be correct sir , all though what if you wanted to do a 0 - 0 or 5 - (-1)... we would exclude negative integers and results if you actually put 0 in both columns as you did in row 4... in which case we may go back to what Popino was doing with a small alteration...

Please Login or Register  to view this content.

This would give result

\	A	B	C
1		0	0
2	0	0	0
3	3		3
4	0	1	-1
5	-2	1	-3
6

This allows negative integers in A/B as well

[newdoverman]

Confusion reigns. Here is another way to do what I think the OP wants.

	A	B	C
1	1	1	0
2		1	-1
3	0
4	0	0
5	-2	1	-3
6		1	-1
7	1		1
8	-4	7	-11
9	12	9	3
10		0
11	=IF(OR(A1<>0,B1<>0),A1-B1,"")

[ELeGault]

HAHAHA - I like it - OP, please weigh in and let us know if you have what you need... I think there are several solutions and one is bound to do the trick for you

[AmirSoft]

Good Reply. Helpful

[Baldev Kumar]

Go to "excel option">>> Click on "Advanced", Untick the option "Show a zero in cell that have zero value"

[neokragu]

=if(count(A1:B1)=0,"",A1-B1)

Try this

Thank you very much. This is exactly what I want. Tested, working as expected.

Thank you all for those gave your suggestions.