How to calculate best numbers 3 i a certain rank or order (european championship)

To push things a bit forward my proposition to finish step1:
in BB3 (check if it is right "big points" are most important. If equal, goals difference matters, if still equal own goals are counted. If it is not right calculate score right way here):

Formula:

Please Login or Register  to view this content.

and copy down
in BB18:

Formula:

Please Login or Register  to view this content.

and copy down (three more rows)
in AV18:

Formula:

Please Login or Register  to view this content.

and copy right and down

I'm a bit disappointed with T34 :-P so will stop here by now.

Well, lets try second step too:
AV49 and similar in av50:

Formula:

Please Login or Register  to view this content.

in AV51 we face situation when some letters could be already used in AV49 or 50 so a bit more complicated:

Formula:

Please Login or Register  to view this content.

and similar in AV52.
while "problem" was impossible in AV49 and AV50, I suspect it could occur in AV51/52. But it was not a case with your data.
in E49 simple:

Formula:

Please Login or Register  to view this content.

and copy down

Glad to hear that. And to see the "simulation" result. Wish it could happen :-)

Okay, it didnt seem to work out the way I expected.
That was not because of the formula, but because of the way the UEFA calculates the rank order.
So, basically I didnt write the correct question.
Ive uploaded a new tab. With the correct data.
It comes down to this.
When the best numbers 3 are from 4 specific groups, theres a specific way against who they will play.
example: if the best numbers 4 are from group a,b,c and d, the winnar of group a, will play against against c, the winnar of group b will play against d, the winnar of group c, will play against a and the winnar of group D, will play against b.
Like this, there are 15 possibilities of ranking the best numbers 3.

So the first part of the initial sollution is still good. (calculating the best numbers 3), but I have no clue how I can pick the right row (BE48:BE62). I've tries some what if functions, but I cant get there.
Kaper (or others), do you have any idea?

The right numbers 3 need to get in cells g49:g52

Try in BC18:

Formula:

Please Login or Register  to view this content.

and in BD18:

Formula:

Please Login or Register  to view this content.

and copy these 2 next 3 rows down.

Then in BA46:

Formula:

Please Login or Register  to view this content.

and in BB46:

Formula:

Please Login or Register  to view this content.

and copy this one right.
Of course in BB48:BE62 shall be not texts, but appropriate addresses, like:

Formula:

Please Login or Register  to view this content.

etc.

Hello Kaper and everybody else,
I have a question.
I've made a EC pool with the help a few years ago from Kaper, but I have one problem and I kindly ask your help.
I built an excel that calculates all the standings etc. Also all teams going through to the next round.
Now this year again there are four numbers three going through, which makes it a bit more difficult.

Basically I need Excel to automatically fill in cells BP10, BP11, BP13 and BP14

The last time I was here Kapser helped me with the same question. I'm close to the result but I need help in the last step.
My excel automatically calculates the four best numbers 3 (BU3:BU6).
I also have the schedule of the Uefa which shows which teams plays a certain opponent at a certain result (CA3:CE18).

Now I only need excel to find my best four (BU3:BU6) in the matrix (BW3:BZ18) (in my case BW6:BZ6) and to name the countries behind the numbers.


Ive attached the Excel and hope someone can help me out here.

I'd expect that for BP10 formula below shall work (provided you in the meantime upgraded to Microsoft 365 - if yes, please change information in your profile: https://www.excelforum.com/profile.php?do=editprofile )

Formula:

Please Login or Register  to view this content.

and in next cells similar, but with CC4:CC18 and so on.

(see the first file attached)



If you have still older Excel, you may use:

Formula:

Please Login or Register  to view this content.

instead.


Please note that first INDEX argument is here a range starting in row 1 in CB1:CB18
In the next cells use respectively CC1:CC18, etc.

(see the second file attached)

Kaper, thx a lot, it works!
But when I copy pasted the formula it didnt, though I hace MS 365...
After opening your attachment I noticed the following change : instead of "filter" in the formula it says: "_xlfn._xlws.FILTER".
But with that it works out...
Very helpfull again!!!

hmm, after opening looking at the formula without changing and closing I get an error at your first attachment.
Though your second attachment works out, so its still great.
A little strange though, since I do use MS 365, but nevertheless, I'm helped out

_xlfn._xlws.FILTER

This means that you are not using a version of Excel that supports FILTER - are you SURE that you have 365 and not Excel 2019?

Excel 2021 and 365 support FILTER.

EDIT: Do you still have an older version of Office installed as well? If so, what is the default Office version for Office (particularly Excel) files on your system? Are they opening in an old version instead of the 365 client?

See my edit to poat #12. Do you have another version on your system? When you double-click an Excel file, which version is opening the file?

You will NEVER get that error message with 365.

The edit might be the problem... I'm gonna check that out. Anyway, the second option works as weel, so I'm very happy with the result

I think so - you need to check File Associations in Windows and make sure that Excel files are set to open with 365, and not the older version.

The second option is for Excel versions older than 2021 and 365.

Only minor detail, is that I had to use ";" instead of "," in the first formula, but I think that was just a mistype.

Not an error at all, no. European locales use ";" where everyone else pretty much uses ",". It depends on your locale and whether a decimal separator is a point or a comma. This is an Anglophone forum, so formulae are given in UK/US locale format.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help