Limit Trend lines to positive value only

Hi Folks,

I have been asked to ensure that sales representatives trends are to stop at Zero, as they are unable to trend below selling less than nothing.

The attached picture Trendline.jpg showing the trend going below Zero, is not wanted.

Is there any method to accomplish this?

Thanks in advance

Iain

I don't think there's a quick and easy way to this in the chart. I would probably do it in the spreadsheet:

1) Use LINEST function to calculate the coefficients of the trendline.
2) Add a column to calculate the values of the trendline at each point.
3) Then add another column =Max(trendline,0) which should convert all those points below 0 to 0.

MrShorty,

Thanks for the assistance, I am unfamiliar with this function and from the reading of it I will need to some more help.

Could you please insert this function into the attached workbook chart help.xlsb to enable me to better understand how this will work?

Thanks in advance

Unfortunately, I don't have Excel 2007, so I can't view or change your file.

Which function are you unfamiliar with? Do you have a specific question about these functions. It is often easier to answer specific questions rather than, "it doesn't work."

Here's a sample spreadsheet, using the data from the Excel help file for the LINEST function showing the basic use of the LINEST, since I'm guessing that is the main part of your question about the solution. Remember that LINEST is an array function, so it must be confirmed with ctrl-shift-enter.

MrShorty,

from your example sheet I don't understand why there are two different results for the same formula? yet when I load the same formula (as an array) into an exaple sheet I get the same answer both times

Attached is a compatible version of the worksheet chart help.xls so you can see my "work"

Your assistance is appreaciated and I look forward to learning this function and the associated TREND.

from your example sheet I don't understand why there are two different results for the same formula?

In short, this is because you did not enter the LINEST function as an array formula. You must select the range for the function, then confirm with ctrl-shift-enterl. You should probably also read through the Excel help file for the LINEST function (http://office.microsoft.com/en-us/ex...010069838.aspx) so you can better understand what the LINEST function is trying to accomplish. This file also has detailed instructions for how to enter this function as an array function.

If you have any questions about what linear regression is supposed to do, you might review a statistics text or similar internet page to understand what linear regression is about.

Looking at your sample file, there are inconsistencies that make it hard for me to understand what you are trying to accomplish. The chart you show is a basic line chart that does not specify x data, so Excel assumes x is the count numbers {1,2,3,4,...}. In your implementation of the LINEST function, you used month as the y data and column G as the x data. In the chart, column G is shown on the y axis. I also note that you seem to only have data for month 8, so a regression against month number is not going to show much because there is only one month with data.

At this point, I would suggest you go back and look at your problem statement. What are you trying to show with your trendline? What are your independent (x) and dependent (y) variables. Once you understand what your regression is trying to do, and you get a better understanding of how the LINEST function works to give you the coefficients of a linear regression, I expect the spreadsheet will be easy to program.

Originally Posted by MrShorty

In short, this is because you did not enter the LINEST function as an array formula. You must select the range for the function, then confirm with ctrl-shift-enterl. You should probably also read through the Excel help file for the LINEST function (http://office.microsoft.com/en-us/ex...010069838.aspx) so you can better understand what the LINEST function is trying to accomplish. This file also has detailed instructions for how to enter this function as an array function.

If you have any questions about what linear regression is supposed to do, you might review a statistics text or similar internet page to understand what linear regression is about.

Thanks for looking at this for me, i do remember entering the formula as an array via ctrl-shift-enter, in hindsight I should have used a data set from a consultant who had a larger history.

I was supposed to be studying Business Statistics this university term; work got in the way of this but I still have the text so I'll get intoo that and do some learning.

Thanks for tying to point me in the right direction, once I've educated myself I'll give it another crack

Cheers

Iain