IF STATEMENT REFERENCING A BLANK CELL

[Maureen]

I'm trying obtain a blank cell with an IF statement based
on the formula B6-SUM(B9;B11;B13;B15;B17):
1. if B6 contains a reference which returns a blank if the
referenced cell is blank
and
2. if B6-SUM(B9;B11;B13;B15;B17) returns a 0.

I've resolved item 2 this way:
=IF(D6-SUM(D9;D11;D13;D15;D17)=0;"";D6-SUM
(D9;D11;D13;D15;D17))
but everything I've tried for item 1 returns an error
message.

If anyone can help, thanks.

 

[Frank Kabel]

Hi
try
=IF(OR(D6-SUM(D9;D11;D13;D15;D17)=0;D6="");"";D6-SUM(D9;D11;D13;D15;D17
))

Frank

 

[Maureen]

This works when D6 has a value and returns a blank when D6
and the sum of the other cells are equal (normally
returning a 0), all of which I want.

But it continues to give me an error value when D6
contains a reference which returns a blank if the
referenced cell (located somewhere else in the workbook)
is blank.

Maureen

 

[Frank Kabel]

Hi Maureen
can you provide the formula in D6. Also what kind of error did you
receive

Frank

 

[Maureen]

Hi Frank,

D6 refers to the value in G63 of another worksheet and the
formula in G63 of that worksheet is =IF(SUM(G56:G62)
=0;"";SUM(G56:G62)).

The error I'm getting is #VALUE!

Maureen

 

[Frank Kabel]

Hi Maureen
so you have a #VALUE error in D6? (and also in G63 on the other
sheet)?. If yes, the best way would be to prevent this error in G63
(you may have text values in the are G56:G62)

A workaround could be the following formula in D6:
=IF(ISERROR(G63),"",G63)

Frank

 

[Maureen]

Frank,

Sorry if I've somehow muddled things.

D6 is blank because G63 is blank.

The #VALUE error is in D19 that contains the formula I
mentioned at the start:

D6-SUM(D9;D11;D13;D15;D17)

In D19, I'm trying to obtain a blank cell with an IF
statement based on this formula:

1. if D6 contains a reference which returns a blank if the
referenced cell is blank
and
2. if D6-SUM(D9;D11;D13;D15;D17) returns a 0.

I've solved Item 2 with the following formula:

=IF(D6-SUM(D9;D11;D13;D15;D17)=0;"";D6-SUM
(D9;D11;D13;D15;D17))

I just need to modify this formula in D19 to solve the
problem outlined in Item 1.

Maureen

 

[Frank Kabel]

Hi Maureen
see below

Frank,

Sorry if I've somehow muddled things.

D6 is blank because G63 is blank. o.k.


The #VALUE error is in D19 that contains the formula I
mentioned at the start:
D6-SUM(D9;D11;D13;D15;D17)

Is there a #VALUE error in either D6, D9,D11,D13,D15,D17 or are there
text values??. You should check this and solve this problem
A workaround would be using (though better solve the problem in the
used cells):
=IF(OR(D6="",ISERROR(D6-SUM(D9;D11;D13;D15;D17)),D6-SUM(D9;D11;D13;D15;
D17))=0);"";D6-SUM(D9;D11;D13;D15;D17))

Frank

 

[dcronje]

Have you tried:

=IF(ISERROR(D6-SUM(D9,D11,D13,D15,D17)=0),"",D6-SUM(D9,D11,D13,D15,D17))

Davi

 

[Maureen]

The #VALUE error appears only in D19. D6 references
another cell; and D9, D11, D13, D15 and D17 each contain a
formula returning a blank cell if the result is 0.

Maureen

 

[Frank Kabel]

Hi Maureen
I think the formula should prevent the error now. Just curious: If you
like you can send me your spreadsheet
(frank[dot]kabel[at]freenet[dot].de)
Frank

 

[dhager]

The previous tips are a great help and I tried to use the logic to mak
them work in my situation without success.

How do I reference a blank cell in a Conditional Formatting situation
such as "If cell is blank"color the cell red? Once any value i
entered, I want the cell to revert to its normal format.

Thank you,

Do

 

[Wendy L]

Give it a conditional format with the conditions set as (from first drop
down list) "Cell Value Is" (from second drop down list) "Equal to" and in
the blank white space type "0". Then select your format and format it how
you wish it to look if the cell value is blank. HTH

 

[dhager]

Wendy,

Thank you for that.

I realize some of this is probably very basic stuff. I don't have muc
Excel experience so I find this site very helpful.

Do

 

[angdani]

Hi Frank,

I'm having sort of the same problem as Maureen had last February.

My problem is:

On B12 the formula is =IF(IsBlank(A12),"","Yes")
On C12 the formula is =If(IsBlank(B12),"",compute)

C12 always reports an error whenever A12 is blank. I guess "" does no
evaluate to blank. Is there a way to assign "Blank" to a field instea
of ""? For some other reason, the formula in C12 cannot be changed
locked. So the only way is to change to formula in B12.

Cheers
Danie

 

[Debra Dalgleish]

A formula can't return a blank, so you'll have to unlock C12, and change
the formula.

But currently, C12 should always return the value in the compute range,
since B12 contains a formula. What error do you get?

 

[Myrna Larson]

A blank cell is one that has nothing in it. What you are asking is, can the
formula clear the cell. No, it can't -- the cell contains the formula. It
can't erase itself. If it did, the formula wouldn't be there any more.

Maybe you can change the formulas to

=IF(A12="","","Yes")
=IF(B12="","",computer)

Note: you MUST change the formula in C12. There's no way around that. You'll
have to unlock the sheet and do that.