Zizi yes I will research INDIRECT()
But in the case of the January (11) result turning out to be “4” how do I manually adjust what you have generously provided me with? Or am I missing the point somewhere here?
If I delete the 4.966 and add 4 into B6 then what do I do as I don’t seem to be able to delete or drag down E6 and F6 ??? Message is You cannot change part of an array???
Ok yes I understand that this is probably an uncertain trendline but as the months progress and if the actual trend turns out to be something approaching what is now suggested then it will become more reliable I guess. ie no exaggerated moves as in the earlier months
…and I suggest you to study the function OFFSET() Then you will able to create a dynamic table for the determination of the actual (new) coefficients from the growing data range.
Ok thank you for this advice although I cannot determine any difference in the outcome?
But I will bow to your much superior knowledge and experience in trying to assist me.
And you must study the usage of the Array Functions.
In a few words:
Select all of cells in the coeff. determining range - without the textual header - modify the Formula, and finally (on Windows) hit Shift-Ctrl-Enter. Then the new coefficients will be appeared.
The usage of the OFFSET(), INDIRECT() and other simple spreadsheet functions, and the usage of the Array Functions are a basic knowledge of the Spreadsheet applications.
Zizi I have created this chart and inserted 2 Trendlines Power and Logarithmic. How can I simply see and extract the data labels at the monthly grid points please? It does not seem possible to Add Data Labels to a trendline which to me seems strange? I am not considering making this somehow dynamic as previously discussed as it is probably above my abilities.
Trendline Predictions.ods (16.8 KB)
It is not possible - in my opinion.
That is the reason why I suggested you that you can create the trendline data outside the graph, and you can draw it as a new (second) exponential line - after determined the SAME coefficients of the regression curve what the built-in trendline feature can show you.
.
I suppose it: the inserted trendline contains many more estimated points than you have in the x cell range. If the Calc would insert labels for every points, those can not appear clearly (too many labels).
.
And these are estimated “points” (but they are not real points) contain a level of uncertainty. You can not sure, that “they are must be on that place really”. The reliability of the trendlines depends of the count and the correlation value of the real (esxisting) points of the original graph.
The data about the precisity of the curve fitting is present in the table of the coefficient determination. It is a number which approximates the value 1 from below.
…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.