ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   make cell show blank when total is zero (https://www.excelbanter.com/excel-discussion-misc-queries/175756-re-make-cell-show-blank-when-total-zero.html)

Lindy

make cell show blank when total is zero
 
I searched and found this thread. I'm using Excel 2003.

I used Frank's advice of custom cell format to make cell show blank when
total is zero, eg: 0.0;-0.0;

but it only returns me one place of decimal rounded up to nearest 10.
Result: 123 * 11.05 = 1359.2

I changed the custom cell format to 0.00;-0.00;
Result: 123 * 11.05 = 1359.15

What I would like returned is 1,359.15

Can you help me with a custom cell format returning 1,359.15 where it will
make the cell show blank when total is zero?

Lindy

"Frank Kabel" wrote:

Hi
goto 'Format - Cells'. Choose custom and enter this format in the
textbox on the right side

--
Regards
Frank Kabel
Frankfurt, Germany

"jtinne" schrieb im Newsbeitrag
...
How would I enter that in the formula?

"Frank Kabel" wrote:

Hi
you could use a custom format such as
0.0;-0.0;

--
Regards
Frank Kabel
Frankfurt, Germany

"jtinne" schrieb im Newsbeitrag
...
Here is my formula:
=COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Daily
MP'!$5:$5,0)-1),"*" &
$B8 & "*")
When the total is zero, how do I show the cell as blank instead

of
having a
0 show in the cell?





Sandy Mann

make cell show blank when total is zero
 
Try a custom format of:

#,##0.00;-#,##0.00;

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lindy" wrote in message
...
I searched and found this thread. I'm using Excel 2003.

I used Frank's advice of custom cell format to make cell show blank when
total is zero, eg: 0.0;-0.0;

but it only returns me one place of decimal rounded up to nearest 10.
Result: 123 * 11.05 = 1359.2

I changed the custom cell format to 0.00;-0.00;
Result: 123 * 11.05 = 1359.15

What I would like returned is 1,359.15

Can you help me with a custom cell format returning 1,359.15 where it will
make the cell show blank when total is zero?

Lindy

"Frank Kabel" wrote:

Hi
goto 'Format - Cells'. Choose custom and enter this format in the
textbox on the right side

--
Regards
Frank Kabel
Frankfurt, Germany

"jtinne" schrieb im Newsbeitrag
...
How would I enter that in the formula?

"Frank Kabel" wrote:

Hi
you could use a custom format such as
0.0;-0.0;

--
Regards
Frank Kabel
Frankfurt, Germany

"jtinne" schrieb im Newsbeitrag
...
Here is my formula:
=COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Daily
MP'!$5:$5,0)-1),"*" &
$B8 & "*")
When the total is zero, how do I show the cell as blank instead

of
having a
0 show in the cell?








Lindy

make cell show blank when total is zero
 
Thank you so much. Just what I wanted.
Lindy

"Sandy Mann" wrote:

Try a custom format of:

#,##0.00;-#,##0.00;

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lindy" wrote in message
...
I searched and found this thread. I'm using Excel 2003.

I used Frank's advice of custom cell format to make cell show blank when
total is zero, eg: 0.0;-0.0;

but it only returns me one place of decimal rounded up to nearest 10.
Result: 123 * 11.05 = 1359.2

I changed the custom cell format to 0.00;-0.00;
Result: 123 * 11.05 = 1359.15

What I would like returned is 1,359.15

Can you help me with a custom cell format returning 1,359.15 where it will
make the cell show blank when total is zero?

Lindy

"Frank Kabel" wrote:

Hi
goto 'Format - Cells'. Choose custom and enter this format in the
textbox on the right side

--
Regards
Frank Kabel
Frankfurt, Germany

"jtinne" schrieb im Newsbeitrag
...
How would I enter that in the formula?

"Frank Kabel" wrote:

Hi
you could use a custom format such as
0.0;-0.0;

--
Regards
Frank Kabel
Frankfurt, Germany

"jtinne" schrieb im Newsbeitrag
...
Here is my formula:
=COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Daily
MP'!$5:$5,0)-1),"*" &
$B8 & "*")
When the total is zero, how do I show the cell as blank instead
of
having a
0 show in the cell?









Sandy Mann

make cell show blank when total is zero
 
Glad that it worked for you. Thanks fot the feedback.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lindy" wrote in message
...
Thank you so much. Just what I wanted.
Lindy

"Sandy Mann" wrote:

Try a custom format of:

#,##0.00;-#,##0.00;

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lindy" wrote in message
...
I searched and found this thread. I'm using Excel 2003.

I used Frank's advice of custom cell format to make cell show blank
when
total is zero, eg: 0.0;-0.0;

but it only returns me one place of decimal rounded up to nearest 10.
Result: 123 * 11.05 = 1359.2

I changed the custom cell format to 0.00;-0.00;
Result: 123 * 11.05 = 1359.15

What I would like returned is 1,359.15

Can you help me with a custom cell format returning 1,359.15 where it
will
make the cell show blank when total is zero?

Lindy

"Frank Kabel" wrote:

Hi
goto 'Format - Cells'. Choose custom and enter this format in the
textbox on the right side

--
Regards
Frank Kabel
Frankfurt, Germany

"jtinne" schrieb im Newsbeitrag
...
How would I enter that in the formula?

"Frank Kabel" wrote:

Hi
you could use a custom format such as
0.0;-0.0;

--
Regards
Frank Kabel
Frankfurt, Germany

"jtinne" schrieb im Newsbeitrag
...
Here is my formula:
=COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Daily
MP'!$5:$5,0)-1),"*" &
$B8 & "*")
When the total is zero, how do I show the cell as blank instead
of
having a
0 show in the cell?













All times are GMT +1. The time now is 10:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com