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]

I think this is what you want but there are some mismatches.

Book1
	C	D	E	F	G	H	I
1					answer	wanted result	check
2	B5	3/20/17		R	0.285714	0.28570	FALSE
3	B1	3/21/17		0			TRUE
4	B0	3/22/17		0			TRUE
5	B5	3/23/17		R	0.200000	0.20000	TRUE
6	B0	3/24/17		0			TRUE
7	B0	3/25/17		0			TRUE
8	B0	3/26/17		0			TRUE
9	B0	3/27/17		0			TRUE
10	B0	3/28/17		0			TRUE
11	B0	3/29/17		0			TRUE
12	B0	3/30/17		0			TRUE
13	B0	3/31/17		0			TRUE
14	B0	4/1/17		0			TRUE
15	B0	4/2/17		0			TRUE
16	B0	4/3/17		0			TRUE
17	B0	2/4/17		R	0.000000	0.00000	TRUE
18	B0	4/5/17		0			TRUE
19	B0	4/6/17		0			TRUE
20	B0	4/7/17		0			TRUE
21	B5	4/8/17		0			TRUE
22	B0	4/9/17		0			TRUE
23	B0	4/29/17		0			TRUE
24	B0	4/11/17		0			TRUE
25	B0	4/12/17		0			TRUE
26	B0	4/13/17		0			TRUE
27	B0	4/14/17		0			TRUE
28	B0	4/15/17		R	0.125000	0.11110	FALSE
29	B0	4/16/17		0			TRUE
30	B0	4/17/17		0			TRUE
Sheet1

Cell Formulas
Range	Formula
G2:G30	G2	=IF(F2:F30="R", IFERROR(MAP(D2:D30,LAMBDA(m,SUM((D2:D30>=m-7)*(D2:D30<=m+7)*(C2:C30<>"B0")*(m<>D2:D30))/ COUNTIFS(D2:D30,">="&m-7,D2:D30,"<="&m+7,D2:D30,"<>"&m))),0),"")
I2:I30	I2	=G2:G30=H2:H30
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I30	Cell Value	=FALSE	text	NO

 

[Newbie73]

Thank you! The first one is just because I did only 4 digits decimal cases, last one is just a mistake on my manual maths that I just double check so that formula works!

Say that I would adapt that formula to another version, instead of within 7 days of the date would be between 8 and 14 days, how would I change it in the formula?

Thank you so much!

 

[Cubist]

I've added LET to make the references more manageable, but you would change the -7 (# days before) and +7 (# days after).

=IF(F2:F30="R",MAP(D2:D30,LAMBDA(m,LET(c,C2:C30,d,D2:D30,k,(d>=m-7)*(d<=m+7)*(m<>d),IFERROR(SUM(k*(c<>"B0"))/SUM(k),0)))),"")

 

[Newbie73]

I've added LET to make the references more manageable, but you would change the -7 (# days before) and +7 (# days after).

=IF(F2:F30="R",MAP(D2:D30,LAMBDA(m,LET(c,C2:C30,d,D2:D30,k,(d>=m-7)*(d<=m+7)*(m<>d),IFERROR(SUM(k*(c<>"B0"))/SUM(k),0)))),"")

Amazing, thank you so much for your help!

 

[Cubist]

You're welcome.

 

[Newbie73]

Hello, sorry I've opened a new thread today without realising I could still use this one! Your formula:

=IF(F2:F30="R",MAP(D2:D30,LAMBDA(m,LET(c,C2:C30,d,D2:D30,k,(d>=m-7)*(d<=m+7)*(m<>d),IFERROR(SUM(k*(c<>"B0"))/SUM(k),0)))),"")

Is working great, but it's really heavy on the spreadsheet especially as there's a few variations I need to do with the same formula. It's probably a long shot but is there perhaps a easier or more efficient way of doing it or with some change into the formula? As the loading/Calculating time after using has massively increased.

Sadly I know that excel struggles with more data and some formulas (Something like SQL would be better) but that's not an option so just thought it giving it a try and see if something could be done to it

Thanks and no worries if not!

 

[Cubist]

Is your date always in sequential order with no repeats like 1/1/24, 1/2/24, 1/3/24, etc...?

 

[Newbie73]

Is your date always in sequential order with no repeats like 1/1/24, 1/2/24, 1/3/24, etc...?

It's order from oldest to younger date but doesn't have necessary every day if that matters but there can be cases where theres two or more events in the same date, so it would repeat itself 2 or 3 times next.

Test.xlsx

This is another example I had create to a thread, you can see an example for the dates on Column A. Do let me know if you need anything and if nothing can be done about no worries thank you anyway

 

[Cubist]

I don't see a more efficient way with the formula. For each cell in the date column, it's checking against the entire date column so say you have 100K rows, so it's checking (100K)^2 times plus other computations. Are you open to the VBA option? That might be more efficient.