How to give a value to NA in Excel

[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

 

[BenjieLop]

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(D810)/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.

 

[Mike]

Thanks BenjieLop! That did the trick.

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(D810)/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.

 

[BenjieLop]

You're welcome ... thanks for the feedback.

Regards.