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!
Shakil - India : How to create macro in excel
Dear sir, I want to know the process how to create macro in excel.
Dear Shakil,
You can either 'Record a macro' or 'Write a macro'. To write a macro you will need to have command over VBA however to record a Macro you don’t need any language skill.
To Record a Macro:
Open a new excel file and on Cell A1 type “Shakil”.
Click to VIEW Ribbon (menu) in Excel 2007, you will find ‘Macros’ tab in the end, Click on it and select “Record Macro” (In previous version Go to Tools->Macros->Record a Macro).
This will pop up a window asking you to choose a name and shortcut for the macro etc. Let’s give it the name “Shakil”, Shortcut key “k” (to set the shortcut to “Ctrl+k”). Click OK button.
Now Excel will record anything you do on the worksheet, do whatever you want... eg type “=IF(A1="Shakil"," is a quick learner"," is not a quick learner")” on cell B1. Press Enter.
Now go to the Macros Tab and click “stop recording”.
Congratulations! You have successfully created the Macro named “Shakil”.
Now delete all data on the sheet. Type “Shakil” on any cell on the worksheet and then move to next cell and hit “Ctrl+k”. you will be displayed the result.
Enjoy Learning!
Dear Shakil,
You can either 'Record a macro' or 'Write a macro'. To write a macro you will need to have command over VBA however to record a Macro you don’t need any language skill.
To Record a Macro:
Open a new excel file and on Cell A1 type “Shakil”.
Click to VIEW Ribbon (menu) in Excel 2007, you will find ‘Macros’ tab in the end, Click on it and select “Record Macro” (In previous version Go to Tools->Macros->Record a Macro).
This will pop up a window asking you to choose a name and shortcut for the macro etc. Let’s give it the name “Shakil”, Shortcut key “k” (to set the shortcut to “Ctrl+k”). Click OK button.
Now Excel will record anything you do on the worksheet, do whatever you want... eg type “=IF(A1="Shakil"," is a quick learner"," is not a quick learner")” on cell B1. Press Enter.
Now go to the Macros Tab and click “stop recording”.
Congratulations! You have successfully created the Macro named “Shakil”.
Now delete all data on the sheet. Type “Shakil” on any cell on the worksheet and then move to next cell and hit “Ctrl+k”. you will be displayed the result.
Enjoy Learning!
Subscribe to:
Posts (Atom)