Solved

How to disable #N/A being displayed

Posted on 2012-03-22
5
194 Views
Last Modified: 2012-06-27
Hi,

In cell B10 a #N/A is being displayed because cells F10 has now value (0)

F10 is a result of multiplying D10 with E10

How do i not show the #N/A

Thanks for any help
experts-1d.xlsx
0
Comment
Question by:propolis
5 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751394
User the ISNA function:
IF(ISNA(D10*E10),0,D10*E10)
0
 
LVL 7

Expert Comment

by:PaulNSW
ID: 37751409
you could use conditional formatting to change the cell text colour to white

use

=ISNA($cell$reference)
0
 

Author Comment

by:propolis
ID: 37751604
Hi,

Not sure how to implement this:

F10 has already a calculation in it to which cell B10 does a vlookup based on the value f10

So f10 is:   0  then the vlookup results is #N/A

Please advice and thanks for your help
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751608
Just replace F10. It has some formula, for example, =VLOOKUP(....). Just change it to:
=IF(ISNA(VLOOKUP(.....),0,VLOOKUP(.....))
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 37751609
You need to change the formula in B10 (and the cells above) to:
=IFERROR(VLOOKUP(F10,Sheet2!$A$2:$C$10,3,FALSE),"")

for example. Note: you will need to disable the data validation temporarily to make the change.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
macro for each dropdown 15 45
Hiding column macro 10 28
Search for a value in Column? 5 21
Google Sheets - Artificial Intelligence 2 6
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now