Formula to create a ranking out of several variables

Hi guys,

I need to do a ranking based on several variables with predefined ranks. I.e. I have 4 Variables with different numbers. Depending on the amount of each variable, a person ranks up or not. so i need to find a formula that does the matching for me. Can you help me?

I attached a workbook with two sheets in it. 1. Ranking Structure, and 2. Report with data (the report I have has about 1000 entries)

with shares not numbers

Hi Tim201110, Hi Special-K,

Thank you for your help, unfortunately, I am not sure if you guys understood me correctly. The issue I face is that I have a report with lots of data. I now need to rank this data in a preset rank structure provided in the ranking sheet. There are 21 predefined ranks which are based on 4 variables. For example, you get Rank 7 if you have a minimum of 6 in Variable 1, 72'000 in variable 2, 75 in Variable 3, 30 in variable 4. As soon as you reach all the requirements of Rank 8 you rank up.

I now need a formula for the Column F in the sheet Report, which gives shows me what Rank (1-21) from the Ranking tab applies to that user.

Unfortunately i think neither of your solutions fit my need. Is there any other option?

still not clear:
var1?
what to do if positions are not the same?

Hi,
take a look at attachment and says if that is what you want

Hey TudyBTH, sorry for my late answer. That's exactly what I needed!

Thanks a lot for your help!

Originally Posted by TudyBTH

Hi,
take a look at attachment and says if that is what you want

Hey TudyBTH,

I just realised that I cannot use your doc since it has an error when it comes to the lower ranks... Can you check again and see if there is another option for the Formula to return the right answers?

I tried the AND function but that doesn't work either.

I'm sorry but I do not understand very well what are the criteria for ranking. Please fill column manually with the correct values.

Originally Posted by TudyBTH

I'm sorry but I do not understand very well what are the criteria for ranking. Please fill column manually with the correct values.

Hi TudyBTH,

Thanks for the answer. I wrote next to the ones that are wrong which Rank title they should have. See Excel Attached.

Thanks for your help!

Try this ...

=INDEX(Ranking!$F$3:$F$25,MATCH(1,(E2>=Ranking!$B$3:$B$25)*(Report!D2>=Ranking!$C$3:$C$25)*(Report!B2>=Ranking!$D$3:$D$25)*(Report!C2>=Ranking!$E$3:$E$25),0))

Enter with Ctrl+Shift+Enter

Originally Posted by Phuocam

Try this ...

=INDEX(Ranking!$F$3:$F$25,MATCH(1,(E2>=Ranking!$B$3:$B$25)*(Report!D2>=Ranking!$C$3:$C$25)*(Report!B2>=Ranking!$D$3:$D$25)*(Report!C2>=Ranking!$E$3:$E$25),0))

Enter with Ctrl+Shift+Enter

Hi Phuocam,

Thanks a lot for your quick answer... unfortunately that doesn't seem to work for everything. I slightly changed your formula and attached it to the doc so it would fit my report. In Tab Passive ranks in line 2 there should be rank 3 if I am correct. What am I doing wrong?

H2 = 1 -> not Rank 3

I've been too long at this... can't even see straight anymore Thanks a lot!

You are welcome, good luck!

I do have another question however... do you know why there is a few #N/As in there? there shouldn't be? they should be Rank1, no?

I did... I checked the document I attached to my last post, but couldn't find any leading or trailing spaces... Is there another possibility?

Originally Posted by AliGW

#N/A suggests that one or more of the data items the formula is looking at is not in the correct format. In other words, it can't find relevant data for one or more of its processes. Try stepping through the formula using the Show Calculation Steps tool on the Formulas ribbon.

I tried... i do get that the error seems to be in the second part of the Formula, the MATCH one... but I can't figure out what is wrong...

Would you mind having a quick look yourself into the excel?

Thanks ... anyways, i liked your guidance. Let's hope Phuocam will be able to assist me

yes i did.. there seems to be none in there that are not needed.

haha... sorry, I meant any spaces that aren't placed between Titles to make the words readable Correct answer would have been, there are NO leading or trailing spaces.

I just found the issue which cured everything but User 5.... so i'm one step closer. It was a simple reference to a shorter range than everyone else... now off to the last one

Hi Dlinen!

Your formula:

=INDEX('Ranking Metrics'!$F$3:$F$25,(MATCH(1,(D2>='Ranking Metrics'!$A$3:$A$25)*(E2>='Ranking Metrics'!$B$3:$B$24)*(F2>='Ranking Metrics'!$C$3:$C$25)*(G2>='Ranking Metrics'!$D$3:$D$25)*(H2>='Ranking Metrics'!$E$3:$E$25),0)))

Change 24 to 25.

Thanks Phuocam! was figuring it out myself (took some time though )

thanks a lot for your support!

You are welcome, good luck!