Calculate interval since last positive occurance

@CAntosh
I am trying to avoid creating another column. My desire is to have the formula calculate the value for column C so that I do not need to manually calculate it. I modified it for column C and it worked for existing rows but does not fill down when additional entries are added.

Originally Posted by glgold

@CAntosh
I am trying to avoid creating another column. My desire is to have the formula calculate the value for column C so that I do not need to manually calculate it. I modified it for column C and it worked for existing rows but does not fill down when additional entries are added.

My formula doesn't require another column? You can put it in C2. I used it in D2 in my sample to demonstrate that it returns the expected results. Paste the following in C2:

=IF([@[Ate it?]]="No","-",[@Date]-INDEX([Date],MATCH("yes",[Ate it?],0))-(SUM($C$2:$C2)-$C$2))

I tried it this way:

This code goes in the worksheet's VBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim lstObj As ListObject
Dim rColumn As Range
Dim sDate   As Date
Dim eDate   As Date
Dim xrow    As Long
' Get the table reference
Set lstObj = ActiveSheet.ListObjects("V_Feed")
 
' Get the column reference
Set rColumn = lstObj.ListColumns("Ate it?").DataBodyRange
If Target.Column <> rColumn.Column Then Exit Sub
Application.EnableEvents = False
sDate = 0: eDate = 0
For xrow = 1 To lstObj.DataBodyRange.Rows.Count
    If LCase(rColumn(xrow).Value) = "no" Then
        lstObj.DataBodyRange.Cells(Target.Row, 3).Value = 0
    ElseIf LCase(rColumn(xrow).Value) = "yes" And sDate = 0 Then
        sDate = lstObj.DataBodyRange.Cells(xrow, 1).Value
    ElseIf LCase(rColumn(xrow).Value) = "yes" Then
        eDate = lstObj.DataBodyRange.Cells(xrow, 1).Value
        lstObj.DataBodyRange.Cells(xrow, 3).Value = WorksheetFunction.Days360(sDate, eDate)
        sDate = lstObj.DataBodyRange.Cells(xrow, 1).Value
    End If
Next xrow
Application.EnableEvents = False
End Sub

For the average I set the formula as follows:

=AVERAGEIFS(V_Feed[Interval],V_Feed[Ate it?],"yes")

Another way. Array enter this in C2. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=IFERROR(IF($B2="Yes",$A2-SMALL(IF($B$2:$B2="yes",$A$2:$A2),COUNTIF($B$2:$B2,"yes")-1),"-"),7)

Without the array entry it is not calculating based the interval from the most recent yes.

Correct. I am not familiar with Mac.

From reading posts here the method is apparently version specific ... most commonly though ...

1. from edit mode press and hold the Cmd (daisy graphic?) while hitting Enter.
2. You will know it is properly committed when you see curly braces ({}) around the formula in the formula bar.
3. You do not type those in yourself.
4. If you do you will get an error.
5. Excel puts them in for you.

Originally Posted by glgold

@FlameRetired
Will the array dynamically enlarge as more rows of data are added?

I don't work that much with tables so I can't say with certainty, but since it dynamically filled down (as tables do) and the ranges increased accordingly I believe it will.

Try it and see.

Use this in C2 then drag down:

=IFERROR(IF(B2="No","-",A2-LOOKUP(2,1/($B$1:B1="yes"),$A$1:A1)),7)