Sum or leave blank

[Charles_]

Hi all,

I am trying to work out a formula that will do a calculation OR don't and leave blank if there is a zero or a blank cell.

Thanks in advance.

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C (expected result of formula)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]=A1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]18[/TD]
[TD]IF in A2 there is a number that it is not '0' or blank, leave this cell blank. Otherwise =A1+A2[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12[/TD]
[TD]IF in A2 there is a number that it is not '0' or blank, leave this cell blank. Otherwise =A1+A2+A3[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[TD]IF in A2 there is a number that it is not '0' or blank, leave this cell blank. Otherwise =A1+A2+A3+A4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]IF in A2 there is a number that it is not '0' or blank, leave this cell blank. Otherwise =A1+A2+A3+A4+A5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Charles_]

What if we would have the following?

A1 = 10
A2 = 18
A3 = 0
A4 = 8
A5 empty

Sorry I missed this, I am not sure I understand your question though?

It is possible to have a 0 followed by a number like you have above, the formula provided by Fluff also works in this case.

 

[Aladin Akyurek]

Sorry I missed this, I am not sure I understand your question though?

It is possible to have a 0 followed by a number like you have above, the formula provided by Fluff also works in this case.

I was asking for the numbers you want to see in B1:B5.

 

[dfsobral]

Thank you but I can't use a macro in this case, I wonder if I can point the graph to a different range and have a formula there to paste values only, is that possible?

this should work but is not automatic

place this formula in C1 and copy down

=IF(A1<>0, "#", sum($A$1:A1))

then select the entire column copy and paste values (in the same location)

after replace # for nothing (you can use any other character

the formulas will be gone but the graph should be ok

the problem is that when you update values you will need to repeat the entire process

 

[Charles_]

this should work but is not automatic

place this formula in C1 and copy down

=IF(A1<>0, "#", sum($A$1:A1))

then select the entire column copy and paste values (in the same location)

after replace # for nothing (you can use any other character

the formulas will be gone but the graph should be ok

the problem is that when you update values you will need to repeat the entire process

Thanks for this, I appreciate it can't be done without a Macro so will have to live with the graph dipping down to 0 as I rather that than repeating the process every single time, thanks!