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!
=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!