Connect the dots?

[Febra]

Is there a way to connect the plot points in a line chart when the plot points are a calculated point? In the pregnancy weight gain chart that I am working on there is an upper and lower line chart for the acceptable limits of weight gain. On the spreadsheet I added a place to plot current weight from which the prepregnant weight is subtracted and that weight gain is plotted on the chart. Unfortunately, the lines do not all connect. The line climbs from the baseline up to the plotting point and then back down to the baseline for each plotting. The plottings look like a series of mountain peaks. I have attempted to "interpolate" this, as was done with the upper and lower limits of weight gain chart lines but it is not working. Thanks!

 

[Thomas Brady]

Yes there is.

First go to Insert then Chart. Chart type will be line. Then on the Chart sub-type: select the figure that says "Line with markers displayed at each data value." Then click Next.

Next you would want to click on the series Tab and click on the Add button. You can name the range you'll being using the data for in the Name: input box. For the values you'll need to take out the ={1} then click on the button inside the box to get the data for the range. You can add more set just by clicking the add button and repeating the step of name and selecting the range

Just make sure the range of data is either is vertical or horizontal allignment.

 

[Jon Peltier]

Hi Febra -

The weight you are plotting is a differential, so you have a formula,
right? There must be gaps in the input data, so your formula must look
like this to avoid calculating when there is nothing to calculate from:

=IF(B2=0,"",B2-A2)

Problem is, "" is treated not as a blank, but as a text string. Excel
plots text strings as zero. But you're in luck (sort of), because Excel
interpolates over #N/A errors. So replace "" with NA(), like this:

=IF(B2=0,NA(),B2-A2)

This looks good in the chart, but ugly in the worksheet. But Debra
Dalgleish tells us how to hide these errors with conditional formatting:

http://contextures.com/xlCondFormat03.html#Errors

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

 

[Febra]

Thanks! I changed my IF(B2="",""",(B2-A2)) to the suggested
IF(B2=0,NA(),B2-A2) and it worked like a charm!!!! And Mr Peltier, I checked out your web site and loved the speedometer chart! Now I need to find some way of working that chart into our performance improvement reporting!