Forum Discussion
Function @ IF, INDEX, AGGREGATE, LARGE, and COUNT
The person I usually go to for help recently passed away, and I am not sure how to adjust the functions to do what I am trying to accomplish. I am given the scores in rounds. Even rounds are performed at one physical location and the odd rounds are at another location. I then Rank and order the "even" and "odd" rounds. The first issue I have is I am attempting to show all scores under "odd Rounds".
Odd Rounds
The "Top" cell is any number I select, in this case, 28.
The "Hurdle" cell function is =LARGE($C$3:$C$85,M5)
The "Count" cell function is =COUNTIF($C$3:$C$80,">="&M6)
Even Rounds
The "Top" cell is any number I select, in this case, 28.
The "Hurdle" cell Function is =LARGE($H$3:$H$85,P5)
The "Count" cell Function is =COUNTIF($H$3:$H$80,">="&P6)
As you can see below, all the even-round scores are shown, but not all the odd-round scores.
I have made sure that the function is consistent and the same as the cells above 28 in the cells below the Top 28 line.
The next step I am attempting with the Odd and Even Rounds (Row M) data is to take the top 28 from both Odd and Even round locations and then sort them in score order to create a semi-final seeding. The function I currently have is the start of my issue with creating a semi-finals seeding. I know this, but I'm not sure how to accomplish the semi-final seeding.
Combined Rounds
The top is any number I select, in this case, 56.
The Hurdle cell function is currently =LARGE($M$9:$M$150,P14)
The Count cell Function is currently =COUNTIF($M9:M$150,">="&P14).
My functions for Semi-Final Seeding is as follows:
Top Left Cell function is =IF(ROWS($R$5:R5)>$P$16,"",ROWS($R$5:R5))
Next cell to the right is =IF(R5="","",LARGE($M$9:$M$150,R5))
The last cell to the right is =@IF(S5="","",INDEX($N$9:$N$150,AGGREGATE(15,6,(ROW($M$9:$M$150)-ROW($M$9)+1)/($M$9:$M$150=S5),COUNTIF($S$5:S5,S5))))
I also want to create a warning, either with a change in font color or cell color, that there is a tie in the semi-final seeding. If any warning type was possible?
Thank you in advance for any help!
4 Replies
Unfortunately, images are nowhere near as helpful as actual workbooks/spreadsheets. Is it possible for you to share the real thing. Without it, as you've already noticed (with well over 100 views and no replies), you're less likely to get any help. There are just too many questions. The formulas make specific cell references, but we'd have to make assumptions as to which cells in the images are the critical ones, etc.
So you could help us help you by posting the actual spreadsheet on OneDrive or GoogleDrive with a link pasted here that grants access to that spreadsheet.
- Thank you for the insight - here is the spreadsheet. https://www.dropbox.com/scl/fi/se3t4x37xo6aibibwlttf/SA_Semis_AND_Finals.xlsx?rlkey=5l0b1s6xm2vxysnftgrq3ellb&dl=0
Sigh.....
I'm sorry that your usual go-to person isn't available any more. That's sad on many levels; I pray, first, that he or she has gone to a better place.
Second, though, this is a classic case of a system needing some user documentation, both on how to use AND (equally important) what all the formulas are for, how the overall system works.
To come in cold and figure out even what the big picture is is complex enough. I'm attaching the Excel spreadsheet here, with the hope that perhaps some others more skilled at "reverse engineering" can apply that kind of magic.
You can help those others, and me, by:
- Describing the "big picture" -- you've replaced the real names (of schools?) with letters in this, but the real names were there in the image, so although that was thoughtful of you, it was kind of closing the barn door after the horse had escaped. That said: I'm assuming this is a tally sheet for the result of team competitions between schools, perhaps a set of debate teams or some such. But the more you can tell us, still maintaining confidentiality of the who and where, the better.
- In the course of describing that "big picture", what do the various entries, the "names," the numbers to the third decimal place, etc., represent?
- What's the relationship between data on Sheet1 and data on Sheet2? Is one the more "raw data"? Which?
- Your spreadsheet, without names, only with letters, appears just to run from A to ZZZZZ, with no repeat names; but I assume that in the real world, the teams repeat. I.e., Team AAA may meet different opposition in each of the different rounds, but team AAA appears in every round, always representing Central High School [or whatever] in Springfield, MO [or wherever].
- If that's the case, then doesn't the version you posted present a data-integrity problem, in that there's no way to track the performance of team AAA through all the rounds.
- If it's not the case, then please describe more thoroughly what we are to make of the scrambled sets of letters? Or do they have no real meaning at all?
I could go on, but I think you get the idea. If we were sitting down face-to-face, I'd be asking a lot of questions. Anticipate those questions, realize this needs a lot of explaining, and--even though you're in the middle here, maybe not even being the original creator of this, what would you be telling us about what it is, how it is intended to work, what the different areas of the workbook are for, and so on....
