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(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.

[Mike]

Thanks BenjieLop! That did the trick.

Mike

"BenjieLop" <[email protected]> wrote in
message news:[email protected]...
>
> Mike Wrote:
>> 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.
>
>
> --
> BenjieLop
>
>
> ------------------------------------------------------------------------
> BenjieLop's Profile:
> http://www.excelforum.com/member.php...o&userid=11019
> View this thread: http://www.excelforum.com/showthread...hreadid=488383
>

[BenjieLop]

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

Regards.