HELP!! chart graphing zeros

S

Stefano

here is a sample of my values

Date Sev1 Sev2 Sev3 Tota
5/5/04 1 1 2
5/6/4 1 1 1
5/7/04
5/8/04

the last field "Total" is a calculated field of the three before it. as you can see, with days where no data is entered the total field displays 0. i have a chart that graphs this total and it graphs the zeros. how can i get it to only graph the dates where data has been entered WITHOUT changing the data source? i have selected a whole month to graph and i want the chart to change dynamically as data is entered. i don't want it to graph zeros.
 
G

Guest

hmm would having the minimum scale value of Y on your
graph as 1 help? i.e only values => 1 will show?
-----Original Message-----
here is a sample of my values:

Date Sev1 Sev2 Sev3 Total
5/5/04 1 1 2 4
5/6/4 1 1 1 3
5/7/04 0
5/8/04 0

the last field "Total" is a calculated field of the three
before it. as you can see, with days where no data is
entered the total field displays 0. i have a chart that
graphs this total and it graphs the zeros. how can i get
it to only graph the dates where data has been entered
WITHOUT changing the data source? i have selected a whole
month to graph and i want the chart to change dynamically
as data is entered. i don't want it to graph zeros.
 
S

Stefano

thanks for that comment but unfortunately it still graphs the line

the line goes straight down from 15 to zero, showing a decline when there really isn't on

i'd like it not to draw the line to points that equal zero.
 
A

Andy Pope

Hi Stefano,

For the calculated field Total you will have to add a test for the Sev
fields being empty. When this condition is true output NA().
Assuming data is in A1:E5 try something like this,

=IF(AND(ISBLANK(B2),ISBLANK(C2),ISBLANK(D2)),NA(),SUM(B2:D2))

Although I guess there must be a better way of checking whether the Sev
values are all blank.

Cheers
Andy
 
T

Tushar Mehta

Use the general idea behind the examples on the Excel/Tutorials/Dynamic
Charts page of my web site. The defined name should check how many
entries there are for the Sev1 column (or Sev2 or Sev3) and use that
number in selecting the cells in the Total column.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

here is a sample of my values:

Date Sev1 Sev2 Sev3 Total
5/5/04 1 1 2 4
5/6/4 1 1 1 3
5/7/04 0
5/8/04 0

the last field "Total" is a calculated field of the three before it. as you can see, with days where no data is entered the total field displays 0. i have a chart that graphs this total and it graphs the zeros. how can i get it to only graph the dates where data has been entered WITHOUT changing the data source? i have selected a whole month to graph and i want the chart to change dynamically as data is
entered. i don't want it to graph zeros.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top