Forum Discussion

ffi82's avatar
ffi82
Seasoned Newcomer
2 years ago

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:
=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
),)))
  • ffi82's avatar
    ffi82
    Seasoned Newcomer
    Known issues:

    • Shows levels that are less then 1 for armies with 1 level 1 unit that require 5 army points. (ex.: OL 0.5)
    • Due to the way the cnctaopt link is mapped: in the Offense view , if units that have the Transport upgrade (Guardian, Paladin / Reckoner, Venom) are loaded, the link / formula will only consider the "passenger". Hint: link has unused army points (this can also be for luring units though... one might keep free army points on purpose). Workaround: manually add the transporters in the link.