April 21, 2015
Hope somebody can help with this item.
For the gymnastic club here I made several result lists. During the years they improved in automation. But I kept on thinking about a more better method with less handwork.
I’m almost there I think. For this Forum I made an extract from the datasheet (the data comes from other sheets with some functions, but for now I hardcopied them in).
On this sheet you’ll see al kinds of data which child it’s about, what club, etc. but most important for my question is column E where the Categorie is given. As you can see children are classified in different categories. Each categorie can have one to many participants, spread over more groups (column B). But they compete against each other.
So in column AB I want to rank them for their categorie.
Of course I can do that ‘by hand’ (rank the rating either from column Z or AA and give for the first categorie by hand in that’s the group from row 5 until 29 (with 28). But that’s typical something I would like Excel to do for me: find the groups and rating them automatically.
I tried a lot with index, match, offset and such, but can’t get it.
You find already some testing numbers in the sheet, based on how the calculations go so don’t bother about them and feel free only to give an example for one categorie.
Any help would be gladly appreciated!
EDIT: for this year, for this round this is the setting for the categories. But I have to go two other rounds this year with a completely different setting (les categories, more children in the different categories. So that's why I want that part 'automated'. Hope this is clear. It's not about this sheet itself, it's part of a bigger 'problem'. Thanks!
June 25, 2016
April 21, 2015
Hi Sunny, I owe you one! Magnificent!
Exactly working the way it’s needed. Knowing this, I did some research again in my books and found an example and explanation how it works, but that’s afterwards I never would have discovered I could use this function for this problem.
And even better: I presented the slightly adjusted figures in column Z. I now tested with hard numbers with only two digits and there it’s also working perfect for the figures that are the same. Also a problem I had to solve: in the case of two numbers 2 for instance what’s next? Number 3 or number 4? And if you give 4 medals how to deal with that? I got some instructions on how they want it and that’s precisely how it works out now!
Many thanks again also on behalve of the more than 220 children who are going to compete in two weeks , with this new formula on the background for a honest competition!
Most Users Ever Online: 57
Currently Online: Matthias
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
R vd Bosch
Rao Sharafat Ali
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas