Exponential Trend With Missing Numbers

[lbird2]

OK, I think I have a tough one. Probably not for you guys!

Let's say I have the following data set in two rows where the top row are the x's and the second row the y's. Like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]1530[/TD]
[TD="align: center"]1418[/TD]
[TD="align: center"]1046[/TD]
[TD="align: center"]1088[/TD]
[TD="align: center"]1137[/TD]
[TD="align: center"]1045[/TD]
[/TR]
</tbody>[/TABLE]

Assume the first cell is A1.
The following formulas will get me the two values I need to calculate an exponential trendline y = c *e ^(b * x)

c=EXP(INDEX(LINEST(LN(A1:F1),A2:F2),1,2))
b=INDEX(LINEST(LN(A1:F1),A2:F2),1)

If I do this in a chart, I will match. In a chart if I remove a value, say the 1046, it will recalculate c and b and display the adjusted trend.

The formula however will give me an error.

Now finally to my question: How can I alter my formulas to recalculate properly if I remove one or more values?

I hope I stated that clearly enough

Thanks.

 

[shg]

LINEST and a lot of other math functions are intolerant of non-numeric values; charting is more forgiving.

Short of using VBA to calculate a packed array, you just need to close up the gaps.

 

[lbird2]

For those who might be interested, I found some discussion on this topic at the site below. Now I'm just trying to make sense of it.

https://newtonexcelbach.wordpress.com/2011/05/14/using-linest-on-data-with-gaps/

 

[shg]

OK, here's a way:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td][/td][td]

b​

[/td][td]

log(c)​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

1530​

[/td][td]

1418​

[/td][td]

1046​

[/td][td]

1088​

[/td][td]

1137​

[/td][td]

1045​

[/td][td][/td][td]

-0.07227​

[/td][td]

7.340073​

[/td][td]H2:I2: {=LINEST(LN(INDEX(v(CloseColumns(A1:F2)), 2, 0)), INDEX(v(), 1, 0))}[/td][/tr]

[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1540.825​

[/td][td]I3: =EXP(I2)[/td][/tr]

[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td][/td][td]

b​

[/td][td]

log(c)​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

1530​

[/td][td]

1418​

[/td][td][/td][td]

1088​

[/td][td]

1137​

[/td][td]

1045​

[/td][td][/td][td]

-0.07822​

[/td][td]

7.395596​

[/td][td]H7:I7: {=LINEST(LN(INDEX(v(CloseColumns(A6:F7)), 2, 0)), INDEX(v(), 1, 0))}[/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1628.796​

[/td][td]I8: =EXP(I7)[/td][/tr]
[/table]

It uses two UDFs: CloseColumns, which closes up the gaps for missing numbers,

Function CloseColumns(r As Range, _
                      Optional iMode As Long = 1) As Variant
  ' shg 2015
  ' Returns a variant array comprising the filled rows of r

  ' iMode
  '   1     keep numbers
  '   2     keep strings
  '   3     keep numbers & strings
  '   4     keep Booleans
  '   5     keep Booleans & numbers
  '   6     keep Booleans & strings
  '   7     keep everything except empty & errors

  Const iNum        As Long = 1
  Const iStr        As Long = 2
  Const iBoo        As Long = 4

  Dim av            As Variant
  Dim avOut         As Variant

  Dim iR            As Long
  Dim iW            As Long
  Dim iTop          As Long
  Dim jR            As Long
  Dim jTop          As Long
  Dim jW            As Long

  av = r.Value2
  iTop = UBound(av, 1)
  jTop = UBound(av, 2)

  Do While jR < jTop
    jR = jR + 1
    For iR = 1 To UBound(av, 1)
      Select Case VarType(av(iR, jR))
        Case vbEmpty, vbError
          Exit For
        Case vbDouble
          If (iMode And iNum) = 0 Then Exit For
        Case vbString
          If (iMode And iStr) = 0 Then Exit For
        Case vbBoolean
          If (iMode And iBoo) = 0 Then Exit For
      End Select
    Next iR

    If iR <= iTop Then
      ' close up
      For jW = jR To jTop - 1
        For iR = 1 To UBound(av, 1)
          av(iR, jW) = av(iR, jW + 1)
        Next iR
      Next jW
      jTop = jTop - 1
      jR = jR - 1
    End If
  Loop

  ReDim avOut(1 To UBound(av, 1), 1 To jTop)
  For jR = 1 To jTop
    For iR = 1 To UBound(av, 1)
      avOut(iR, jR) = av(iR, jR)
    Next iR
  Next jR

  CloseColumns = avOut
End Function

And a wonderful little function V, which avoids need to run CloseColumns more than once in a formula:

Public Function V(Optional vInp As Variant) As Variant
  ' Stephen Dunn 2002
  Static vSav       As Variant

  If Not IsMissing(vInp) Then vSav = vInp
  V = vSav
End Function

There a complementary function CloseRows if anyone is interested.

 

[lbird2]

Wow shg! Thanks for going above and beyond. I'll give this a shot tomorrow. I'll let you know how it works.

 

[lbird2]

Worked like a charm. Brilliant!

Thanks again.

 

[shg]

You're welcome.

 

[PanzerPixel]

Hi shg,

I realise this is 4 years old... but is there a way to do this if the X and Y aren't in adjacent rows?
I'm trying to show growth rates over time at 50+ sites in a way that I can easily update as new data is available.
I'm using a fixed range of consecutive years, not every site will have data for each year.

This is what I'm trying to do -
[TABLE="width: 844"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 10"]YEAR[/TD]
[TD="colspan: 4"]GROWTH[/TD]
[/TR]
[TR]
[TD]SITE[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]Exp (%)[/TD]
[TD]Exp R^2[/TD]
[TD]Linear[/TD]
[TD]Lin R^2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]182[/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]193[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]199[/TD]
[TD][/TD]
[TD="align: right"]2.6[/TD]
[TD="align: right"]0.7404[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.7424[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]21.3[/TD]
[TD="align: right"]0.9293[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.7089[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1426[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3125[/TD]
[TD="align: right"]3561[/TD]
[TD="align: right"]3987[/TD]
[TD="align: right"]4521[/TD]
[TD="align: right"]21.4[/TD]
[TD="align: right"]0.9595[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]0.9965[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]436[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]625[/TD]
[TD="align: right"]734[/TD]
[TD="align: right"]821[/TD]
[TD][/TD]
[TD="align: right"]956[/TD]
[TD="align: right"]1007[/TD]
[TD="align: right"]1245[/TD]
[TD="align: right"]11.6[/TD]
[TD="align: right"]0.9824[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]0.9643[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!! I've looked everywhere online and your post was the closest I've gotten!