How to define "empty cell" in a formula?

  • Thread starter Thread starter Ari Järmälä
  • Start date Start date
A

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ä
 
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
 
unfortunately you can't. There is no function that return a real empty
cell.
Click to expand...
...

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.
 
Harlan said:
...
..

Not technically correct. Consider the UDF
Click to expand...

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.
Click to expand...

So no help either (unfortunately)
Frank
 
To be precise: ...no build-in worksheet function...
Click to expand...
...

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)
Click to expand...

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

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?
Click to expand...

O.K. Touche. Never discuss with a mathematician (I assume you're one)
about precision. You'll always loose ;-)
Regards
Frank
 
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
 
More to the point, is there any technical reason why the designers of Excel
don't give us control over the blankness of cells?
Click to expand...
...

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.
 
Back
Top