Prev: Printing Worksheet
Next: enhance a function of single character to a combination
From: lee-sc on 14 Mar 2010 23:35
Example :

E/no salary Bonus total
001 1000 #N/A
002 200 100
003 2000 #N/A

How to set a formula for each worker in total column (Salary & Bonus)when
the value are set as #N/A. How to convert #N/A to 0 for sum formula.

Please advise.

Thanks.
From: Corey on 14 Mar 2010 23:41
D2=IF(C2<>"#N/A",C2+B2,B2)
Assuming the Bonus values are in Column C and Salary amounts are in Column
B.
Copy/drag formula down the column D.
Corey...
"lee-sc" <leesc(a)discussions.microsoft.com> wrote in message
news:1ADC71CA-4C31-4C50-A201-4816CB925189(a)microsoft.com...
> Example :
>
> E/no salary Bonus total
> 001 1000 #N/A
> 002 200 100
> 003 2000 #N/A
>
> How to set a formula for each worker in total column (Salary & Bonus)when
> the value are set as #N/A. How to convert #N/A to 0 for sum formula.
>
> Please advise.
>
> Thanks.


From: Dennis Tucker on 14 Mar 2010 23:42
A Find & Replace?



"lee-sc" <leesc(a)discussions.microsoft.com> wrote in message
news:1ADC71CA-4C31-4C50-A201-4816CB925189(a)microsoft.com...
> Example :
>
> E/no salary Bonus total
> 001 1000 #N/A
> 002 200 100
> 003 2000 #N/A
>
> How to set a formula for each worker in total column (Salary & Bonus)when
> the value are set as #N/A. How to convert #N/A to 0 for sum formula.
>
> Please advise.
>
> Thanks.

From: Fred Smith on 15 Mar 2010 00:36
You could use:
=sum(b2:c2)

as sum will ignore text.

However if the #N/A you are displaying is the result of an Excel formula,
then Sum won't work. In this case, you are better off to avoid the #N/As in
the first place, typically done with:
=if(isna(yourformula),0,yourformula)

Regards,
Fred

"lee-sc" <leesc(a)discussions.microsoft.com> wrote in message
news:1ADC71CA-4C31-4C50-A201-4816CB925189(a)microsoft.com...
> Example :
>
> E/no salary Bonus total
> 001 1000 #N/A
> 002 200 100
> 003 2000 #N/A
>
> How to set a formula for each worker in total column (Salary & Bonus)when
> the value are set as #N/A. How to convert #N/A to 0 for sum formula.
>
> Please advise.
>
> Thanks.

From: Ashish Mathur on 15 Mar 2010 01:17
Hi,

Try this

=sumif(B2:C2,">=-9.999E307",B2:C2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"lee-sc" <leesc(a)discussions.microsoft.com> wrote in message
news:1ADC71CA-4C31-4C50-A201-4816CB925189(a)microsoft.com...
> Example :
>
> E/no salary Bonus total
> 001 1000 #N/A
> 002 200 100
> 003 2000 #N/A
>
> How to set a formula for each worker in total column (Salary & Bonus)when
> the value are set as #N/A. How to convert #N/A to 0 for sum formula.
>
> Please advise.
>
> Thanks.

 | 
Pages: 1
Prev: Printing Worksheet
Next: enhance a function of single character to a combination