How to can return an empty cell

[SHood]

Version: v.X
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

using the IF function.

 

[Mike Middleton]

SHood -

If by "empty" you mean the same as that obtained with Delete or Clear
Contents, then it cannot be done.

An Excel worksheet formula or function returns a value only to the cell it
occupies.

If you mean "blank," then use two double-apostrophes, i.e., two quote marks
with nothing between them.

For example, =IF(B2=C2,"","not equal") returns blank when B2 and C2 are
equal.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel





Re: How to can return an empty cell

 

[SHood]

Yes, I meant empty as with the delete key.

I need to get a total using the CountA function that does not count the "Blank" cells.

Is there a way to use the "IF" function with the "CountA" to exclude the Blanks returned by my original "IF" function.

Thanks

 

[Carl Witthoft]

Yes, I meant empty as with the delete key.

I need to get a total using the CountA function that does not count the
"Blank" cells.

Is there a way to use the "IF" function with the "CountA" to exclude the
Blanks returned by my original "IF" function.

Thanks

You need to give us an example, as it's not clear what you mean by
"Blanks returned by my original IF" . An IF function can only put some
value into its own cell. I thought COUNTA will ignore cells whose value
is "", which an IF could write to its cell.

But to get pure blanks, you'll need to write a macro which executes the
CLEAR command on designated cells. All in all, it's ugly and basically
a hangup in Excel. You'd think there'd be a way to treat a no-content
cell the same as a 'null,' or blank cell, but no. Take a look at the
mess you get when graphing blanks vs "" or 0 cells.

 

[SHood]

OK, I'm playing around with an NCAA Tournament sheet.

My picks are listed first, the actual winners are listed next, and a bunch of IF functions compare the two.

The IF will return the winning teams name in the next round if I picked them and WANTED to return a blank, (delete type), if I was wrong so that the CountA function could give my total correct for each round.

Since I cannot return a blank is there some way I could return say "Wrong" and have the sheet count anything that is not equal to "Wrong" ?

Thanks

 

[Mike Middleton]

SHood -

Since I cannot return a blank is there some way I could return say "Wrong"
and have the sheet count anything that is not equal to "Wrong" ? <

For example, if picks are in A1:A8 and corresponding winners are in A9:A16,
then A17 could contain
=IF(A1=A9,"OK","Wrong"), which you could copy down to A24.

And, then, if the "Wrong" text values appear in A17:A24, for example, in
some other cell you could use

=COUNTIF(A17:A24,"<>Wrong")

or, =COUNTIF(A17:A24,"=OK")

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[SHood]

COUNTIF worked great.

Was exactly what I was looking for.

I'll work on the checking of these sheets and probably be back with a thousand questions about forms I'd like to use to have them filled in.

Thanks