question

RaoAvinash-2966 avatar image
0 Votes"
RaoAvinash-2966 asked emilyhua-msft commented

Prevent chart with Smooth Lines from not going less than a minimum value

Hi, I am plotting a smooth line graph for the following coordinates {(1,10),(2,60),(3,50),(4,90),(5,0.5),(6,10),(7,40)}.

The drawn plot appears as though the there were some coordinates for which the y values were negative. Is there a way to avoid the curve from going less than a minimum value say 0.

194200-image.png


sql-server-reporting-servicesoffice-excel-itprooffice-scripts-excel-dev
image.png (33.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RaoAvinash-2966
I am working on this thread, any updates, I will post back.

0 Votes 0 ·

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft commented

Hi @RaoAvinash-2966

Based on my tests, there is no related option to set the the range of series values for Scatter with Smooth Lines, as it is automatically calculated from the data source.
We can only set the rang for Y-axis as you do for the chart above.

The workaround is to use Scatter with Straight Lines Chart, it just connects the data points.

Besides, if the negative values are not too large, you can try to add auxiliary columns to create a line in place of the X-axis.
194535-capture112.jpg
- Such as I add 2 coloums, one includes the values from 0 to 9, this determines the length of the fake x-axis.
- The another one includes the value "-0.37", which is about the minimum value displayed by Series 2 on your chart above.
- Then you can insert one Scatter line, remove X-axis and Gridlines, but show the X values for this Scatter line.

Hope the infromation could be helpful.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




capture112.jpg (132.5 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RaoAvinash-2966

I am checking thread, if there is any update or issue, welcome to post back.

0 Votes 0 ·

@emilyhua-msft, Thanks for your response.

The problem with moving the axes crossing is that, I only know the minimum value in the series data points. But it can also happen that the min value in the series that I am supplying to the chart greater than the actual points in the plot because of the smooth curve algo.
As far as using the straight line graph is concerned, the plot doesn't look visually good as there are only few data points in the series that I am trying to plot. So using Straight line graph is not an option.

0 Votes 0 ·

Hi @RaoAvinash-2966

Thanks for your reply.

But your issue is mainly related to algorithm used for Smoothed line: Bezier curves, Excel will use a cubic equation to fit the relationship between two adjacent points, and use this equation function to draw the curve between the two points.
We are unable to adjust the internal algorithm, as it is by design.

In my opinion, you can try to add additional data points and reduce the distance between points to improve the accuracy of the chart.

Such as I add the (5.1,0.4) under (5,0.5) on your sample.
195890-capture117.jpg
(Please note, you can hide the row where the new data is added, and then set the chart to display the data of the hidden row.)




0 Votes 0 ·
capture117.jpg (65.4 KiB)
Show more comments