Cond. Formatting; If Cell B6 or B7 is blank I want Cell D12 to be

[Dr. Darrell]

I have a formula in Cell D12:

=SQRT(POWER($B$6,2)+POWER(B$7,2))

If in have no entry in Cell B6 or B7, I would like cell D12 to be blank.

I think it should be done with Conditional Formatting, but how do I identify
or recognize a blank (no entry) cell?

 

[Mike H]

Try,

=IF(COUNT(B6:B7)<2,"",SQRT(POWER($B$6,2)+POWER(B$7,2)))

Mike

 

[Mike H]

Hi,

If you want to do it with conditional formatting then you can by setting the
font colour to white (same as background) if either cell is empty. The zero
is still returned but you won't see it.

Use this conditional format formula in your formula cell.

=COUNT(B6,B7)<2


Mike

 

[Excel_Learner]

Type in D12:
=if(and(b6<>"", b7<>""), SQRT(POWER($B$6,2)+POWER(B$7,2)), "").

 

[Dana DeLouis]

=SQRT(POWER($B$6,2)+POWER(B$7,2))

Just to mention a slightly shorter version...

=SQRT(SUMSQ($B$6,$B$7))

- - -
HTH
Dana DeLouis

 

[Shane Devenshire]

Hi,

Answering the original question:

1. =IF(AND(B6="",B7=""),"",SQRT(POWER($B$6,2)+POWER(B$7,2)))
or
2. =IF(AND(B6="",B7=""),"",SQRT(SUMSQ(B6:B7)))
or, array entered (press Ctrl+Shift+Enter)
3. =IF(AND(B6:B7=""),"",SQRT(SUMSQ(B6:B7)))

It can be done with conditional formatting
To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format
2. Choose Format, Conditional Formatting
3. Choose Equal is from the second drop down
4. In the third box enter 0
5. Click the Format button
6. Choose the white Color on the Font tab
7. Click OK twice.

In 2007 provides a slightly better option:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Format only cells that contain
4. In the Format only cells with, choose the Equal from the second drop down
5. Enter 0 in the third box
5. Click the Format button and choose Number tab, Custom and enter ;;;
(three semi-colons) in the Type box.
6. Click OK twice

If this helps, please click the Yes button.

Cheers,
Shane Devenshire