If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to set a formula if the value is with #N/A
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. |
#2
|
|||
|
|||
How to set a formula if the value is with #N/A
D2=IF(C2"#N/A",C2+B2,B2)
Assuming the Bonus values are in Column C and Salary amounts are in Column B. |
#3
|
|||
|
|||
How to set a formula if the value is with #N/A
A Find & Replace?
"lee-sc" wrote in message ... 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. |
#4
|
|||
|
|||
How to set a formula if the value is with #N/A
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" wrote in message ... 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. |
#5
|
|||
|
|||
How to set a formula if the value is with #N/A
Hi,
Try this =sumif(B2:C2,"=-9.999E307",B2:C2) -- Regards, Ashish Mathur Microsoft Excel MVP "lee-sc" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|