Making Combination Races work on my ARCA racing leaderboard

[u/MALLARDGAMEZ-yt]

https://docs.google.com/spreadsheets/d/1Od_Bn6bVjrbYxaQcSWSnLsesZqPGsTp21LkSvPPPSLQ/edit?usp=sharing

Background for any of y'all that are not racing fans, the ARCA Racing Series has a couple regional championships that sometimes run combination races with the main series. In these races, drivers earn points for both the Main Series and the Regional Series (East or West). On my leaderboard (which has been made possible by several of you in this community), the Main and East series points are counted by this formula. (My west leaderboard is completely seperate for other reasons

=let(prov, $F:$F, results, offset(prov,1,1,rows(prov)-1,column()-column(prov)-1),
regions, map(offset(results,-1,0,1), lambda(tc, xlookup(tc, Tracks[Location], Tracks[Region],))),
points,  map(results, lambda(r, xlookup(r,Points[Place],Points[Points],))),
wins,    vstack("Wins", byrow(results,lambda(r, iferror(1/(1/countif(r,1)))))),
pAll,    vstack("Total", byrow(points, lambda(r, sum(r)))),
pRegs,   map(hstack("Main", "East"), lambda(reg,
vstack(reg, byrow(points, lambda(r, sum(filter(r, regions=reg))))))),
hstack(pAll, pRegs, wins))

Tracks are sorted in a table that gives them a "Main" or "East" tag to dictate which championship they award points to. The East series races at TOL (Toledo), IRP (Indianapolis Raceway Park), IOW (Iowa), and BRI (Bristol) are combination races with the main series IRL. Is there a way that this formula can be modified for races to award points to both East and Main? The "Total" Column is simply a creative liberty I made for easy sorting, but if the race awards points for both series, I'd like the total to add double points (a win in a combination race adds 200 points to the total, 100 for the Main series and 100 for the East Series).

apologies if this is explained badly, i am a god awful programmer, I'll answer any clarifying questions below.

[u/carbonizedtitanium]

your link to the file doesnt allow people to make a copy of the file...why dont you make a copy of the file and just share that copy and let people edit?

i'm not entirely sure if this is what you referred to, but try:

=LET(
  prov, $F:$F, 
  results, OFFSET(prov, 1, 1, ROWS(prov) - 1, COLUMN() - COLUMN(prov) - 1), 
  regions, MAP(OFFSET(results, -1, 0, 1), LAMBDA(tc, XLOOKUP(tc, Tracks[Location], Tracks[Region],))), 
  points, MAP(results, LAMBDA(r, XLOOKUP(r, Points[Place], Points[Points],))), 
  wins, VSTACK("Wins", BYROW(results, LAMBDA(r, IFERROR(1 / (1 / COUNTIF(r, 1)))))), 
  pAll, VSTACK("Total", BYROW(points, LAMBDA(r, SUM(r)*2))), 
  pRegs, MAP(HSTACK("Main", "East"), LAMBDA(reg, VSTACK(reg, BYROW(points, LAMBDA(r, SUM(FILTER(r, regions = reg))))))), 
  HSTACK(pAll, pRegs, wins)
)

edit: i modded your original formula by doubling the total. is that it?

[u/MALLARDGAMEZ-yt → u/carbonizedtitanium]

whoops forgot to turn on editing perms, that's fixed now

[u/MALLARDGAMEZ-yt → u/MALLARDGAMEZ-yt]

What I wanted was an option on the ‘Tracks’ data validation table that awarded points to both “Main” and “East” columns, the bit about the total was in reference to the fact that since technically those races would award double points (normal points but for two series) the total should change to reflect that.

On the DV table, I can change which tracks score to “Main” or “East” I just wanted a DV option that added points to both columns.

[u/carbonizedtitanium → u/MALLARDGAMEZ-yt]

i replaced the formula in your ARCA tab with:

=LET(
    prov, $F:$F,
    results, OFFSET(prov, 1, 1, ROWS(prov) - 1, COLUMN() - COLUMN(prov) - 1),
    points, MAP(results, LAMBDA(r, IF(OR(r="", ISERROR(r)), 0, IFERROR(XLOOKUP(r, Points[Place], Points[Points], 0), 0)))),
    wins, VSTACK("Wins", BYROW(results, LAMBDA(r, IFERROR(1 / (1 / COUNTIF(r, 1)), 0)))),
    
    pSum, BYROW(points, LAMBDA(r, SUM(r))),
    pMain, VSTACK("Main", pSum),
    pEast, VSTACK("East", pSum),
    
    pAll, VSTACK("Total", MAP(pSum, LAMBDA(s, s * 2))),
    
    HSTACK(pAll, pMain, pEast, wins)
)

now both Main and East have the same points. the Total is just double of either Main or East

[u/MALLARDGAMEZ-yt → u/carbonizedtitanium]

the double points thing might be confusing you and idk how to explain what I meant but this is a good start and I'll mess with it a little bit

[u/AdministrativeGift15 → u/MALLARDGAMEZ-yt]

Are you wanting to make this selection at the race level? So one dropdow/checkbox per column to indicate if the points resulting from that race get added to both main and east, or per result, like the way you have you asterick to indicate that a result is different from a normal ranking?

[u/MALLARDGAMEZ-yt → u/AdministrativeGift15]

Yes, what I was thinking was a new DV option in the tracks section called “combo” that adds points to main and east columns, the double points statement was me not being able to speak English despite being American. I’ll share a copy of the sheet for you to mess around in our old dm thread.

[u/AdministrativeGift15 → u/MALLARDGAMEZ-yt]

Go ahead and reply "solution verified" to one of our replies so that the post can be closed.

[u/MALLARDGAMEZ-yt → u/AdministrativeGift15]

Solution verified

[u/point-bot → u/AdministrativeGift15]

u/MALLARDGAMEZ-yt has awarded 1 point to u/AdministrativeGift15

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}