I'm new to Stack Overflow and need help with optimizing a Google Sheets formula. I'm working on a statistic called "Friends and Enemies" from the game show TWOW. This statistic is calculated by taking the average of the absolute differences between the scores of two contestants. Higher differences (higher percentiles) indicate that the contestants have opposing performances and are labeled as "Enemies," while lower differences (lower percentiles) mean the contestants have similar performances and are labeled as "Friends."
For example, here are the scores for two contestants:
The process involves subtracting each pair of scores, which results in differences of 10%, 70%, 20%, 50%, 20%, 10%. The average of these differences is 30%.
Currently, I calculate this statistic for each pair of contestants using the following formula:
=IF(H$2=$A3,,ARRAYFORMULA(AVERAGE(ABS(VLOOKUP($A3,$A$3:$G$13,SEQUENCE(6,1,2),0)-VLOOKUP(H$2,$A$3:$G$13,SEQUENCE(6,1,2),0)))))
This formula, however, is only applicable to a single cell and becomes cumbersome and slow when scaled up to multiple contestants, causing significant lag due to the need to drag the formula across hundreds of cells.
I have tried to encapsulate the entire operation in a single formula using ARRAYFORMULA
, ARRAY_LITERAL
, and other array-related functions (including those for LAMBDA
), as well as combination formulas like INDEX+MATCH
and various LOOKUP
functions, but none have successfully simplified the process.
Here is the link to the spreadsheet: Google Spreadsheet
You may try this in Cell_H16:
=let(x,A16:A26,map(x,lambda(Σ,map(torow(x),lambda(Λ,if(Σ=Λ,,let(Γ,lambda(z,xlookup(z,x,B16:G26)),index(average(abs(Γ(Σ)-Γ(Λ)))))))))))