Hello everyone, I have this formula that works perfectly but I was just wondering if there is a more efficient way of achieving the same? I know that Excel can struggle with loads of data, but due to my company I need to carry out work in it instead of SQL or something similar, so was just wondering as these formulas are really hitting hard the spreadsheet in terms of loading and calculating for any change. Maybe there isn't but just thought in giving it a try with more knowledgeable people in it
Example sheet:
Test.xlsx
A is Dates
C is IDs
D is grades
F to I different list of IDs that some will match and shows if a ID is "R" or "IR"
K and L checks if the IDs on C are present in between F and I
And then the problem starts with a heavy (but required formula)
It checks if an ID which is "R" (K) and then compared the chanceof the animals having a Grade (Column D) different than "B0" and the chance of that happening between 7 days both ways of the respective date in A.
Example of the formula:
=IF(K2:K10000="R",MAP(A2:A10000,LAMBDA(m,LET(c,D2:D10000,d,A2:A10000,k,(d>=m-7)*(d<=m+7)*(m<>d),IFERROR(SUM(k*(c<>"B0"))/SUM(k),0)))),"")
As I said the formula work wonders exactly as I want to, but it's really heavy plus it has small different variations as you can see after Column N and the original spreadsheet has even more variations and a bigger data pool to do it. Maybe there's nothing that could do the same but easier on the CPU, but just thought in giving it a try.
Thank you in advance for your time and let me know if I didn't explain myself correctly or if you need any more information.
Example sheet:
Test.xlsx
A is Dates
C is IDs
D is grades
F to I different list of IDs that some will match and shows if a ID is "R" or "IR"
K and L checks if the IDs on C are present in between F and I
And then the problem starts with a heavy (but required formula)
It checks if an ID which is "R" (K) and then compared the chanceof the animals having a Grade (Column D) different than "B0" and the chance of that happening between 7 days both ways of the respective date in A.
Example of the formula:
=IF(K2:K10000="R",MAP(A2:A10000,LAMBDA(m,LET(c,D2:D10000,d,A2:A10000,k,(d>=m-7)*(d<=m+7)*(m<>d),IFERROR(SUM(k*(c<>"B0"))/SUM(k),0)))),"")
As I said the formula work wonders exactly as I want to, but it's really heavy plus it has small different variations as you can see after Column N and the original spreadsheet has even more variations and a bigger data pool to do it. Maybe there's nothing that could do the same but easier on the CPU, but just thought in giving it a try.
Thank you in advance for your time and let me know if I didn't explain myself correctly or if you need any more information.
