How do I insert a "true blank" into a cell?

[Guest]

How do I insert a "true blank" into a cell as the result of a calculation or
IF() statement? "Help" says "Use NA to mark empty cells. " But #NA is NOT
the same as an empty cell:
1. If the cells are truly blank, both the function (AVERAGE()) and the plot
work like they should.
2. If the blank cells are used as input to an arithmetic operation, zeros
are inserted, resulting in zeros being plotted where you don't want them.
3. Inserting blanks with double quotes still works OK with the AVERAGE()
function, but the plot sees them as zeros, still unwanted.
4. Putting "NA()" in the null cells fixes the plot, but now the AVERAGE
function is fouled up.

 

[Guest]

I think what you are looking for is "". I use this in IF statements a lot.
Example:
A5=1
G5=2

=IF(A5>$G$2,A5,"")

This will return a blank cell.

I this helps.

 

[Bernie Deitrick]

Monique,

Keep using the NA()'s to fix the plot, but use an array function (entered
with Ctrl-Shift-Enter)

=AVERAGE(IF(NOT(ISERROR(A1:A10)),A1:A10))

to do your averaging. Update both range references to reflect the range that
you want to average.

HTH,
Bernie
MS Excel MVP

 

[Tushar Mehta]

Use two ranges: one for calculations, the other for charting.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Gord Dibben]

cwilson

Info only.......

A cell with "" is not blank, just looks like it.

Try =ISBLANK(cellref) on one of these and see what you get.

Gord Dibben Excel MVP