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.

Enjoy Learning!