We help IT Professionals succeed at work.

Need a formula to assign ranks/positions based on the percentage values in one column

456 Views
Last Modified: 2012-02-12
Hi there,

Please see the attached file

1) Need to have a formula in column F.
2) Based on the column E result (percentage) assign the position in the class for example 1st, 2nd, 3rd, 4th, 5th or just 1, 2, 3, 4, 5 and so on till the last item of the data/list (and that can be any number of students)
3) In-case of same exact percentage then need to check P.NO.  and the lowest P.NO.  gets more weight-age/preference. For example in the given example P.NO. 101 and P.NO. 109 has the same percentage but with the P.NO. 101 will be above in the position above then P.NO. 109

Best Regards
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hello barryhoudini,

I do not know why the attachment won't come up. let me try again

Thank you...have applied your provided formula in my file. Works like a Champ!!. Please see the attached file.

Now can you please tell me little bit about the formula logic especially after + sign and SUMPRODUCT.
Display-Positions.xls
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
The RANK function will give you duplicate ranks in the higher position, so in your example E5 and E11 are both 87.33 and would therefore both be ranked 2 (with no 3).....but you want one of those to be 2 and one to be 3 based on column A value.......so you need a function that will count all the rows where the column E % is the same.....and compare the column A values....so in F5 the SUMPRODUCT part is

=SUMPRODUCT((E5=E$2:E$100)*(A$2:A$100<A5))

that counts rows where the column E % matches E5 (i.e. row 5 and row 11) and also where column A value is < A5. As there are none of the latter the result is zero and RANK stays at 2......but in row 11 you have

=SUMPRODUCT((E11=E$2:E$100)*(A$2:A$100<A11))

and row 5 satisfies both criteria so the result is 1....making the RANK increment to 3.

Note: if you have Excel 2007 or later you can use COUNTIFS rather than SUMPRODUCT so formula would then be

=RANK(E2,E$2:E$100)+COUNTIFS(E$2:E$100,E2,A$2:A$100,"<"&A2)

regards, barry

Author

Commented:
Timely, Brilliant with explanation and with alternatives...could not ask for more!!

Thank you barryhoudini

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.