To leave the cell Blank

[Guest]

Hello from Steved

If the cell has an 0 as a value what is required please to make it blank.

=SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned
Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109).

Thankyou.

 

[Biff]

Hi

3 choices:

1: Wrap the formula inside an IF function:

=IF(formula=0,"",formula)

That will make the formula twice as long.

2: Goto Tools>Options>View

Uncheck: Zero values

3: Use conditional formatting

Select the cell
Goto Format>Conditional Formatting
Cell value is equal to 0
Set the font color to be the same as the fill color.

Biff

 

[Guest]

I usually do it with an IF test like this...

=IF(SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned
Kilometres'!$B$4:$B$109="P")*'Planned
Kilometres'!$AC$4:$AF$109)=0,"",SUMPRODUCT(('Planned
Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned
Kilometres'!$AC$4:$AF$109))

Roy

 

[Guest]

I thankyou Both.

I usually do it with an IF test like this...

=IF(SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned
Kilometres'!$B$4:$B$109="P")*'Planned
Kilometres'!$AC$4:$AF$109)=0,"",SUMPRODUCT(('Planned
Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned
Kilometres'!$AC$4:$AF$109))

Roy

 

[Biff]

Someday people will learn that long sheet names really do suck! <vbg>

Biff

 

[Neon520]

Hi Biff,

I'm working on a similar situation that Steved asked you.
The problem that I have is that if I use =IF(formula=0,"",formula), the cell
the left blank after this formula is regarded as text (because of the ""),
but I'd like to pick up that value for calculation the next row, and it will
show as #VALUE! error. How can I avoid this?

I'm working on something similar to a logging balance sheet that I don't
want to show the same value for the rest of the sheet if the log doesn't
reach a certain line yet.

Thank you,
Neon

 

[T. Valko]

You can do this:

=IF(formula=0,0,formula)

However, it sounds like you would want the cells to be blank but since we
don't know exactly what you're doing I can't suggest anything more specific.

Post back with more *specific details*.

 

[Neon520]

Okay, what I'm doing is similar to a mileage log. A column named "Balance"
is at the far right of the sheet and that's where the running balance is at.
A little bit different from the Mileage log that is always positive entry
(adding up to the balance), my logging sheet will deduct of plus the value of
the entry to the previous line balance accordingly.

What I'd like is to show the Balance only up to the point where the last
entry is, not all the way to the end of the sheet. I'd like to leave the rest
of the Balance where there are no any entry BLANK, regardless of the balance
is positive negative or even zero.

Thank you,
Neon

 

[Spiky]

Okay, what I'm doing is similar to a mileage log. A column named "Balance"
is at the far right of the sheet and that's where the running balance is at.
A little bit different from the Mileage log that is always positive entry
(adding up to the balance), my logging sheet will deduct of plus the value of
the entry to the previous line balance accordingly.

What I'd like is to show the Balance only up to the point where the last
entry is, not all the way to the end of the sheet. I'd like to leave the rest
of the Balance where there are no any entry BLANK, regardless of the balance
is positive negative or even zero.

Thank you,
Neon

Instead of =IF(formula=0,"",formula)...try
=IF('dataentrycell'=0,"",formula). Assuming the data entry area is
left blank or as a 0.