PDA

View Full Version : [SOLVED] Two rankings



idnoidno
08-19-2017, 08:34 AM
20111
May I ask the teacher on the forum,how to deal with the ranking of grades,about class ranking and the total ranking?

mdmackillop
08-19-2017, 11:46 AM
see here (https://trumpexcel.com/rank-within-groups-excel/)

idnoidno
08-22-2017, 05:55 PM
I am very grateful for your answer.
If I want to write VBA CODE about ranking, hope someone can teach me!

mdmackillop
08-23-2017, 01:34 AM
I would use the macro recorder for this; giving you a solution will not "teach you"

idnoidno
08-23-2017, 06:52 AM
I mean that I was hoping someone could teach me how to use VBA to solve the problem instead of using "FORMULA".

mana
08-23-2017, 07:07 AM
range("d2:d10").formula="=rank(........)"
range("e2:e10").formula="=sumproduct(.........)"

mdmackillop
08-23-2017, 07:11 AM
I wouldn't try to reinvent the wheel.
1. Write the formula while recording a macro
2. Adjust the macro to put the formula in required cells
3. Copy/Paste special the results
If you have problems, post your code.

snb
08-23-2017, 09:51 AM
It's not that simple:


Sub M_snb()
[D2:D10] = [index(rank(C2:C10,C2:C10),)]

For j = 1 To 4
[G1] = j
[G2:G10] = [if(A2:A10=G1,C2:C10,"")]
[E2:E10] = [if(E2:E10="",if(G2:G10="","",rank(G2:G10,G2:G10)),E2:E10)]
Next

[G1:G10].ClearContents
End Sub

idnoidno
08-24-2017, 05:12 PM
Thanks to including mdmackillop, mana, snb, whether it is teaching or to provide solution, because the work just encountered such a ranking problem, for a long time can not be resolved, so I hope to learn some vba skills, if there are impolite, please forgive me.