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

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

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

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

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

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

THANKYOU ONCE AGAIN!!

Awesome, works great.

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

THANKYOU ONCE AGAIN!!

Awesome, works great.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1