ufers.blogg.se

NewFormula = Replace(newFormula, Application.DecimalSeparator, ". NewFormula = Replace(newFormula, "y =", "") 'Strips "y =" 'Format the new formula to be understanding by Evaluate() function Trendline.DisplayEquation = trendlineWasVisible 'If you have set the precision, you can set it back here

'Put back the trendline equation like it was before 'Add parenthesis if the formula finishes with a superscript char If preChar = "x" Or preChar = "e" Or IsNumeric(preChar) Or preChar = ")" Then If preChar = "x" Or preChar = "e" Or preChar = ")" Or IsNumeric(preChar) Then 'If we need to add a "*" before the actual char If Not bCharIsPower And bPreCharIsPower Then

If bCharIsPower And Not bPreCharIsPower Then Alternatively, in cell A2 enter LOG(B2) or LN(B2) and fill it down to A8 and use non-array formulas SLOPE(C2:C8,A2:A8) INTERCEPT(C2:C8,A2:A8) and youll get 38.50 or 16.72 as slope and -33. 'Look if the char in written in superscriptīCharIsPower = (i).Font.Superscript Dont type the curly brackets hold Ctrl+Shift while pressing Enter and Excel puts them there to show these are curly brackets. TrendlineWasVisible = trendline.DisplayEquationįor i = 1 To Ĭhar = Mid(, i, 1) 'get the actual char 'Keep track of the style of the trendline. If trendline.Type = xlLogarithmic And xValue <= 0 Then 'If equation is logarithmic and x <= 0, return 0 'If the trendline is a moving average, return 0 Public Function calcTrendlineValueForX(trendline As trendline, xValue As Double) As Double Result = calcTrendlineValueForX((1).Trendlines(1), x) 'I have a sheet with one scatter plot in sheet "graph" You may want to set the precision of the Datalabel to suit your needs. I found a solution that works for every type of trendlines (exept for moving average of course).