2

I have a data set in which Y is an increasing function of X.  Here is the data:

5  | 0.1
10 | 0.789
15 | 147.5
20 | 216.5
25 | 276.5

I create an XY (Scatter) Chart with Smooth Lines:

     

Because the first two points are very close in Y value, and not characteristic of the distribution of the other points, the smooth line option displays a 'dip' between those two points.

How can I plot this with a smoothed line that estimates the curve on which the data lie without having it include a dip?

  • Is adding a trendline while hiding the main graph an acceptable option? – K. Rmth Sep 6 '15 at 15:25
  • @k.Rmth can u see the data i added to help me determine the solution? – cjh Sep 6 '15 at 15:47
  • What do you mean by "dip" down? – DavidPostill Sep 6 '15 at 15:59
  • @DavidPostill the second image of the search results from google image search 'dip graph' – cjh Sep 6 '15 at 16:07
  • Hmm. This is what I get in LibreOffice Calc imgur.com/sR1lX0B - I don't get any "dips" – DavidPostill Sep 6 '15 at 16:28
0

Are you willing to manipulate (fudge) your data to get the desired chart?  I was able to add one point:

5     0.1
9.9   0.7
10    0.789
15    147.5
20    216.5
25    276.5

and the chart changed to this:

     

  • No need to fudge the data, simply repeat the second point. – Jon Peltier Sep 10 '15 at 12:23
2

Aside from looking prettier, there's usually no reason to use smoothed lines. Not if you're plotting real data. The smoothing hides the fact that you only have a handful of data points. I'm always a little suspicious (no offense) when I see a chart with smoothed lines, and I always recommend that people not use them.

The most honest way to plot the points is with markers connected with straight line segments.

Straight Lines With Markers

If you omit the markers but just show the straight lines, you get the following, which still has a slight indication of the scarcity of data.

Straight Lines Without Markers

If you absolutely must use smoothed lines, you can eliminate the dip without actually fudging your data (as suggested by @scott), by merely duplicating the second data pair.

Smoothed but Without the Dip

Not really different from the previous graph, with straight segments.


Edit by fixer1234: Understanding why this works is as useful as knowing that it works, and the reason isn't necessarily intuitive. Quick, simplified explanation:

I'll refer to the five XY data points as the letters A through E. Excel creates the smoothed line by fitting a curve to (a rolling), three points at a time: ABC, BCD, CDE. When points A, B, and C are in a pattern like this example, forcing the curve through those points can produce an unexpected shape, like the dip.

Adding a duplicate second point means that Excel is fitting ABB, BBC, BCD, CDE. The three points ABB have the same effect as fitting a line between two points, A and B, because the two Bs are essentially the same point for curve-fitting purposes. Similarly, BBC is equivalent to fitting a line between B and C. Fitting a line between two points gives you a straight line with those endpoints.

Note that while this solves the problem of graphing the data, it still introduces fake data. The value is identical to the real data, so it doesn't involve estimating intermediate points, but be aware that the added point can still throw off calculations that use the data for a purpose other than graphing.

  • 1
    I find that to be non-intuitive.  Can you explain why it works? – Scott Sep 10 '15 at 15:15
  • I don't know the exact details, but a colleague of mine fleshed it out a long time ago. Excel fits a bezier to two adjacent segments or three adjacent points (or does something equivalent). Apparently if two adjacent points have the same coordinates, these points behave like endpoints of a set of points. – Jon Peltier Sep 11 '15 at 17:06
  • @fixer1234 - That's fine. I thought a comment was sufficient, but maybe more people will read the amendment. Thanks for the comment too. – Jon Peltier Sep 14 '15 at 19:20
0

A really simple fix is:

Format Axis > Reset Bounds 'Minimum' to 0 > Draw a straight line connecting the missing line.

Done.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.