How to make a value of a cell invisible

[mario]

Hi,

I have formula as below:

C1 = SUM(A1,B1) (= 21 as example)

======================

C2 =(SUM(C1,A2)-B2) (= 18)

C3 =(SUM(C2,A3)-B3) (= 27)...(value of C3 depends on value of C2)

and copy the formuala of C2, or C3 down of column C to C20


10 11 21 (C1)

2 5 18 (C2)

9 27 (C3)

27 (C4)

27 (C5)


How do I hide the value of C4 when no data entered in A4 and B4, or C5 when no data entered in A5, B5? I don't want to see 27 visible on C4, and C5.


I'm new to Excel. Any help is very much appreciated.


Thanks

Mario

 

[Debraj]

Hi Mario,

Warm Welcome to the forum..


In C2 write formula as

=IF(AND(A2<>"",B2<>""),SUM(C1,A2)-B2,"")

then drag the same upto C20..

Now start putting values from A2 to B20..


Regards,

Deb

PS: There is a list of Super Mario in this Forum.. we called them NINJA..

 

[mario]

Thanks Deb,

I'm thrilled to see the fast reply.

Your formula works fine, but when you skip one ROW ( for example the row 3 with no data entered), then the value of the next C4 is incorrect because C3 was 0.


Please check and advise.


Best regards,


Mario

 

[Debraj]

Hi Marion,


In C2 apply formula

=$C1+($A$2:$A2)-($B$2:$B2)


Now drag the formula upto C20.

select c1 to c20.

Goto Home >> Conditional formatting >> New Rule >> Select Use Formula to determine which cells to Format.


In the "Format value where this formula is true,

Write

=IF(AND(A1="",B1=""),TRUE,FALSE)

and In the format section >> Select Number tab>> select Custom >> in the type Field write ;;;


and press OK >> again Apply/OK..


Please let me if you are facing any issue..

Regards,

Deb

 

[mario]

Hi Deb,


I tried, tried exactly what instructed from you, but the value of the C's cells still show up even though there no data entered on A's cells and B's cells.


Please check and help me again.


Regards

Mario

 

[NARAYANK991]

Hi Mario ,


Till such time Deb answers , can you try out this ?


=IF(OR(ISBLANK(C1),AND(ISBLANK(A2),ISBLANK(B2))),"",$B$1+SUM($A$1:A2)-SUM($B$2:B2))


Enter this in C2 , and copy downwards.


Narayan

 

[shrivallabha]

Write following formula in C2.


=IF(COUNT(A2,B2)=0,"",SUM($C$1,$A$2:$A2)-SUM($B$2:$B2))


Copy down.

 

[mario]

Thanks Masters Deb, Narayank991, and Shrivallabha.


It WORKS and I'm very happy.


The formula from Ninja Narayank just had a typo........should be $C$1 instead of $B$1. and that's it.

Today I learned a lot from 3 of you guys. Gracias, Mercy.....


Thanks a bunch.


Mario.