preventing EXCEL from returning zeros when copying formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello out there.

I work in mental health research and my department collects information from
patients by interviews and questionnaires. The paper and pencil
questionnaires are filled in by patients and then one of our staff members
enters the raw data into EXCEL 2002 (for this application I was in a hurry
otherwise I would have set it up in ACCESS) so that scores can be computed
and the data analyzed.

My task: to have EXCEL compute scores (say Depression scores) as the raw
data is entered.

What I did:
I opened a workbook, and set up two worksheets within.
The first worksheet is for raw data and the columns correspond to the items
on the questionnaire.
The second worksheet is where the scoring takes place. One formula
references ten columns from the raw data worksheet and asks for a sum.
Another formula references five other columns from the raw data worksheet and
asks for an average.
Once the formulas were completed and set up in a row , I then copied these
formulas down the worksheet to row 500 or so. I didn't know how many
patients were going to be interviewed but 500 seemed like a good guess.

The problem:
As I copy my formulas down the worksheet, zeros appear automatically because
of course EXCEL is calculating, as it should. The problem is that I don't
want EXCEL to calculate NULL cells--zeros have meaning in our questionnaires
and so an automatic zero result is throwing off our scoring because we think
it's from raw data but really it's from empty cells.

Wish:
I would like to prevent EXCEL from carrying out my formulas based on empty
cells and instead provide a score if it encounters a zero or other number.

Kindly help me.

Thanking you in advance for your consideration of this issue,
Myriam
 
You will have to use an if statement. try =IF(<your argument
here>=0,"",<your argument here), eg =IF(SUM(A1:G25)=0,"",SUM(A1:G25)). It
means IF your argument equates to 0, THEN ""(Empty), ELSE your argument
 
Thank you for your prompt response. I have tried that suggestion but the
problem is that zeros are valid questionnaire responses so I cannot equate
them with a null cell. Any other ideas, suggestions, different ways that I
should be approaching this problem?
 
ISBLANK(cellref) is TRUE if nothing is entered in the cell (but is FALSE if a
null string is in the cell.

ISNUMBER(cellref) is TRUE if the cell contains a number but false if it
contains anything else or if it contains nothing.

Use whichever of these is appropriate to your data in your IF statement.

Jerry
 
Jerry,

I am sorry to persist, but I must. I loved your suggestion and began to
test ISBLANK using just one cell reference and it worked fine but I've been
at it for some time now and I can't get a handle on the logic when it
involves more than one cell. The following is what is in my cells now, but I
don't know how to add ISBLANK for such a long sequence. Would you mind
giving me some pointers please?

This is the actual cell formula:
=SUM('Raw Data'!C2,'Raw Data'!K2,'Raw Data'!S2,'Raw Data'!AA2,'Raw
Data'!AJ2,'Raw Data'!AQ2,'Raw Data'!AY2,'Raw Data'!BC2)

Thanks,
Mimi
 
=SUM( IF(ISBLANK('Raw Data'!C2),,'Raw Data'!C2), IF(ISBLANK('Raw
Data'!K2),,'Raw Data'!K2), ...)

or

=SUM( IF(ISNUMBER('Raw Data'!C2),'Raw Data'!C2), IF(ISNUMBER('Raw
Data'!K2),'Raw Data'!K2), ...)

If there are no numeric values in C2:BC2 other than the ones that you want
to sum, then you could simplify to =SUM(C2:BC2) since the SUM function
ignores blank and non-numeric cells.

Jerry
 
Jerry,

Thanks for responding. The minute I read your post I tried both ISNUMBER
and ISBLANK and although they calculate beautifully, I still get zeros on my
spreadsheet when I copy the formula to the rest of the worksheet. I think
the formula has to say something like: SUM the following cells only if a
number is present (ISNUMBER) otherwise leave it blank or null.

If you feel like getting into this, please scroll down for the original
description of the problem.

Thanks again for the attention you have given this--any other ideas? I hate
giving up.

Mimi
 
Kassie suggested the ususal solution for that.

=IF(formula=0,"",formula)

It may be possible to simplify the condition formula=0 to something like
COUNT(range)=0

Jerry
 
Back
Top