Error in graph

[ub]

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

 

[Peo Sjoblom]

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

 

[Dave Peterson]

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]

Thanks and God Jul

--


Regards,


Peo Sjoblom

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))

 

[ub]

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

Thanks and God Jul

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

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

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

 

[ub]

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]

Use the formula I provided


--


Regards,


Peo Sjoblom

 

[Jon Peltier]

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
_______

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