Generating truly blank cells
 

I am using an IF function to generate data for an array. Under certain
conditions a given cell should generate a totally blank cell, as opposed to a
zero value. I have been trying to use a formula such as:

=IF(A10A1,A10,"")

The problem relates to the use of "" in the creation of data arrays.

Once the array has been created I have copied all of the formula results and
converted them to values, i.e. I have eliminated the formula and now merely
have an array of data that was created using a formula. I have been trying to
use the Data Analysis tool to run a Descriptive Statistics summary for each
column of the array. However, when attempting to do so I receive an error
message that says non-numeric information is included in the array, i.e. the
cells that appear empty are being recognized as non-numeric information.
Those cells were originally created as "" cells when using the function. I
know that I could locate each "blank" cell and delete its contents, which
works, but is infeasible as my data array is rather large.

I am in need of a simple solution that either 1) creates truly blank cells
in the original function used to create the array or 2) allows me to convert
those "blank cells" to cells that are recognized as being truly empty by the
Descriptive Statistics tool.

Not sure, but you could try this .. After converting to values, do an
autofilter on the column, select: (blanks). Then just select the filtered
"blank" range and do a right-click Clear contents.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Nat" wrote in message
...
I am using an IF function to generate data for an array. Under certain
conditions a given cell should generate a totally blank cell, as opposed
to a
zero value. I have been trying to use a formula such as:

=IF(A10A1,A10,"")

The problem relates to the use of "" in the creation of data arrays.

Once the array has been created I have copied all of the formula results
and
converted them to values, i.e. I have eliminated the formula and now
merely
have an array of data that was created using a formula. I have been trying
to
use the Data Analysis tool to run a Descriptive Statistics summary for
each
column of the array. However, when attempting to do so I receive an error
message that says non-numeric information is included in the array, i.e.
the
cells that appear empty are being recognized as non-numeric information.
Those cells were originally created as "" cells when using the function. I
know that I could locate each "blank" cell and delete its contents, which
works, but is infeasible as my data array is rather large.

I am in need of a simple solution that either 1) creates truly blank cells
in the original function used to create the array or 2) allows me to
convert
those "blank cells" to cells that are recognized as being truly empty by
the
Descriptive Statistics tool.

Change your formula so that it returns a space instead of a blank. After
you've converted the formulas to values, search & replace, substituting
nothing for spaces, but be sure to tell the search to look at entire cell

"Nat" wrote:

I am using an IF function to generate data for an array. Under certain
conditions a given cell should generate a totally blank cell, as opposed to a
zero value. I have been trying to use a formula such as:

=IF(A10A1,A10,"")

The problem relates to the use of "" in the creation of data arrays.

Once the array has been created I have copied all of the formula results and
converted them to values, i.e. I have eliminated the formula and now merely
have an array of data that was created using a formula. I have been trying to
use the Data Analysis tool to run a Descriptive Statistics summary for each
column of the array. However, when attempting to do so I receive an error
message that says non-numeric information is included in the array, i.e. the
cells that appear empty are being recognized as non-numeric information.
Those cells were originally created as "" cells when using the function. I
know that I could locate each "blank" cell and delete its contents, which
works, but is infeasible as my data array is rather large.

I am in need of a simple solution that either 1) creates truly blank cells
in the original function used to create the array or 2) allows me to convert
those "blank cells" to cells that are recognized as being truly empty by the
Descriptive Statistics tool.

Nat wrote...
....
The problem relates to the use of "" in the creation of data arrays.
....
I am in need of a simple solution that either 1) creates truly blank cells
in the original function used to create the array or 2) allows me to convert
those "blank cells" to cells that are recognized as being truly empty by the
Descriptive Statistics tool.

There's no direct solution because BLANK corresponds the the same value
as VBA's Empty, but there's no way to produce it in formulas. So your
option 1 can't be done. As for 2, the only thing you could do is clear
the nonnumeric cells. If you're using Edit Paste Special, Values to
produce your data ranges, then just after pasting press [F5], click on
the Special... button, select Constants and uncheck the Numbers check
box, and click OK. At this point, only the cells with nonnumeric values
would remain selected. Just press [Delete] to clear them.

I use a variation of your recommendation:

=IF(A10A1,A10,na())
Then drag down???


Then I select the range and use Edit|goto|special and clean up the formulas with
errors (or values with errors if I converted to values).



Duke Carey wrote:

Change your formula so that it returns a space instead of a blank. After
you've converted the formulas to values, search & replace, substituting
nothing for spaces, but be sure to tell the search to look at entire cell

"Nat" wrote:

I am using an IF function to generate data for an array. Under certain
conditions a given cell should generate a totally blank cell, as opposed to a
zero value. I have been trying to use a formula such as:

=IF(A10A1,A10,"")

The problem relates to the use of "" in the creation of data arrays.

Once the array has been created I have copied all of the formula results and
converted them to values, i.e. I have eliminated the formula and now merely
have an array of data that was created using a formula. I have been trying to
use the Data Analysis tool to run a Descriptive Statistics summary for each
column of the array. However, when attempting to do so I receive an error
message that says non-numeric information is included in the array, i.e. the
cells that appear empty are being recognized as non-numeric information.
Those cells were originally created as "" cells when using the function. I
know that I could locate each "blank" cell and delete its contents, which
works, but is infeasible as my data array is rather large.

I am in need of a simple solution that either 1) creates truly blank cells
in the original function used to create the array or 2) allows me to convert
those "blank cells" to cells that are recognized as being truly empty by the
Descriptive Statistics tool.

--

Dave Peterson