Leaving Cells of Currency Format Blank

  • Thread starter Thread starter GBL
  • Start date Start date
G

GBL

Hi:
I have an H-column of calculated values (formatted as currency) based on
a formula of: IF(A13=2,H12-G13,H12+G13). Problem is: I do not wish these
calculated cells to be filled with zero ($ 0) values when the A-column is
not filled in. Is there any way to leave the calculated cells blank when
the A-column is blank?

Thank you in advance!!
 
You could try one of 3 things,

use the formula
=IF(A13="","",IF(A13=2,H12-G13,H12+G13))

Suppress zero value, menu Tools>Options>View and check the Zero Values
checkbox

Use conditional formatting to set the font colour to white if the value is
zero.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Adding to Bob's comments,

A fourth is to put a custom format that suppresses zeroes.

Format | Cells | Number | Custom

An example is as follows:

a) does not suppress zeroes.

#,##0.00_);[Red](#,##0.00)

0 number looks like
0.00

b) suppresses zeroes

#,##0.00_);[Red](#,##0.00); (note the semicolon at the end)
0 number looks like

[It is blank, you can't see it]


Regards,
Kevin
 
Hi Bob:
Great idea Bob!! I chose to alter the formula to your revision. Works
great!! I knew there was a way but could not wring it out. Thanks!!
 
Very true. I'll add that to my list <vbg>

Bob

Kevin Stecyk said:
Adding to Bob's comments,

A fourth is to put a custom format that suppresses zeroes.

Format | Cells | Number | Custom

An example is as follows:

a) does not suppress zeroes.

#,##0.00_);[Red](#,##0.00)

0 number looks like
0.00

b) suppresses zeroes

#,##0.00_);[Red](#,##0.00); (note the semicolon at the end)
0 number looks like

[It is blank, you can't see it]


Regards,
Kevin



Bob Phillips said:
You could try one of 3 things,

use the formula
=IF(A13="","",IF(A13=2,H12-G13,H12+G13))

Suppress zero value, menu Tools>Options>View and check the Zero Values
checkbox

Use conditional formatting to set the font colour to white if the value is
zero.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

based A-column
is
Click to expand...
Click to expand...
 
Back
Top