Forum Discussion
Cell Format within ABCD
If I understood right, you want to rank A B C D looking at X value; if there's a tie between any X, you check Y, then Z.
How many groups (A B C...) are you going to have in general? 4? Or they can be 10, 200, 5000?
I created something you should check.
Try change values in green cells, and check the red ones (do not overwrite red cells!). Is that the right way to check the rankings?
At the end, you should hide columns F to J (so you won't see the calcs).
What I did:
First, I ranked X, Y and Z in 3 different columns (F G H).
Second, I "merged" the 3 rankings adding rank(X) + rank(Y)/10 + rank(Z)/100; in this way I have a number like 1,14 for the Team A. 1,14 means that X value is ranked 1°, Y is 1° and Z is 4°.
If you'll have 10 to 99 groups (letters, in your example), you have to switch that formula to: rank(X) + rank(Y)/100 + rank(Z)/10000 obtaining 1,0104.
Changing that dividers allows the bottom ranking of 99,9999 (X 99°, Y 99°, Z 99°).
In general, you should count the last position available and adjust the number of 0 in that formula.
I called the I column "tot. rank" meaning the sum of these rankings (now you have to forget the points, we're talking about rankings and orders).
Now, because 1 is the best ranking, you have to sort these "tot. rank" in a new column "Rank of Totals", but you have to sort them starting from the smallest so the new formula is rank(value;all cells;1): the last "1" means that you want to have first the smallest value (1° is 1 and it's better and smaller than 5° that is 5).
Hi Arul,
Thanks for the fast reply.
Then, in case of a tie, you have to check who is the direct-winner, right?
Yes correct.
I'm not getting how some matches are not symmetric (A vs C is 1x 2y 0z and C vs A is 0x 1y 2z; I thought it should be 0x 0y 2x aka simply switching y and z -I ignore x that is the final result win/lose-).
It depends on the match method, example best of 3, usually the win will be just like 2-0,2-1 or 1-2 only, according to the game set and none other if match format is different.
I am attaching a newer version with lots of calcs that I can not explain in detail.
It's okay to come, but you're a lot to help me in this case and I'm really worth it because it's out of my mind box and I can not afford to this level unless I'm studying, I have no knowledge in excel just a little bit.
An AN column to the US assigns to each of the Teams (A B C D E F G H) the corresponding resistance in the casting rope. NOTE: You must name a team with A to H, and this does not work if there are more than 2 teams in the tie (say A, B and F are all 1x 2y 0z). This 3+ result produces # N / D! Error in table (col U, "POS").
Yes I have seen and understand what you mean.
The AU and BD columns surrender points to the IF team he won a match against his opposing side.
Yes this table is certainly the last way to get a decision if all the scores are the same. the clearer (head to head)
I have attached 1 sample table that I have changed slightly from 8 to 4 only without disturbing all the formats you have made, trying to see.
When it's almost ready all I'm dealing with is the problem of getting a position on the match table (in blue) I've come up with a format like you did but the answer I get is zero. Do I have mistake? can you help me to check?
I have to do this for a lot of tables and I refer to the example you've shared. I have a schedule for 3,4,5,6,7, and 8. I have to do one for full information for every match schedule I have recommended. It aims to simplify my work and save time every game.
Thanks in Advance,
Regards,
Uncle Rich...
Formulas in column W to BH are fixed to the table with $.
It now refers to cells (example) AE4 but you copied that table in row 104, so the valor it's receiving is 0 and this messes up everything.
Take my file (the one I sent you) and copy 1 table at a time. Once you copied the first table (and you have the second), you have to change the refers in that columns (W-BH); click on the formula and move the colored squares on the correspondant cell in the new table (which will have all datas but no refers on that cells).
It's a bit hard or tricky to do it, but it's ok.
In alternative, you should duplicate the sheet and have Group 1, Group 2... sheets with all the formula I made before (download the file again to avoid other errors).
So, all the 1vs1 tables are to be separated between the sheets.
EDIT: instead of deleting columns and rows if you need 4 team groups instead of 8, just hide them. You'll be able to use again the same file (after changing datas) in a later tournament even if there are more players.
The formula in column W to BH is set to the table with $.
Ok I understand.Take my file (which I send) and copy 1 table at a time. Once you copy the first table (and you have the second one), you need to change the reference in the column (W-BH); click on the formula and move the colored squares to the correspondent cells in the new table (which will have all the data but not refer to the cells).
Yes I did as you explained.It's quite difficult or hard to do, but it's ok.
Yes I admit it is very difficult but I also admit you are great in this matter. It would be nice if I had the expertise like you, of course, I've done a lot of things, because I'm really interested in this Microsoft Excel because I know with this app can do a lot of things.
Alternatively, you need to duplicate the sheets and have Group 1, Group 2 ... with all the formulas I made before (download the file again to avoid any other errors).
Yes I have made many copies and the files you give me make it as a reference only.So, all 1vs1 tables should be separated between sheets.
Yes I did as you said.
EDIT: instead of deleting columns and rows if you need 4 groups of groups instead of 8, just hid them. You will be able to re-use the same file (after changing data) in the upcoming tournament although there are more players.
Yes, I'm sorry for the mistake I did because I did not have much knowledge, but I'm very interested in this application, luckily you want to help me. I appreciate everything.I have attached 1 file that you gave me days to review, which information in this file I made changes only on the table, but POSITION is not as I was thinking, is this application unable to read transparently of this change? or is there an additional format that you need to do to get real results? I'm not going to trouble you to change or add anything but I just want to know how you created the format to get these results because I'm very interested in this kind of learning.
You're among the best I've never seen for this. Seriously. ;)
Thanks in Advance
Regards,
Uncle Rich...
The 8th position is caused by the Z value.
In this chart, the highest Z is the worse result, right? Well, a Team that doesn't play at all (E F G H in the example) has 0x 0y 0z.
The Team A you stated has 0x 0y 6z.
So, the Team that played the game WAS worse than the teams that does not exist in the group, because of the availability to count up to 8 Teams in the Same Group.
I changed a couple of columns adding to ignore rankings IF the Team letter (A B C D) is not specified; so now the Team A ranking is worse than B C D but not worse than the unexisting E F G H that caused it to be 8th.