Formula Help

[Newbie73]

Hello, I have the following formula:

=MAP(F2:F9904,LAMBDA(x,IF(x="R",LET(c,C2:C9904,r,ROW(c),s,ROW(x),y,(r>=s-8)*(r<s-0)+(r>s+0)*(r<=s+8),SUM(y*(c>"B0"))/SUM),"")))

And this is the example spreadsheet:

Example.xlsx

Explaining the best I can, if F="R" then check the 8 rows above and below (ignoring the row itself where F="R") and if in the checked rows C is different than "B0" count it and divide it by the total number of rows checked.

This provides me the results on G which is perfect. I was wondering if this formula could be changed slighlty to instead of counting 8 rows above and below (if C different than the value "B0"), to count if C is different than the value "B0" and the date on Column D is within 7 days

So in the example spreadsheet, F2 ="R" so count all the rows that are within 7 days of D2 and that C is different than the value "B0"

I've added the wanted results on Column I

Thank you!

 

[Cubist]

If I were to run an adaption where I only wants days after (or before but not together). Is there a easy way of doing it? Would I change this numbers in the code:

You'd add the parameters. As mentioned before, if omitted, they default to 7's, but you can specify it.

=CalculateProb(B2:B2200,A2:A2200,C2:C2200,"R","B0",0,7)

For the second case, you'd put -7 because in the code it's already subtracting so minus minus is plus.

=CalculateProb(B2:B2200,A2:A2200,C2:C2200,"R","B0",-7,14)

 

[Newbie73]

Got it! The 0,7 works perfectly but with the -7 it's giving me slighlty different results as it happened before you've changed something in the OR code.

For example, (getting the between 7 and 14 days) this is using days after only where column E is

=CalculateProbOR(A1:A9999,B1:B9999,C1:C9999,"R","B0",-7,14)

and column F is

=IF(C1:C9999="R",MAP(A1:A9999,LAMBDA(m,LET(c,B1:B9999,d,A1:A9999,k,(d>=m+7)*(d<=m+14)*(m<>d),IFERROR(SUM(k*(c<>"B0"))/SUM(k),0)))),"")

The older formula has it correct, within 7 to 14 days after there's one and it matches the conditions requested, being 0% for the second as there isn't nothing afterwards but the OR formula with -7,14 is giving 50% in each?

12/01/2004​	B1	R		50%​	100%​
22/01/2004​	B5	R		50%​	0%​

 

[Cubist]

You're not using the OR version correctly. This

=CalculateProbOR(A1:A9999,B1:B9999,C1:C9999,"R","B0",-7,14)

means (d>= m-14)(d<=m--7)+(d<=m+14)(d>=m-7). The OR version is used when you have a "+" , which makes no sense when you pass a negative number.

If you want the result in col F, then would use the other version.

=CalculateProb(A1:A9999,B1:B9999,C1:C9999,"R","B0",-7,14)

 

[Newbie73]

You're not using the OR version correctly. This

=CalculateProbOR(A1:A9999,B1:B9999,C1:C9999,"R","B0",-7,14)

means (d>= m-14)(d<=m--7)+(d<=m+14)(d>=m-7). The OR version is used when you have a "+" , which makes no sense when you pass a negative number.

If you want the result in col F, then would use the other version.

=CalculateProb(A1:A9999,B1:B9999,C1:C9999,"R","B0",-7,14)

Sorry! Just me being stupid, thank you so much everything is working as intended and much faster!!

 

[Cubist]

If dateArr(j, 1) >= currentDate - daysBefore And dateArr(j, 1) <= currentDate + daysAfter Then

It's probably easier on the brain to switch the - to a + in the code on this line so that when you pass a negative number, it means 7 days before and +7 is 7 days after. It's currently opposite right now.

 

[Newbie73]

Hello Cubist! Sorry to bring this thread back up, but just notice that sometimes one of the cell might be empty and that I believe creates a #Value error. Is there any chance that you could tell me what to add to the codes to ignore the rows where for example on that same row the value of column I is zero/blank?

 

[Newbie73]

Nevermind I've managed

Function CalculateProb(dateRange As Range, gradeRange As Range, rRange As Range, _
                       checkString As String, gradeCheckString As String, _
                       Optional daysBefore As Long = 6, Optional daysAfter As Long = 6) As Variant
                                        
    Dim lastRow As Long, i As Long, j As Long
    Dim dateArr() As Variant
    Dim gradeArr() As Variant
    Dim rArr() As Variant
    Dim resultArr() As Variant

    ' Store values in the arrays
    dateArr = dateRange.Value
    gradeArr = gradeRange.Value
    rArr = rRange.Value
    ReDim resultArr(1 To UBound(dateArr, 1), 1 To 1)

    For i = LBound(rArr, 1) To UBound(rArr, 1)
        If rArr(i, 1) = checkString Then
            If IsDate(dateArr(i, 1)) Then
                Dim currentDate As Date
                currentDate = dateArr(i, 1)

                Dim totalCount As Long
                totalCount = 0
                Dim gradeCount As Long
                gradeCount = 0

                ' Count gradeCheckString and total # days
                For j = LBound(dateArr, 1) To UBound(dateArr, 1)
                    If dateArr(j, 1) > currentDate + daysAfter Then Exit For
                    If dateArr(j, 1) >= currentDate - daysBefore And dateArr(j, 1) <= currentDate + daysAfter Then
                        If dateArr(j, 1) <> currentDate Then
                            totalCount = totalCount + 1
                            If gradeArr(j, 1) <> gradeCheckString Then
                                gradeCount = gradeCount + 1
                            End If
                        End If
                    End If
                Next j

                ' Calculate probability resultArr
                If totalCount > 0 Then
                    resultArr(i, 1) = gradeCount / totalCount
                Else
                    resultArr(i, 1) = 0
                End If
            Else
                resultArr(i, 1) = ""
            End If
        Else
            resultArr(i, 1) = ""
        End If
    Next i

    ' Return the result array
    CalculateProb = resultArr
End Function