To leave the cell Blank

S

Steved

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.
 
B

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
 
R

Roy

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
 
S

Steved

I thankyou Both.

Roy said:
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
 
N

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

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*.
 
N

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
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top