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.

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 ToolsOptionsView

Uncheck: Zero values

3: Use conditional formatting

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

Biff

"Steved" wrote in message
...
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.

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(('Plann ed
Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned
Kilometres'!$AC$4:$AF$109))

Roy
"Steved" wrote:

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.

I thankyou Both.

"Roy" wrote:

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(('Plann ed
Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned
Kilometres'!$AC$4:$AF$109))

Roy
"Steved" wrote:

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.

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

Biff

"Steved" wrote in message
...
I thankyou Both.

"Roy" wrote:

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(('Plann ed
Kilometres'!$A$4:$A$109=1)*('Planned
Kilometres'!$B$4:$B$109="P")*'Planned
Kilometres'!$AC$4:$AF$109))

Roy
"Steved" wrote:

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.

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

"Biff" wrote:

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 ToolsOptionsView

Uncheck: Zero values

3: Use conditional formatting

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

Biff

"Steved" wrote in message
...
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.

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

--
Biff
Microsoft Excel MVP


"Neon520" wrote in message
...
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

"Biff" wrote:

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 ToolsOptionsView

Uncheck: Zero values

3: Use conditional formatting

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

Biff

"Steved" wrote in message
...
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.

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Neon520" wrote in message
...
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

"Biff" wrote:

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 ToolsOptionsView

Uncheck: Zero values

3: Use conditional formatting

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

Biff

"Steved" wrote in message
...
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.

On Apr 1, 5:18 pm, Neon520 wrote:
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

"T. Valko" wrote:
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*.

--
Biff
Microsoft Excel MVP

"Neon520" wrote in message
...
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

"Biff" wrote:

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 ToolsOptionsView

Uncheck: Zero values

3: Use conditional formatting

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

Biff

"Steved" wrote in message
...
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.

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