…And you always will wait for a Help from somebody???
.
Here is a dynamic version of may sample. I have used the function OFFSET() in the coeff. determining table, based on the COUNT() of the input data.
You must fill-in the input data table continously started in the cell B2.
Try enter a new data, or delete the last data.
Rate predictions via Trendline Zizi64_DinamicdDataHandle.ods (34.4 KB)
If one cannot see data labels on trendlines then this is a mistake by LO surely? Calc has to know what these values actually are to place them on the trendline at the appropriate grid line so why not make them visible to the user? Makes little sense to me???
To be able to create the trendline data outside the graph I would need to be able to see the trendline DATA inside the graph which is not visible so I cannot do this. This is the problem in a nutshell and if I could see the trendline data inside the graph then I would not have even posted this question in the first place. These two trendlines seem to have high co efficient numbers so the actual data is highly correlated to the proposed trendlines, yes?
As you can see in my last sample file, it is possible.
- You can create the trendline data in the Calc cell range, based on the regression coeffuicients re-created in the small table by usage the array function LOGEST().
- Then you can “calculate” some estimated points for the gaph based on the equation determined by the coefficients.
- Finally, you can draw the “virtual trendline” (as a second curve in the graph) based on the estimated trendline data.
- And my last sample file shows you how you can make this tast dynamic (aotomatic) for the newly entered data points. (all of the coefficients, and the data of the virtual trendline will be recalculated automatically when you enter a new data, or when you modify one or more of the existing data. (And the lines will be redraw, too. Try it with a data which can not fit on the previous trendline. A bigger change will be appeared more clearly.)
Tha value of the F (mathematics called the "precisity of the curve fitting to " F ") in my sample file is not too high.
See the meaning of the “F” here:
The value 0.998… can be more higher (0,9999765 or more) when the curve can fit better to the blue existing points. (Of course the red estimated points ARE located precisely ON the CURVE - because they was calculated from the curve equation.)
…And - I can say it again - four points is too low count of the existing points. As I said it: you can fit even a circle or many other different type of curves PRECISELY (with F = 1.000000 value) to three points.
Lets see if I have my understanding correct here. It is possible to create a trendline or more than 1 trendline from a set of given points in
LO CALC chart BUT
These trendlines will not show data labels natively because LO Calc does not have this simple ability? (Should have and my original post would not be needed). So the only way to add a trendline to a chart is to become creative with arrays and offsets etc? Requiring a higher level of skill and understanding in creating “virtual trendlines” to which one can then add data labels quite simply? As per your excellent DYNAMIC example chart which was great to see in operation as the changes occurred.
I did not say it was too high. I was commenting on MY 2 trendlines as being high but at 0.998 then my ones are actually low maybe???
Please have a look at the last chart I posted as it has two trendlines projected out to month 36 and there are 9 ACTUAL DATA points used to create these 2 trendlines not 4.
And interestingly in my experiments here there is very little difference at month 36 between the outcomes of either the 4 point or the 9 point chart trendline???
Trendline Predictions.ods (24.9 KB)
Zizi we all have our own innate abilities and some are better than others. I am doing my very best with trying to put this together and understand what it is that I need to understand to make this happen. I do not have the available time to turn this endeavour into a university degree level of comprehension. I would just like it to provide the information of predicting the possible future rewards if possible. It should be a super simple operation as it creates the trendline.
I have played around with your chart that you kindly posted so a big thank you for that. It is impressive in that it is a dynamic chart and it is cool to see the trendline change in real time as a new data point is added. From my own charts I have discovered that the Logarithmic trendline appears to deliver a result much more in line with my expectation from the other modelling I have done so I have been trying to change the trendline to Logarithmic.
When I increase the months to 36 and drag the C19 cell down to fill the C column down to month 36 at C31 it all populates as expected. I have then changed the Data Range to $Sheet1.$A$2:$A$31 and $Sheet1.$B$2:$B$31
I then insert a new Logarithmic trendline which does not work as hoped due to the calculations still using the co efficient for the Exponential trendline? I guess???
So please how do I edit your chart to accept the Logarithmic co efficient for the Logarithmic trendline as you seem to have protected the cells so that they cannot be edited??
I realise that you do not have to help me with this as it is my problem so I do thank you for your efforts and I do appreciate your experienced advice.
Rate predictions via Trendline Zizi64_DinamicdDataHandle.ods (34.7 KB)
As I know it this is correct: Not possible to insert data labels to a built-in threndline. That is the reason why I suggested you to use your own (user defined) virtual trendline for this task.
How you created two trendlines for one dataset? I see that the sample file uses constant data embedded into the graph object.
My solution can not change the type of the regression automatically. But you must change the type the built-in regression curve manually, too.
Here is a comparison the precisity of the Logarithmic and Exponential fittings. See the R^2 values. I have choosed the exponential one, because you hav used that too.
.
R^2: regression - Whats the relationship between $R^2$ and F-Test? - Cross Validated
.
Rate predictions via Trendline Zizi64_DinamicdDataHandle3.ods (31.7 KB)
Zizi I have 9 actual data points and from there I added in two types of trendlines (not virtual trendlines) and yes obviously using this same 9 point data set as that is factual. I projected these out to month 36. So at this point everything is fine the trendlines look correct and the only thing missing are the data labels which cannot be done in Calc??? My choices are accept this shortcoming of Calc or embrace a much higher knowledge and understanding of next level mathematics? It is an obvious decision for me I guess as the maths are above my level but thank you.
Zizi Thank you Unfortunately the Regression link with algebra and cross validation is way beyond my grasp. As mentioned earlier I think from my experiments that the Logarithmic trendline is producing what seems to be the most accurate trendline to what I expect. So a simple question how do I simply change the formulas contained within cells E2 to G7 in your chart to reflect the co efficient data associated with the Logarithmic trendline in my chart. Thank you Please see my new chart with just 1 Logarithmic
Trendline Predictions.ods (17.4 KB)
trendline
Please upload a usable sample file what stores the data in two culumns of a CELL RANGE, but not embedded in the Graph.
.
Note:
There are 10 point in the table inside your attached graph, but there are drawn 9 of them (2…10 of the data points) only. It requests more usage of the OFFSET() or other tricks, if it is not an user error…
Zizi
I think this what you are asking for, with the chart and data hopefully. I only see 9 points 2 to 10 in the previous chart so I am not sure of your reference??? Could you expand on that if it is important?
Sample Logarithmic 9 points data.ods (21.5 KB)
hey @AussieWayne1
Are you shamed to set hidden all important formulas of analytic geometricals as embedded into graph? As the result in yours way i can see nothing to extrapolate any futured point.
Your points 1 and 2 (month 2, 3) are outside of any log(b)x-function! When you delete both, the 12th extrapolated point is aiming now to 2.040, not to 2.400 with both.
I don’t understand?
You asked for the raw data in columns which I included then I inserted a chart then I inserted a trend line???
Which part is not right please,?
What value has you for gradient, for deviation, for distributon, for accepted tolerance, for hypothetics, for spot check samplings, for error function, for probability integral?
Have a simple (!) look on LO-help onto RGP- and RKP-functions as a beginners intruduction of higher mathematicals and natural physicals you need to understand statistics.
Here is my modified sample file with the LOGEST and LINEST regression curves. The LINEST can calculate the coefficients of the Linear, of the Polinomial, and even of the Logarithmic curves.
.
You will not able to use the Calc effectively for these tasks without a higher level of knowledge of the mathematical background.
.
Rate predictions via Trendline Zizi64_DinamicdDataHandle4.ods (54.5 KB)