Wahid - Pakistan : How to automatically find the highest three positions?
I am a school teacher and have to conduct weekly tests, I use excel sheets to
prepare result card but I have one problem i.e. to find out the first three positions
I have to sort total number gained in “Descending” order which disturb the
sequence of student names/roll numbers. Is there any way to find the positions
automatically?
Solutions:
Yes! There are number of ways to find the highest number, I am giving you an
example of the function RANK().
RANK (number, ref, order)
Number is the number whose rank you want to find.
Ref is a list of numbers.
Order “0” for Descending else for Ascending.
This function as its name says, tells you the RANK of a specific number in the list of
numbers. Let’s try it.
1. Open a new excel file.
2. Write 10,20,30,40 in Cell Number A1, A2, A3 and A4 respectively.
3. Select cell B1 and write “=rank(“ then move Left Arrow Key to cell A1 then
press “,” and move again Left Arrow Key to cell A1 then press “Shift” key and
press Down Arrow Key till cell A4. Now press “F4” key (to fix the list) then
press “,” and write “0” for Descending Order, close the bracket and press
“Enter”.
4. Now you will see the number “4” in cell B1 and your formula will look like
“=RANK(A1,$A$1:$A$4,0)” in Formula Bar.
5. Copy Cell B1 and paste it from B2 to B4.
6. You will get the position of that specific number in the list from Highest to
Lowest (Descending Order).
To automate the finding of first three positions in a Result Card you may use a
combination of IF() and RANK() to perform the job. Let’s try it.
1. Continuing with
the
previous
sheet,
now
in
Cell
C1 write
“=IF(RANK(A1,$A$1:$A$4,0)>3,"",IF(RANK(A1,$A$1:$A$4,0)=1,"First",IF(RANK(A1
,$A$1:$A$4,0)=2,"Second",IF(RANK(A1,$A$1:$A$4,0)=3,"Third",""))))”.
2. Copy the Cell C1 and paste it from Cell C2 to C4.
3. Congratulations! You have find the first Three Positions.
See Example Excel File
ASK YOUR QUESTION