How to give a value to NA in Excel


M

Mike

Hi,
I'm trying to come up with a way in Excel to QA customer service phone
calls. I've created a simple spreadsheet to add up the scores for sections
of the sheet and have added a formula to convert them to a percentage. For
instance, the first section has three questions worth 2 points each for a
possible total of 6. The formula to convert it to a percentage is
=SUM(C8:C10)/6 This formula works fine when adding values of 0 and 2, but
depending on the type of call, not all questions are applicable, so I'd like
to add a value of NA. However Excel sees NA as a 0, thus skewing the
percentages. I'd like to give NA a value of 2. Sure, I can just enter the
full value, but would prefer to see NA in the cell. Is there any way that I
can do this? Any help or suggestions would be most appreciated.

Mike
 
Ad

Advertisements

B

BenjieLop

Mike said:
Hi,
I'm trying to come up with a way in Excel to QA customer service phone
calls. I've created a simple spreadsheet to add up the scores for
sections
of the sheet and have added a formula to convert them to a percentage.
For
instance, the first section has three questions worth 2 points each for
a
possible total of 6. The formula to convert it to a percentage is
=SUM(C8:C10)/6 This formula works fine when adding values of 0 and 2,
but
depending on the type of call, not all questions are applicable, so I'd
like
to add a value of NA. However Excel sees NA as a 0, thus skewing the
percentages. I'd like to give NA a value of 2. Sure, I can just enter
the
full value, but would prefer to see NA in the cell. Is there any way
that I
can do this? Any help or suggestions would be most appreciated.

Mike
A simple, no-frills solution will be to add a helper column. In Cell
D8, enter the formula

=IF(C8=\"NA\",2,C8)

and copy down until D10.

Your percentage formula will then be

=SUM(D8:D10)/6

NOTE: Since Column D is a helper column, you can hide this and the
above percentage formula can be entered in, say, Cell C11.

Like I said, this is a simple, no-frills solution but it will work.

Regards.
 
M

Mike

Thanks BenjieLop! That did the trick.

Mike

BenjieLop said:
A simple, no-frills solution will be to add a helper column. In Cell
D8, enter the formula

=IF(C8=\"NA\",2,C8)

and copy down until D10.

Your percentage formula will then be

=SUM(D8:D10)/6

NOTE: Since Column D is a helper column, you can hide this and the
above percentage formula can be entered in, say, Cell C11.

Like I said, this is a simple, no-frills solution but it will work.

Regards.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top