Not have #N/A show up in cell

[bottman6]

Is there away to add to formula to when no value is there that it leave the cell blank and not give me #N/A

 

[MOB]

=if(iserror(yourformula),"",yourformula)

Something like that!

 

[Aladin Akyurek]

Is there away to add to formula to when no value is there that it leave the cell blank and not give me #N/A

Would you post the formula you have and specify what kind of data (number or text) it is expected to return?

 

[Brandwood]

To chip into this debate, using ISERROR in the format suggested by MOB is good and correct, but be aware it will correct for every error, not just #N/A.

This may create other issues in your model when it corrects for bad results inthe data set like #num or div/0.

applies to ISERR as well as ISERROR so instead try ISNA.

as you might guess it will only correct for #N/A errors.

regards

 

[bottman6]

• =VLOOKUP(A12,$V$14:$W$18,2,FALSE)+VLOOKUP(C12,$V$14:$W$18,2,FALSE) returns number

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>

• =SUM(H12,I12)*(IF(K12=0,0,(IF(K12=0,0,1)))) returns number

<o> </o>

• =SUM(H12:I12)*K12 returns number

<o> </o>

• =ROUNDUP(PRODUCT(0.05,N12),0) returns number

<o> </o>

• =SUM(N12:O12) returns number

<o> </o>

• =M12*L12*1.05 returns number

<o> </o>

• =SUM(H12,I12)*J12*1.05 returns number

<o> </o>

• =M12/100*X12/4*(IF(K12<1,0,1)) returns number

 

[Aladin Akyurek]

Can you get away with:

=SUM(SUMIF($V$14:$V$18,A12,$W$14:$W$18),
         SUMIF($V$14:$V$18,C12,$W$14:$W$18))

• =VLOOKUP(A12,$V$14:$W$18,2,FALSE)+VLOOKUP(C12,$V$14:$W$18,2,FALSE) returns number

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

• =SUM(H12,I12)*(IF(K12=0,0,(IF(K12=0,0,1)))) returns number

<o></o>

• =SUM(H12:I12)*K12 returns number

<o></o>

• =ROUNDUP(PRODUCT(0.05,N12),0) returns number

<o></o>

• =SUM(N12:O12) returns number

<o></o>

• =M12*L12*1.05 returns number

<o></o>

• =SUM(H12,I12)*J12*1.05 returns number

<o></o>

• =M12/100*X12/4*(IF(K12<1,0,1)) returns number

 

[bottman6]

can i get away with that formula for all my formula? no these are the formulas that work for my sheet.

 

[bottman6]

also sorry to ask is there away to make it zero istead of blank, or what i would prefer is if it blank make the cell red, or have a red border around it?

 

[Aladin Akyurek]

can i get away with that formula for all my formula? no these are the formulas that work for my sheet.

I'm not clear on your answer: Is the SumIf formula an appropriate substitute for your VLOOKUP formula or not? If not, why not?