blank cells v. zeros

[JessNY]

Hi-
I have a database full of different tables on each worksheet. On eac
table, I input test scores and then run my macro. The test scores go t
the last worksheet labeled 'data'. My problem is when I leave a tes
blank on the table (when there is no score for that specific test),
zero appears under that test in 'data'. I tried turning off zero value
but that got rid of every zero, even the ones that actually represent
test score. To make a long story short, I need blank cells to sta
blank and cells with zeros to stay zero. If anyone can help me, i
would be much appreciated!
-Jessic

 

[Jerry W. Lewis]

A formula must return something (Excel has no missing or blank value),
so you would either have to do something like
=IF(ISBLANK(cellref),retval,cellref)
or delete the formula altogether until the cellref contains someting
(you could automate this process in an event driven macro). For
retvalue, you choices are "" which will look blank and be ignored by
COUNT(), SUM(), AVERAGE(), etc. but will plot as a zero or #N/A, or
equivalently NA(), which will propagate through subsquent formulas
unless you use ISNA() in them, but #N/A will not plot.

Jerry