Age old question: How to make #N/A into 0, and blanks into 0 - sample uploadad

[Mitch199]

Hi guys,

As the title suggests, I am almost finished the worksheet, and am now needing to tidy it up. I have a certain row of data (23) that is giving me #N/A scores, due to no data as yet being inputted into the source sheet it is getting data from. The source sheets are constantly updated and changing, and this summary sheet has a formula to get info from the 40 or so source sheets and put the corresponding values on the summary.

I have included a copy, not sure if it will work for you guys as you don't have the 40 or so sheets it picks data from on your computers (at least i dont think so, haha!)

I need to alter the VLOOKUP formula so if it ever hits a non-value, it will just throw up a 0 instead of the ugly looking #N/A.

In the final "Air Free" column, it also contains a formula that I would like to just show a 0 instead of a blank when no info can be calculated. It will display a 0 if the value is exactly that, 0, but I also want it to show 0 if there is nothing to display.

Any help would be awesome, I hope you can see what I mean!

Regards, Mitch

[:) Sixthsense :)]

User Iferror() function to mask the error with your desired output.

=IFERROR(YourFormulaGoesHere,0)


In O12 Cell

=IF(C7="","",IFERROR(VLOOKUP(C7,'N:\AAAFolder Structure\TECHNICAL SERVICES\Ventilation\Bag Sample Spreadsheets\LW104 Smartmate files\[LW104 IR TG seal.xls]RESULTS'!$C$12:$I$110,4),0))

Drag it down...

Add the Iferror() to the rest of the Vlookup() formulas with the above approach.


In I12 Cell


=IFERROR(IF(H12=0,0,(H12*100/(D12-E12*3.77258+F12-E12*0.001577+G12))),0)


Drag it down...

[Mitch199]

THANKYOU ONCE AGAIN!!

Awesome, works great.

[Mitch199]

THANKYOU ONCE AGAIN!!

Awesome, works great.