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.