Hi
I have graph that takes value from sheet in my workbook.
The sheet has some Excell functions that calculate values.
Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0,
because I have not put any values in cell c12:c24. But on my graph for the
week of Dec 24 , it plots value 0.
I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get
a blank value in cell, so that my graph does not capture the 0 value, but it
still keeps on caturing 0 and plots value in my graph.
I have changed the setting on Tools-options-Chart-plot empty cells as not
plotted, but it does not work.

Please advise what to do so that, the cells in my sheet that have a formula
but the value is 0 or "" (blank), do not plot as value 0, but leave them as
not plotted because no value exist for that cell.

Regards

Happy Holidays

Replace the second "" with NA() in your formula


if(sum(c12:c24)="",NA(),sum(c12:c24))

of course it will throw the #N/A which can look ugly in a formula but you
can hide that using conditional formatting and white fonts


--


Regards,


Peo Sjoblom


"ub" wrote in message
...
Hi
I have graph that takes value from sheet in my workbook.
The sheet has some Excell functions that calculate values.
Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is
0,
because I have not put any values in cell c12:c24. But on my graph for the
week of Dec 24 , it plots value 0.
I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to
get
a blank value in cell, so that my graph does not capture the 0 value, but
it
still keeps on caturing 0 and plots value in my graph.
I have changed the setting on Tools-options-Chart-plot empty cells as not
plotted, but it does not work.

Please advise what to do so that, the cells in my sheet that have a
formula
but the value is 0 or "" (blank), do not plot as value 0, but leave them
as
not plotted because no value exist for that cell.

Regards

Happy Holidays

I don't think that Peo saw that first portion.

=sum()
is going to return a number (or error) -- it'll never be "".

So maybe:
To check the sum:
=if(sum(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered at least one number
=if(count(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered a number in all the cells in c12:c24
=if(count(c12:c24)<13,NA(),sum(c12:c24))



Peo Sjoblom wrote:

Replace the second "" with NA() in your formula

if(sum(c12:c24)="",NA(),sum(c12:c24))

of course it will throw the #N/A which can look ugly in a formula but you
can hide that using conditional formatting and white fonts

--

Regards,

Peo Sjoblom

"ub" wrote in message
...
Hi
I have graph that takes value from sheet in my workbook.
The sheet has some Excell functions that calculate values.
Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is
0,
because I have not put any values in cell c12:c24. But on my graph for the
week of Dec 24 , it plots value 0.
I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to
get
a blank value in cell, so that my graph does not capture the 0 value, but
it
still keeps on caturing 0 and plots value in my graph.
I have changed the setting on Tools-options-Chart-plot empty cells as not
plotted, but it does not work.

Please advise what to do so that, the cells in my sheet that have a
formula
but the value is 0 or "" (blank), do not plot as value 0, but leave them
as
not plotted because no value exist for that cell.

Regards

Happy Holidays

--

Dave Peterson

Thanks and God Jul

--


Regards,


Peo Sjoblom


"Dave Peterson" wrote in message
...
I don't think that Peo saw that first portion.

=sum()
is going to return a number (or error) -- it'll never be "".

So maybe:
To check the sum:
=if(sum(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered at least one number
=if(count(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered a number in all the cells in c12:c24
=if(count(c12:c24)<13,NA(),sum(c12:c24))



Peo Sjoblom wrote:

Replace the second "" with NA() in your formula

if(sum(c12:c24)="",NA(),sum(c12:c24))

of course it will throw the #N/A which can look ugly in a formula but you
can hide that using conditional formatting and white fonts

--

Regards,

Peo Sjoblom

"ub" wrote in message
...
Hi
I have graph that takes value from sheet in my workbook.
The sheet has some Excell functions that calculate values.
Example for the week of Dec 22 a cell has formaula sum(c12:c24) which
is
0,
because I have not put any values in cell c12:c24. But on my graph for
the
week of Dec 24 , it plots value 0.
I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to
get
a blank value in cell, so that my graph does not capture the 0 value,
but
it
still keeps on caturing 0 and plots value in my graph.
I have changed the setting on Tools-options-Chart-plot empty cells as
not
plotted, but it does not work.

Please advise what to do so that, the cells in my sheet that have a
formula
but the value is 0 or "" (blank), do not plot as value 0, but leave
them
as
not plotted because no value exist for that cell.

Regards

Happy Holidays

--

Dave Peterson

Hi
Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)),
works with the graph. But it creates another problem in my monthly total. The
sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another cell c26
that has formula sum(c1,c10,c25), now since c25 has value #n/a the total for
the month shows #n/a and it gives error in my next graph.
is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with #n/a in
cell c25.
Thanks

"Peo Sjoblom" wrote:

Thanks and God Jul

--


Regards,


Peo Sjoblom


"Dave Peterson" wrote in message
...
I don't think that Peo saw that first portion.

=sum()
is going to return a number (or error) -- it'll never be "".

So maybe:
To check the sum:
=if(sum(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered at least one number
=if(count(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered a number in all the cells in c12:c24
=if(count(c12:c24)<13,NA(),sum(c12:c24))



Peo Sjoblom wrote:

Replace the second "" with NA() in your formula

if(sum(c12:c24)="",NA(),sum(c12:c24))

of course it will throw the #N/A which can look ugly in a formula but you
can hide that using conditional formatting and white fonts

--

Regards,

Peo Sjoblom

"ub" wrote in message
...
Hi
I have graph that takes value from sheet in my workbook.
The sheet has some Excell functions that calculate values.
Example for the week of Dec 22 a cell has formaula sum(c12:c24) which
is
0,
because I have not put any values in cell c12:c24. But on my graph for
the
week of Dec 24 , it plots value 0.
I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to
get
a blank value in cell, so that my graph does not capture the 0 value,
but
it
still keeps on caturing 0 and plots value in my graph.
I have changed the setting on Tools-options-Chart-plot empty cells as
not
plotted, but it does not work.

Please advise what to do so that, the cells in my sheet that have a
formula
but the value is 0 or "" (blank), do not plot as value 0, but leave
them
as
not plotted because no value exist for that cell.

Regards

Happy Holidays

--

Dave Peterson

Can all the cells C1, C10 and C25 get this error, if yes you can use

=SUMPRODUCT(SUMIF(INDIRECT({"C1","C10","C25"}),"<= 0"&99^99))


which will ignore any error, if no just check for C25 like

=IF(ISNA(C25),SUM(C1,C10),SUM(C1,C10,C25))


--


Regards,


Peo Sjoblom


"ub" wrote in message
...
Hi
Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)),
works with the graph. But it creates another problem in my monthly total.
The
sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another cell
c26
that has formula sum(c1,c10,c25), now since c25 has value #n/a the total
for
the month shows #n/a and it gives error in my next graph.
is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with #n/a
in
cell c25.
Thanks

"Peo Sjoblom" wrote:

Thanks and God Jul

--


Regards,


Peo Sjoblom


"Dave Peterson" wrote in message
...
I don't think that Peo saw that first portion.

=sum()
is going to return a number (or error) -- it'll never be "".

So maybe:
To check the sum:
=if(sum(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered at least one number
=if(count(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered a number in all the cells in c12:c24
=if(count(c12:c24)<13,NA(),sum(c12:c24))



Peo Sjoblom wrote:

Replace the second "" with NA() in your formula

if(sum(c12:c24)="",NA(),sum(c12:c24))

of course it will throw the #N/A which can look ugly in a formula but
you
can hide that using conditional formatting and white fonts

--

Regards,

Peo Sjoblom

"ub" wrote in message
...
Hi
I have graph that takes value from sheet in my workbook.
The sheet has some Excell functions that calculate values.
Example for the week of Dec 22 a cell has formaula sum(c12:c24)
which
is
0,
because I have not put any values in cell c12:c24. But on my graph
for
the
week of Dec 24 , it plots value 0.
I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24))
to
get
a blank value in cell, so that my graph does not capture the 0
value,
but
it
still keeps on caturing 0 and plots value in my graph.
I have changed the setting on Tools-options-Chart-plot empty cells
as
not
plotted, but it does not work.

Please advise what to do so that, the cells in my sheet that have a
formula
but the value is 0 or "" (blank), do not plot as value 0, but leave
them
as
not plotted because no value exist for that cell.

Regards

Happy Holidays

--

Dave Peterson

Hi Peo
Thanks for your reply.
The error I get is in the cell , where I have formula sum (a25: d25), now
since c25 has value #n/a , the cell value in the formula comes to #n/a and
the graph that plots value from the cell that has the formula is also
effected.

regards

"Peo Sjoblom" wrote:

Can all the cells C1, C10 and C25 get this error, if yes you can use

=SUMPRODUCT(SUMIF(INDIRECT({"C1","C10","C25"}),"<= 0"&99^99))


which will ignore any error, if no just check for C25 like

=IF(ISNA(C25),SUM(C1,C10),SUM(C1,C10,C25))


--


Regards,


Peo Sjoblom


"ub" wrote in message
...
Hi
Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)),
works with the graph. But it creates another problem in my monthly total.
The
sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another cell
c26
that has formula sum(c1,c10,c25), now since c25 has value #n/a the total
for
the month shows #n/a and it gives error in my next graph.
is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with #n/a
in
cell c25.
Thanks

"Peo Sjoblom" wrote:

Thanks and God Jul

--


Regards,


Peo Sjoblom


"Dave Peterson" wrote in message
...
I don't think that Peo saw that first portion.

=sum()
is going to return a number (or error) -- it'll never be "".

So maybe:
To check the sum:
=if(sum(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered at least one number
=if(count(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered a number in all the cells in c12:c24
=if(count(c12:c24)<13,NA(),sum(c12:c24))



Peo Sjoblom wrote:

Replace the second "" with NA() in your formula

if(sum(c12:c24)="",NA(),sum(c12:c24))

of course it will throw the #N/A which can look ugly in a formula but
you
can hide that using conditional formatting and white fonts

--

Regards,

Peo Sjoblom

"ub" wrote in message
...
Hi
I have graph that takes value from sheet in my workbook.
The sheet has some Excell functions that calculate values.
Example for the week of Dec 22 a cell has formaula sum(c12:c24)
which
is
0,
because I have not put any values in cell c12:c24. But on my graph
for
the
week of Dec 24 , it plots value 0.
I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24))
to
get
a blank value in cell, so that my graph does not capture the 0
value,
but
it
still keeps on caturing 0 and plots value in my graph.
I have changed the setting on Tools-options-Chart-plot empty cells
as
not
plotted, but it does not work.

Please advise what to do so that, the cells in my sheet that have a
formula
but the value is 0 or "" (blank), do not plot as value 0, but leave
them
as
not plotted because no value exist for that cell.

Regards

Happy Holidays

--

Dave Peterson

Use the formula I provided


--


Regards,


Peo Sjoblom


"ub" wrote in message
...
Hi Peo
Thanks for your reply.
The error I get is in the cell , where I have formula sum (a25: d25), now
since c25 has value #n/a , the cell value in the formula comes to #n/a and
the graph that plots value from the cell that has the formula is also
effected.

regards

"Peo Sjoblom" wrote:

Can all the cells C1, C10 and C25 get this error, if yes you can use

=SUMPRODUCT(SUMIF(INDIRECT({"C1","C10","C25"}),"<= 0"&99^99))


which will ignore any error, if no just check for C25 like

=IF(ISNA(C25),SUM(C1,C10),SUM(C1,C10,C25))


--


Regards,


Peo Sjoblom


"ub" wrote in message
...
Hi
Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)),
works with the graph. But it creates another problem in my monthly
total.
The
sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another
cell
c26
that has formula sum(c1,c10,c25), now since c25 has value #n/a the
total
for
the month shows #n/a and it gives error in my next graph.
is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with
#n/a
in
cell c25.
Thanks

"Peo Sjoblom" wrote:

Thanks and God Jul

--


Regards,


Peo Sjoblom


"Dave Peterson" wrote in message
...
I don't think that Peo saw that first portion.

=sum()
is going to return a number (or error) -- it'll never be "".

So maybe:
To check the sum:
=if(sum(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered at least one number
=if(count(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered a number in all the cells in
c12:c24
=if(count(c12:c24)<13,NA(),sum(c12:c24))



Peo Sjoblom wrote:

Replace the second "" with NA() in your formula

if(sum(c12:c24)="",NA(),sum(c12:c24))

of course it will throw the #N/A which can look ugly in a formula
but
you
can hide that using conditional formatting and white fonts

--

Regards,

Peo Sjoblom

"ub" wrote in message
...
Hi
I have graph that takes value from sheet in my workbook.
The sheet has some Excell functions that calculate values.
Example for the week of Dec 22 a cell has formaula sum(c12:c24)
which
is
0,
because I have not put any values in cell c12:c24. But on my
graph
for
the
week of Dec 24 , it plots value 0.
I changed the formaula in cell ,
if(sum(c12:c24)="","",sum(c12:c24))
to
get
a blank value in cell, so that my graph does not capture the 0
value,
but
it
still keeps on caturing 0 and plots value in my graph.
I have changed the setting on Tools-options-Chart-plot empty
cells
as
not
plotted, but it does not work.

Please advise what to do so that, the cells in my sheet that have
a
formula
but the value is 0 or "" (blank), do not plot as value 0, but
leave
them
as
not plotted because no value exist for that cell.

Regards

Happy Holidays

--

Dave Peterson

An alternative is to keep your original formulas in the data table for
display or analysis, and create another range for charting which uses a
formula like:

=IF(D1)="",NA(),D1)

Being linked, the chart will update with the data, but the table will not
have the error in the display or in downstream calculations.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ub" wrote in message
...
Hi
Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)),
works with the graph. But it creates another problem in my monthly total.
The
sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another cell
c26
that has formula sum(c1,c10,c25), now since c25 has value #n/a the total
for
the month shows #n/a and it gives error in my next graph.
is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with #n/a
in
cell c25.
Thanks

"Peo Sjoblom" wrote:

Thanks and God Jul

--


Regards,


Peo Sjoblom


"Dave Peterson" wrote in message
...
I don't think that Peo saw that first portion.

=sum()
is going to return a number (or error) -- it'll never be "".

So maybe:
To check the sum:
=if(sum(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered at least one number
=if(count(c12:c24)=0,NA(),sum(c12:c24))

or
to check to see if you've entered a number in all the cells in c12:c24
=if(count(c12:c24)<13,NA(),sum(c12:c24))



Peo Sjoblom wrote:

Replace the second "" with NA() in your formula

if(sum(c12:c24)="",NA(),sum(c12:c24))

of course it will throw the #N/A which can look ugly in a formula but
you
can hide that using conditional formatting and white fonts

--

Regards,

Peo Sjoblom

"ub" wrote in message
...
Hi
I have graph that takes value from sheet in my workbook.
The sheet has some Excell functions that calculate values.
Example for the week of Dec 22 a cell has formaula sum(c12:c24)
which
is
0,
because I have not put any values in cell c12:c24. But on my graph
for
the
week of Dec 24 , it plots value 0.
I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24))
to
get
a blank value in cell, so that my graph does not capture the 0
value,
but
it
still keeps on caturing 0 and plots value in my graph.
I have changed the setting on Tools-options-Chart-plot empty cells
as
not
plotted, but it does not work.

Please advise what to do so that, the cells in my sheet that have a
formula
but the value is 0 or "" (blank), do not plot as value 0, but leave
them
as
not plotted because no value exist for that cell.

Regards

Happy Holidays

--

Dave Peterson