Re: Conditional Counting if Multiple Conditions Met
0
2267
4
Hah. And yes, I definitely know how to do this in excel with a very long IF statement. In case it gets your brain firing here’s my statement in excel right now (though it does this and a whole lot more).
=IF(’#Matchups (St)’!G9="","",IF(AND(‘Total Matches (St)’!B6>0.01,SUM(‘Total Matches (St)’!B6)>‘Meta (St)’!$B$5),(‘Wins (St)’!B6/(‘Wins (St)’!B6+‘Losses (St)’!B6)),’#Matchups (St)’!G9))
but the key here is it grabs the name of Team 1 from the row, and team 2 from the column, then does a lookup against all the matches in a match table to count wins, losses, and divide by total matches. Obviously in a database like Airtable I don’t expect it to be the exact same. But just in case it jogs a little something! :slightly_smiling_face:
OK, here’s most of a completed base that kind of does what you want. As published, it supports up to 10 teams, but it can easily be expanded to a larger number. At the moment there are 8 defined; I recommend you add a 9th to see how the base works.
First, though, I should mention there are some shortcuts taken here and there that could be smoothed out. For instance, if you look at the single selects for {Winning Team} and {Losing Team} in the [Games] table, you’ll notice I’ve appended an index number to each. Elsewhere in the base, such things function more as one would expect, with the base associating a certain index value with a specific team name, but early in its development I didn’t want to get wrapped around the axle of establishing and referencing these index numbers, so I simply made them hard-coded. I was about to go back and fix them when it occurred to me I was spending a lot of time on a freebie proof of concept base, so if they bother, have at them. You’ll find other such liberties taken throughout the base.
I’m also not sure why I have both single selects and linked records used to define winners and losers in [Games]. I seem to think I added the selects simply to feed the pivot table, so you could easily eliminate them if you wish. Alternatively, you could use them the way I have been during development: First, I set the winning and losing teams using the drop-down menus. I then copy the newly entered data (select first-entered {Winning Team} and last-entered {Losing Team} — that is, the upper-left and lower-right cells of the block — and press Ctrl-C) and paste it into the first empty cell in {Link to Winning Team}. This populates the linked records fields corresponding to the recently entered single-selects and creates new records as needed.
To add a new team to the league, then, the first step would be to create new options in the configuration of {Winning Team} and {Losing Team}.The options should be in the format 'Team Name - ##' where '##' is the index number assigned to the team. The same index number should be used for both winning and losing entries. (You may notice I fat-fingered several entries, so that some read 'Team Name - ##' and others 'Team Name -##'. As it turns out, either will work; what wouldn’t work would be 'Team Name- ##'. I didn’t go back and correct the erroneous ones because doing so would have caused me to generate new records for the corrected entries — another downside to my expediency)
You can then either enter a placeholder record for the team in the [Winning Team] and [Losing Team] tables or wait for the records to be created as a matter of course.
Caveat: The base uses entries in [Winning Team] to create much of the data it requires for self-management. Should a newly entered team not prove very successful on the field, you’ll want to create a [Winning Team] record manually. (The bulk of the fields in [Winning Team] simply feed other aspects of the base; in practice, probably everything to the right of {Winning Matches} would be hidden.)
{Winning Matches} is just as it sounds: The table contains a record for each team tracked by the base, as indicated by the primary field; {Winning Matches} lists every team over which the primary team has triumphed, along with a count of how many times the primary team has beaten them. It exists mainly as an example to show the data is available from the base; you’ll probably want to access and expose the data in other ways. (I’ll demonstrate one such other way a little later.)
When you add a team, you’ll need to make a couple minor changes in the [Winning Team] and [Losing Team] tables: Namely, you’ll need to establish a link to the [Calc] table from the new record. You can do this manually, by clicking on the plus sign in the {Calc} field and
selecting the single record in [Calc]; you may copy-and-paste a period ('.') character into the {Calc} field; or you can click-and-drag the fill handle from the {Calc} field in the previous row to the current row, thus duplicating the link to the record entitled '.'. You’ll need to establish such a link in both the winning and losing tables.
As mentioned earlier, the remainder of the fields in [Winning team] support internal processes. It is here that, based upon {Autonumber}, the number of currently active teams is determined; it is also here that a {KeyValue} pair is extracted to associate an index number with a team name. Other fields reflect a summarizing process taking place in [Calc]; for instance, {TeamNames} returns a list of key:value pairs concatenated within [Calc] but parsed here to extract names for use in {Winning Matches}.Finally, {GamesOne} packs a triple-digit count of games won by the indicated team against each other team in the roster into a single string.
You’ll find [Losing team] to be very similar, although it boasts fewer internal data fields. It offers a {Losing Matches} field indicating how many games had been lost to each opposing team as well as a packed {Games Lost} field.
The [Calc] and [Grid Background] tables exist primarily to carry out calculations. One of [Calc]'s primary duties is to build a matrix of team:team matchups based on the packed {Games Won} and {Games Lost} fields. That matrix is then accessed by the [Grid Background] table, which uses it to construct cross-record, per-team display fields that are later rolled up into a single field expressing data about multiple records.
I use a variation on a hack I devised for the Black Mirror base in Airtable Universe to provide a display similar to your Excel example. (It will look better as you add games to the database: It’s designed to provide 10 levels of gradation between 0% and 100%, transitioning from red at 0 to yellow at 50 to green at 100.)
And, obviously, it’s incomplete: The X axis needs to be labeled (the columns are team names with index values 1 through 8, left to right) and, more importantly, the formula for the {Winners} field needs to be tweaked so the percent values properly align with the colored fields. It’s simply a process of adding and subtracting HTML space characters of varying widths to the assorted overlapping equations that create the final image… (I’ve done it before; now it’s your turn.)
In any case, there’s your expandable base that will track the win:loss records of a number of teams; that doesn’t need to have teams explicitly named in its code; and that can be easily converted to a number of other uses.
OK, this bothered me, so I fixed it.
I’ve updated the demo base so the won/lost matrix displays correctly. (The two extra columns to the right are where the 9th and 10th teams would go; feel free to delete the heading placeholders if you wish.)
Dammit.
I just thought of a significant improvement. Well, if I implement it, I’ll add yet another reply.
I also Greeked in some additional games, to fill out the matrix somewhat:
