SUM, AVERAGE and PRINT with #N/A cells

Biff:

I loaded the page you refered to me and found that maybe I will need to pay
for any special info, but for the moment I just solved my problem adding a
column and just entering a formula like:
=IF(ISNA(L10),"",L10), so I hid the column containing the #N/A's values but
left it for the graphic and took the new column just to view the results I
got in the previous column and not to graphic it.

If you, in the future, find anyway to make this issue easier, I will be
grateful to receive any help about it.

THANKS A LOT!!!!!!

"T. Valko" wrote:

He may have a technique for not having to use #N/A that won't mess up the
chart.

--
Biff
Microsoft Excel MVP


"PaulinaDi" wrote in message
...
Thanks a lot for the other answers and for trying to solve my last
problem.
I'll reach Jon hoping he has a solution for this issue.

REGARDS!!!!!!


"T. Valko" wrote:

Well, I'm out of ideas.

Take a look around this website:

http://peltiertech.com/

It's supported by Excel MVP Jon Peltier, a charting expert. He may have
something on there that will work.

--
Biff
Microsoft Excel MVP


"PaulinaDi" wrote in message
...
Incredible but true....

Unfortunately I'm not able to paste the image I get but it is something
like
this:
Page SetUp
Page - Margins - Header / Footer - Sheet
On Sheet you have
Print Area
Print Titles
Print
Gridlines
Black and White
Draft Quality
Row and Comumn headings
Comments
Page Order.....

As you see, I'm not able to click on any Error function under the Print
option to disable it.

I have also looked for it into the Tools / Options menu and the Printer
menu
but I don´t have it.

Talking about the other option, I did it yesterday, I changed the color
for
the #N/A cells into gray (as my cell) but THEY APPEAR in black, not
just
in
gray but in black. The only way they function is if I turn the error
into
white and I leave the cell in white but honestly for some cases I need
to
use
colors in order to separate the columns and to make it easier to look
for
when my boss is watching them. That's why I wanted the gray columns to
use
a
special formula.



"T. Valko" wrote:

I can't believe there isn't a print option that let's you choose how
to
handle errors. I don't use Excel 2000 as you do so the menu path to
that
option in Excel 2000 may be different than what I posted. Look around
for
it.

If that option is not available...

Have you tried using conditional formatting to set the text color of
those
cells that contain #N/A to be the same as the background color of
those
cells? For example, the background cell color is white, set the font
to
also
be white.

--
Biff
Microsoft Excel MVP


"PaulinaDi" wrote in message
...
Thanks a lot for your answers. I understood all of them.

Regarding my last question this is the issue: I'm trying to use a
formula
with the NA() option in order to get my chart just with those rows
that
have
any value.
For example:

A L M
Month $ %
6 Jan 38.0843 15.88%
7 Feb 37.8526 -0.61%
8 Mar 41.6886 10.13%
9 Abr 45.5067 9.16%
10 May
11 Jun
12 Jul
13 Aug


To get the 15.88%, -0.61%, 10.13% and so on I used a formula like
this:
=IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a formula
that
brings results from another worksheet once I get some data for that
month.
So
in row 10, 11, 12 and 13 I get an #N/A result.

The formula to get the "L" column results is:
=IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256$O16)))

So what I really want is to get in my chart just the values I have
and
not
the ones I still don´t have like May, Jun, Jul, Aug and that's why I
use
the
NA() option but when I print my file, the NA() APPEARS in the
printed
sheet
when I use any color for my column different from blank. In column M
I
decided to use a gray color so I used the conditional formatting to
get
those
#N/A in gray but still appear in black when printed.

What am I doing wrong?

___________________________________

"Ashish Mathur" wrote:

Hi,

1. Difficult to guess why

2. "1E100" - This is a very large number I.e. 1 raised to the power
of
100.
So in effect we are saying that sum all numbers which are less than
or
equal
to 1E100. Since an error is not a number it is ignored. You may
also
specify the criteria as "=largest numbers I your range"

3. Average(IF( - b'coz there is no averageif() function. If you
do
not
wish to use the array formula, you can use the SUM function in the
numerator
and count function in the denominator
=SUMIF(A1:A10,"1E100")/countif(A1:A10,"1E100)

4. Question not clear

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"PaulinaDi" wrote in message
...
Thanks Sheeloo and T. Valko but I still have questions about this
issue.
I'm
just copying my new questions on this new post to make it easier
or
you
may
refer to my first post where I wrote them originally.

The original issue is after these questions.

I use Excel 2000 so I don´t have the FilePage SetupSheet tab
Under Print...Cell errors asselect blank OK option available.

The =SUMIF(A1:A10,"1E100") formula helped me but would like to
know
what
does "1E100" means.

About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))
I would like to know why should I use an array formula (in fact
it
worked
but just to know why)

And about the =IF(ISNA(your_formula),"",your_formula) formula,
maybe
I
get
a
blank cell to SUM or to AVERAGE but I get a mark on my chart just
on
that
month I'm using it and if I substitute the "" for NA() I get the
#N/A
mark
on
my printed files.

____________________________________________

I'm using a formula to bring results from another worksheet but
in
some
cases
I get the #N/A result as I don´t have data on the original
worksheet.
To
get
the graphics on the final worksheet I use the formula with NA()
not
to
give
me 0 values on the chart and I also use the conditional formula
to
get
the
#N/A results just like the cells were empty (the same color I use
for
that
cell) but when I print my page, I get the #N/A in black (so I can
see
them)
and additional I'm not able to get a SUM or AVERAGE on each
column
as I
get
the #N/A for those cells with that mark.

Two examples of the formula I'm using a

=IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15)))

=IF(L9/L8-1=0,NA(),L9/L8-1)

How may I get the SUM or the AVERAGE for that column and how may
I
print
my
file without seeing the #N/A's in black?

I was going to suggest doing something like that but that's like a last
resort. Sometimes you have to do whatever it takes even if that means it's
not the "perfect" solution.

--
Biff
Microsoft Excel MVP


"PaulinaDi" wrote in message
...
Biff:

I loaded the page you refered to me and found that maybe I will need to
pay
for any special info, but for the moment I just solved my problem adding a
column and just entering a formula like:
=IF(ISNA(L10),"",L10), so I hid the column containing the #N/A's values
but
left it for the graphic and took the new column just to view the results I
got in the previous column and not to graphic it.

If you, in the future, find anyway to make this issue easier, I will be
grateful to receive any help about it.

THANKS A LOT!!!!!!

"T. Valko" wrote:

He may have a technique for not having to use #N/A that won't mess up the
chart.

--
Biff
Microsoft Excel MVP


"PaulinaDi" wrote in message
...
Thanks a lot for the other answers and for trying to solve my last
problem.
I'll reach Jon hoping he has a solution for this issue.

REGARDS!!!!!!


"T. Valko" wrote:

Well, I'm out of ideas.

Take a look around this website:

http://peltiertech.com/

It's supported by Excel MVP Jon Peltier, a charting expert. He may
have
something on there that will work.

--
Biff
Microsoft Excel MVP


"PaulinaDi" wrote in message
...
Incredible but true....

Unfortunately I'm not able to paste the image I get but it is
something
like
this:
Page SetUp
Page - Margins - Header / Footer - Sheet
On Sheet you have
Print Area
Print Titles
Print
Gridlines
Black and White
Draft Quality
Row and Comumn headings
Comments
Page Order.....

As you see, I'm not able to click on any Error function under the
Print
option to disable it.

I have also looked for it into the Tools / Options menu and the
Printer
menu
but I don´t have it.

Talking about the other option, I did it yesterday, I changed the
color
for
the #N/A cells into gray (as my cell) but THEY APPEAR in black, not
just
in
gray but in black. The only way they function is if I turn the error
into
white and I leave the cell in white but honestly for some cases I
need
to
use
colors in order to separate the columns and to make it easier to
look
for
when my boss is watching them. That's why I wanted the gray columns
to
use
a
special formula.



"T. Valko" wrote:

I can't believe there isn't a print option that let's you choose
how
to
handle errors. I don't use Excel 2000 as you do so the menu path to
that
option in Excel 2000 may be different than what I posted. Look
around
for
it.

If that option is not available...

Have you tried using conditional formatting to set the text color
of
those
cells that contain #N/A to be the same as the background color of
those
cells? For example, the background cell color is white, set the
font
to
also
be white.

--
Biff
Microsoft Excel MVP


"PaulinaDi" wrote in message
...
Thanks a lot for your answers. I understood all of them.

Regarding my last question this is the issue: I'm trying to use a
formula
with the NA() option in order to get my chart just with those
rows
that
have
any value.
For example:

A L M
Month $ %
6 Jan 38.0843 15.88%
7 Feb 37.8526 -0.61%
8 Mar 41.6886 10.13%
9 Abr 45.5067 9.16%
10 May
11 Jun
12 Jul
13 Aug


To get the 15.88%, -0.61%, 10.13% and so on I used a formula like
this:
=IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a
formula
that
brings results from another worksheet once I get some data for
that
month.
So
in row 10, 11, 12 and 13 I get an #N/A result.

The formula to get the "L" column results is:
=IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256$O16)))

So what I really want is to get in my chart just the values I
have
and
not
the ones I still don´t have like May, Jun, Jul, Aug and that's
why I
use
the
NA() option but when I print my file, the NA() APPEARS in the
printed
sheet
when I use any color for my column different from blank. In
column M
I
decided to use a gray color so I used the conditional formatting
to
get
those
#N/A in gray but still appear in black when printed.

What am I doing wrong?

___________________________________

"Ashish Mathur" wrote:

Hi,

1. Difficult to guess why

2. "1E100" - This is a very large number I.e. 1 raised to the
power
of
100.
So in effect we are saying that sum all numbers which are less
than
or
equal
to 1E100. Since an error is not a number it is ignored. You
may
also
specify the criteria as "=largest numbers I your range"

3. Average(IF( - b'coz there is no averageif() function. If
you
do
not
wish to use the array formula, you can use the SUM function in
the
numerator
and count function in the denominator
=SUMIF(A1:A10,"1E100")/countif(A1:A10,"1E100)

4. Question not clear

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"PaulinaDi" wrote in
message
...
Thanks Sheeloo and T. Valko but I still have questions about
this
issue.
I'm
just copying my new questions on this new post to make it
easier
or
you
may
refer to my first post where I wrote them originally.

The original issue is after these questions.

I use Excel 2000 so I don´t have the FilePage SetupSheet tab
Under Print...Cell errors asselect blank OK option
available.

The =SUMIF(A1:A10,"1E100") formula helped me but would like
to
know
what
does "1E100" means.

About the AVERAGE formula
=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))
I would like to know why should I use an array formula (in
fact
it
worked
but just to know why)

And about the =IF(ISNA(your_formula),"",your_formula) formula,
maybe
I
get
a
blank cell to SUM or to AVERAGE but I get a mark on my chart
just
on
that
month I'm using it and if I substitute the "" for NA() I get
the
#N/A
mark
on
my printed files.

____________________________________________

I'm using a formula to bring results from another worksheet
but
in
some
cases
I get the #N/A result as I don´t have data on the original
worksheet.
To
get
the graphics on the final worksheet I use the formula with
NA()
not
to
give
me 0 values on the chart and I also use the conditional
formula
to
get
the
#N/A results just like the cells were empty (the same color I
use
for
that
cell) but when I print my page, I get the #N/A in black (so I
can
see
them)
and additional I'm not able to get a SUM or AVERAGE on each
column
as I
get
the #N/A for those cells with that mark.

Two examples of the formula I'm using a

=IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15)))

=IF(L9/L8-1=0,NA(),L9/L8-1)

How may I get the SUM or the AVERAGE for that column and how
may
I
print
my
file without seeing the #N/A's in black?