Generating blank or null cells that the Histogram Data Analysis tool will ignore

[EddyKilowatt]

Hi Folks -

This is a variation on "returning a blank cell from a function", which
I understand you can't do.

I'm analyzing several thousand calculated cells using the Histogram
Tool from the Analysis pack. I need to exclude several hundred of
those cells from the histogram since there were input data dropouts at
those points.

I can test for dropouts by doing IF(ISBLANK() on the input data
cells. I would like to return *something* from the IF test that the
Histogram Tool will ignore... but unfortunately the Tool is quite
fussy about numeric data. I know that the Tool will ignore Blank
Cells, but there is no way to return a Blank Cell as a result of an IF
test.

Does anyone know of any value that I can return from the IF test, that
the Histogram Tool will ignore when it is doing its analysis?
"Ignore" as in skip over and not include in the resulting output
bins...

thanks,
Ed

 

[bj]

what I have done in this situation is o return a value 10 times higher than I
would expect in the distribution
then set up my output bins and format the value equat to my high number with
a ;;; format so that it is hidden

I have also selected the data set, used autofilter to hide the return value
or NA#
selected the data and edit-goto-special-visible cells only
then insert-name define
use the name in the histogram function or the frequency formula to generate
your histogram.
neither of these are as nice as it would be if Excell would ignore a "" or
#NA but are not too onerous

 

[EddyKilowatt]

what I have done in this situation is o return a value 10 times higher than I
would expect in the distribution
then set up my output bins and format the value equat to my high number with
a ;;; format so that it is hidden

I have also selected the data set, used autofilter to hide the return value
or NA#
selected the data and edit-goto-special-visible cells only
then insert-name define
use the name in the histogram function or the frequency formula to generate
your histogram.
neither of these are as nice as it would be if Excell would ignore a "" or
#NA but are not too onerous

Thanks bj. Since I only needed about 20 output bins, the work-around
I settled on was to use multiple COUNTIF cells, each with a different
less-than test, to directly generate my cumulative distribution. I've
saved your suggestions and if I ever need a hundred output bins (any
number too large to enter manually), I'll give them a try.

regards,
Eddy