How to define "empty cell" in a formula?

[Ari Järmälä]

I have Excel 2002. How can you write a formula, that gives a value "no
value, empty cell"? I mean a function, which is neither a zero (0.00)
nor missing data (=NA()).

See, I have a time series. I want to to plot a graph with a moving
average line. So there must not be zeros representing the missing
data.

Secondly, I also want to calculate a moving standard deviation on the
time series. So there must not be any =NA()'s, because you can't
calculate st.dev over a =NA().

These are no good:

=if(something;c1-b1;0) [zero is not the same as empty cell]
=if(something;c1-b1;"") ["" interprets as zero in graphs]
=if(something;c1-b1;NA()) [not allowed in st.dev function]

Clearing the cells with missing data is not an option, because the
time series is huge. How can you define in a formula, that the cell is
empty?

Rgs, Ari Järmälä

 

[Frank Kabel]

Hi
unfortunately you can't. There is no function that return a real empty
cell.
One workaround would be to create tow columns.:
- one with #NA values for charting
- one with "" for calculations
or you may adapt your calculation formulas to check for these kind of
values

Frank

 

[Harlan Grove]

unfortunately you can't. There is no function that return a real empty
cell.

...

Not technically correct. Consider the UDF

Function foo() As Variant: End Function

=ISBLANK(foo()) returns TRUE, ="a"&foo()&"b" returns "ab", and =1+foo()+2
returns 3. It's not that functions can't return blank. Rather, it's that Excel
will *ALWAYS* convert VBA's Empty variant value into either "" or zero,
depending on context, as the Value property of a cell. Only when the cell's
Formula and PrefixCharacter properties are both empty ("") will the cell's Value
property be Empty, thus the cell be treated as BLANK.

 

[Frank Kabel]

...
..

Not technically correct. Consider the UDF

To be precise: ...no build-in worksheet function...

Function foo() As Variant: End Function

=ISBLANK(foo()) returns TRUE, ="a"&foo()&"b" returns "ab", and
=1+foo()+2 returns 3. It's not that functions can't return blank.
Rather, it's that Excel will *ALWAYS* convert VBA's Empty variant
value into either "" or zero, depending on context, as the Value
property of a cell. Only when the cell's Formula and PrefixCharacter
properties are both empty ("") will the cell's Value property be
Empty, thus the cell be treated as BLANK.

So no help either (unfortunately)
Frank

 

[Harlan Grove]

To be precise: ...no build-in worksheet function...

...

To be precise, not a *single* built-in formula. However, if, say, cell IV65536
were blank (a common situation), then what does

=ISBLANK(INDIRECT("IV65536"))

return?

So no help either (unfortunately)

My response wasn't intended to help, only to point out inaccuracies in yours.

 

[Frank Kabel]

..

To be precise, not a *single* built-in formula. However, if, say,
cell IV65536 were blank (a common situation), then what does
=ISBLANK(INDIRECT("IV65536"))
return?

O.K. Touche. Never discuss with a mathematician (I assume you're one)
about precision. You'll always loose ;-)
Regards
Frank

 

[Chris R. Lee]

More to the point, is there any technical reason why the designers of Excel
don't give us control over the blankness of cells?

Regards

 

[Harlan Grove]

More to the point, is there any technical reason why the designers of Excel
don't give us control over the blankness of cells?

...

No spreadsheet does. 'Blankness' in spreadsheets is a state rather than a value.

Is there any reason this functionality hasn't been added? Microsoft isn't about
to add substantive features to Excel as long as the masses are willing to pay
+US$200 for upgrades with very little (if any) new spreadsheet calculation
functionality. None of the others (with the possible exception of gnumeric, but
unlikely soon) is likely to provide it until Excel does. So the simple answer is
that Microsoft doesn't need to to maintain its revenue/profit stream, and
Microsoft does squat altruistically.

If you want/need missing value support, you shouldn't be using spreadsheets. Use
a stats package.