can any one help me find such functionalities and specially the "Forecast " and the "Set Intercept" in Numbers ?( Screen shot taken form Excel )
Thanks in advance .
MacBook Pro with Retina display, OS X Mavericks (10.9.5)
I cannot see your screenshot. I guess the forum is still having problems.
There is no setting for the chart trend line to force it to go through zero. You will have to create an additional series in your table that will be the trend line data and you will include that series on your chart. LINEST gives you the ability to force the trend line to zero. But it might not be the best way to do it, it depends on your data. It will force the intercept to zero but the resulting slope may not fit your data very well. Maybe you would be better off keeping the original trend line slope and adding a constant offset to the data so the line goes through zero. It all depends on your data. Or maybe there is a non-linearity at zero which is why the line does not go that way.
i uploaded it again , can you please take a look at what i mean , thanks
Images still seem to be a problem here but I can see it if I double click on it.
Forecasting you can do by adding an X data point (with no Y data) to your chart. If you want to forecast back to 0, you can add a row to your data and put a 0 in the X column. Include that row in your data and the trend line will start there. Likewise, if you want the trend line to extend to the right, add another row and put in the largest X value you want.
But there are no options on a chart for forcing the trend line to go through Y=0. You will have to ditch the chart's trend line and create a trend line in your table. Add a new column to your table, use LINEST to calculate the slope with an intercept of 0 and create two or more data points for the trend line. Plot it as a second series.
thanks a lot for your helpful answers , one last thing ,
i did what you told me to do , but how can i graph the new trend line after getting the slope of the 0 forced-intercept line .
many thanks again
so i got my points , and then got the slope of the 0 forced intercept best-fit line throughout LINEST function , now i have the slope and i have the point (0,0) so i can draw my line , well
after doing these steps very carefully many times ,
i get this equation(screen shot ) of the line which simply doest not have b as 0 !! it has some strange numbers in terms of E , even though it supposed to be zero because i have already drawn this line BASED ON THE SLOPE WHICH MAKES THE y-INTERCEPT AT 0,0 ,
can't figure out whats wrong .
This is what I meant:
Formulas:
F2 = LINEST(C2:C6,B2:B6,0) and I formatted it to 3 decimal places
D1 = "Y = "&F2&" * X"
D2 = $F$2×B2 and fill down to complete the column
The value in D1 becomes the name of series in the chart legend. Being a trend line, I made the name be the trend line equation
The way computer math works, there are often extremely small errors. That is why the trend line in your last chart has a Y intercept of 1.6 x 10^-15 instead of being exactly zero. I suspect the LINEST function makes it exactly zero because it was specified to be exactly zero, it is not the result of any math.
perfect , so is there a way to hide such extremely small error from the generated equation ? because same numbers on Excel don't give (Show) such small errors ,
so i assume i can put a limit for the accuracy of something ?!
and btw m I'm pretty sure about the slope , i used hand calculations and other programs and still only in Numbers it gives an extremely small value of b , though in any other graphing app , it always says that b is exactly 0 , i fuss it has to do with the "sow equation" functionality ?
I can only see the second of your screenshots at the moment. I really hope they fix this problem with the forums soon.
If you are dead set on using the trend line feature of the chart versus doing it the way I am suggesting, you are at the mercy of how computers do math. Numbers follows the IEEE standard for floating point math. Small errors like you are seeing are part of how computers do math when the standard is followed.
You already have the data points. Why use the trend line on the chart when you can simply connect the data points with lines and create your own trend line? If you want the formula to be shown on the chart instead of in the legend (the only reason I can see for using the chart trend line), create a one-cell table, put a formula for the trend line in that one cell, and put that one-cell table wherever you want it on top of the chart.
Numbers follows the IEEE standard for floating point math. It does not massage the results to get rid of the extremely small rounding errors that often creep in. It follows the standard and those little errors are part of the standard. You will see the same thing from some formulas; you expect the result to be zero but it is instead a really small number. It is not what you expected but it is the correct answer when going by the standard. Microsoft Excel, on the other hand, massages the results to better meet customer expectations; it will guess that the result should have been zero and will set it to zero. Neither way is wrong. Following the standard means you will get the exact same result from all software that follows the same standard, even if the result is "wrong". Massaging the results makes people happy (you expect a zero, you want to see a zero) but sometimes a correct small result will get massaged away (the true answer was a small number but gets turned into a zero) and sometimes the answer you get in one app will not be the same when you use another (they do not massage the result in the same way). In a formula in a table, you can ROUND to get rid of small results. In the trend line on a chart, you have no such option.
How to set the intercept of the best-fit line to be 0 ON the chart
