ffi82
2 years agoSeasoned Newcomer
Offense level Calculator
Based on info provided by @EE_Elephterion, i made a Google sheet with a formula:
https://docs.google.com/spreadsheets/d/1jg94ayqEyai9Ms9H8JSSJb8cijTww7FVuE0NObNzl_8/
Required data: list of units and their level
(army units are filled in from a CnCTAOpt long link that has to be inserted in the first cell... A1)
________________
Short version formula:
Long version formula (H2:H would be the range with multiple cnctaopt links):
https://docs.google.com/spreadsheets/d/1jg94ayqEyai9Ms9H8JSSJb8cijTww7FVuE0NObNzl_8/
Required data: list of units and their level
(army units are filled in from a CnCTAOpt long link that has to be inserted in the first cell... A1)
________________
Short version formula:
=TRUNC(100*PERCENTRANK.INC(TAS_Pool,SUM(UnitScore),10),2)
Long version formula (H2:H would be the range with multiple cnctaopt links):
=MAP(H2:H,LAMBDA(cnctaopt,IF(cnctaopt<>"",TRUNC
(100*
PERCENTRANK
(TAS_Pool,
SUM
(ARRAYFORMULA
(IF
(ISBLANK
(MAP(QUERY(TRANSPOSE(SPLIT(REGEXREPLACE(REGEXREPLACE(RIGHT(REGEXREPLACE(INDEX(SPLIT(cnctaopt,"~"),1,5),"]", ""),36)&"","(?s)(.{1})","$1"&"⏏"),"'","''"),"⏏")),"where Col1 <>'.'"),LAMBDA(unit,FILTER(IF(MID(cnctaopt,45,1)="G",ArmyPointCostGDI,ArmyPointCostNOD),cnctaopt_OffenseMap=unit)))),
,
IFERROR(
MAP(QUERY(TRANSPOSE(SPLIT(REGEXREPLACE(REGEXREPLACE(RIGHT(REGEXREPLACE(INDEX(SPLIT(cnctaopt,"~"),1,5),"]", ""),36)&"","(?s)(.{1})","$1"&"⏏"),"'","''"),"⏏")),"where Col1 <>'.'"),LAMBDA(unit,FILTER(IF(MID(cnctaopt,45,1)="G",ArmyPointCostGDI,ArmyPointCostNOD),cnctaopt_OffenseMap=unit)))*
1.2^
(QUERY(TRANSPOSE(SPLIT(REGEXREPLACE(INDEX(SPLIT(cnctaopt,"~"),1,5),"]", "!"),".abcdefghijklmnopqrstuvwxyz!")),"offset "&(COUNT(TRANSPOSE(SPLIT(REGEXREPLACE(INDEX(SPLIT(cnctaopt,"~"),1,5),"]", "!"),".abcdefghijklmnopqrstuvwxyz!")))-
COUNTA(MAP(QUERY(TRANSPOSE(SPLIT(REGEXREPLACE(REGEXREPLACE(RIGHT(REGEXREPLACE(INDEX(SPLIT(cnctaopt,"~"),1,5),"]", ""),36)&"","(?s)(.{1})","$1"&"⏏"),"'","''"),"⏏")),"where Col1 <>'.'"),LAMBDA(unit,FILTER(IF(MID(cnctaopt,45,1)="G",ArmyPointCostGDI,ArmyPointCostNOD),cnctaopt_OffenseMap=unit))))))-
1+
MAP(QUERY(TRANSPOSE(SPLIT(REGEXREPLACE(REGEXREPLACE(RIGHT(REGEXREPLACE(INDEX(SPLIT(cnctaopt,"~"),1,5),"]", ""),36)&"","(?s)(.{1})","$1"&"⏏"),"'","''"),"⏏")),"where Col1 <>'.'"),LAMBDA(unit,FILTER(IF(MID(cnctaopt,45,1)="G",OffenseLevelModifierGDI,OffenseLevelModifierNOD),cnctaopt_OffenseMap=unit)))
)*
2
,0)
)
)
),10
),2
),)))