Hiding the "#N/A" value

T

theslaz

How does one hide this value so that it isn't visible in a cell. Also;
it appears that when I try a simple calculation of cells; and one of
those cells has this value listed in it; my calculation does not
complete. How can I correct this?
 
T

theslaz

Anne said:
1. =IF(ISNA(myformula),"",myformula)

2. Usually, it means one is formatted as text or has spaces in it or
something.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com
Click to expand...
Don't quite know what you mean. What I have is a invoice that has
several drop down lists. The rows that aren't used show these "#N/A"
symbols. I know what they mean; just want to hide them and be able to do
a simple calculation on the rows that are used and that have values in them.
 
A

Anne Troy

In order to hide the #N/As, you need to change your formulas. I can't help
you change your formulas if I don't know your formulas, and if you don't
know what I mean by my number 1 answer. :)

Why don't you provide one of the formulas you're using, and I'll show you
how to change it so it doesn't show #N/A?

It MIGHT be:
=IF(ISBLANK(SOMECELL),"",myformula)

But I don't know if you understand that.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


theslaz said:
Don't quite know what you mean. What I have is a invoice that has
several drop down lists. The rows that aren't used show these "#N/A"
symbols. I know what they mean; just want to hide them and be able to do
a simple calculation on the rows that are used and that have values in
Click to expand...
them.
 
T

theslaz

Anne said:
In order to hide the #N/As, you need to change your formulas. I can't help
you change your formulas if I don't know your formulas, and if you don't
know what I mean by my number 1 answer. :)

Why don't you provide one of the formulas you're using, and I'll show you
how to change it so it doesn't show #N/A?

It MIGHT be:
=IF(ISBLANK(SOMECELL),"",myformula)

But I don't know if you understand that.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com




them.
Click to expand...
I have built a invoice. This invoice has several drop down lists in
order to pull up different items. Each item has a value. My invoice at
this time has 12 lines (rows); in each of these rows I have the ability
to list my items and there cost. Sometimes I may use 1 row; some times I
may use 6 rows. The unused rows all show the "#N/A" symbol in the cell
listing the price of a item; that is because there is no item chosen for
that row; and I don't want to choose an item because my invoice is
complete. I know that the spreadsheet is treating this as an "error";
but there must be some way to get around this.

I probably should mention that I'm using the "Vlookup" syntax in order
to pull up the respective price for each item.

=vlookup(c1;a1:b10,2,0 ) All this does is pull up the proper price of
the item that will be listed in C1. The spreadsheet does that no
problem. It is the blank rows that I'm having a problem with. I have put
this formula in each of the other rows. If I don't use/need those rows;
it will show the "#N/A" error.
 
R

Ron Rosenfeld

How does one hide this value so that it isn't visible in a cell. A
Click to expand...

You can hide the N/A value by using Conditional Formatting for the cells in
which it might appear. e.g: Format/Conditional Formatting Formula Is:

=ISNA(A1)

and format the font to white (or whatever the background color is).

also;
it appears that when I try a simple calculation of cells; and one of
those cells has this value listed in it; my calculation does not
complete. How can I correct this?
Click to expand...

It depends on the formula and exactly what you want to happen when one of the
factors is #N/A. You'll need to post the formula(s).


--ron
 
T

theslaz

Anne said:
Hi, Slaz. Exactly. I understand. So, you'll need to edit each of your
formulas (sorry! I know it's a pain!) to be like this:

=if(isblank(c1),"",vlookup(c1;a1:b10,2,0 ))

I hope it helps!
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com
Click to expand...
I tried what you suggested and I got rid of the "#N/A" value; but the
"#value" showed up. I still was not able to add my column. I therefore
inserted a "0" between the ""; and it appears to work. Do you find
anything wrong with this?
 
A

Anne Troy

Yes, Slaz. If you want zero (it's a value), you don't need the quotes! :)
(Though I cannot figure why you'd get a #Value if the formula was right.
Also, you can reply at the top of the message...you don't have to scroll
down to the bottom--that is, if that's what you're doing. :)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com
 
E

Ed Ferrero

If you wish to leave formulas alone, but hide #N/A, use a custom format that
includes colours for numeric entries, and then format the font colour for
that cell to white on a white background. Numbers come out in the format
colour, and #N/A shows in white - can't see it on a whie background.

ie set a cell to this custom format
[black]0.0;[red]-0.0

Then change the cell font colour to white.

Copy the format to wherever its needed.

Ed Ferrero
http://edferrero.m6.net
 
Top