Eliminate the appearance of a dip when plotting increasing data with smoothing

Question

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?

Answer 1

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:

     

Answer 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.

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.

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.

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.

Answer 3

A really simple fix is:

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

Done.