Adding data labels to a trend line and is this the right approach?

English
#1

Hey guys is it possible to add data labels to an exponential trend line I have added into my graph?
I have searched here and there but cannot find an answer?
And if I have actual declining reward rates plotted for 4 previous months and wish to try and look into the future possibility over say 12 months, is adding the exponential trend line the correct way to achieve this desired result?

And then assuming that this is even possible how do I extract the series of data labels numbers to then use in another predictive spreadsheet?

#2

Please upload your ODF type sample file here.

#3

Thank you for responding
Here it is in a simple form

Rate predictions via Trendline.ods (18.1 KB)
I would like to extract the data points at each month along the trendline for use in another predictive model.

#4

Here is a tip with the function LOGEST():
Rate predictions via Trendline Zizi64.ods (26.0 KB)

If you want to make this solution dinamic, then you need use some tricks for the newly entered values (the unction INDIRECT(), and/or a Helper table for the estimation the coefficients).
Otherwise you must rework the array formula of the estimation table and the column C manually for every new data entered into column B.

#5

I hope this minimal additions in the modified file will help you:
Rate predictions via Trendline Zizi64.ods (32.9 KB)

#6

Zizi This is fantastic and I thank you for posting this info.
I will not pretend to understand the workings of it but it seems to provide exactly what I am looking for. And yes I somehow need to make it dynamic?

I have just read up a little on array formulas and much of it goes over my head but I will try to find a couple of videos that might help explain the basics so that I might gain at least a working understanding of the process.
From your first response I have increased the months from 24 to 36 and can see the results but I have a question if I may.
Correct me if I am wrong here but what I am trying to do is predict the future reward rates based on the ACTUAL latest 4 months results that I have showing in Column B
BUT
What happens if the next result in January (11) is different to what the prediction is? How do I adjust for this and make a corrected trendline?
Let’s say the January (11) result is 4 instead of the predicted 4.966?
I inserted another trendline at the “4” just to see the effect??
But what would I need to do in the above case to recreate a newly adjusted actual data point with the trendline showing as you have it now with the data labels?

#7

Zizi Is the exponential trendline the correct approach for what I am trying to achieve ie predict the future based on the actual results of the last 4 months??
Is there a better or simpler way although your chart looks like it could be about right but trends can and will change I guess, so having a dynamic setup would be most helpful.

#8

Rate predictions via Trendline.ods (18.1 KB)

#9

Why only four data? More data gives you more precise result.
And maybe you must change the type of the regression based on more data. The type detection never will not work automatically, only the determining the coefficients will work automatically.
(If you have only three pieces of data, they can can determine even a Circle…)

#10

Zizi
I have chosen to use the most recent 4 months as I feel the “trend” has more reliably established itself and would be a more accurate database to rely on moving forward. Some of the earlier rates had wilder adjustments but that now seems to have settled into a more identifiable and hopefully reliable pattern (trend)

#11

You MUST study the function INDIRECT() and the relevant methods to make this solution dinamic, and create an automatic recalculation for the new data rows.

#12

Estimating (extrapolating) 14 extra (non existing) data based on only four (existing) data will cause very high value of uncertainty.

1 Like
#13

It is better to use COLUMNS for the data of the graph instead of the ROWS.

#14

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???

#15

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

#16

…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.

#17

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.

#18

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.

#19

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)

#20

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.