HELP!! chart graphing zeros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
Click to expand...
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.
 
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.
 
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
 
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
Click to expand...
entered. i don't want it to graph zeros.
 
Back
Top