If and rank

Hi Everyone,

My first posting and i sincerely hope someone can help me.
I have a table with a group of student names from cell m10 to m50, the results scores are given from 0 TO 5(Maximum score) from cell r10 to r50

1) i need a formula where i can know which students name appears on cell B20 on which ranks based on the results and name the students have scored highest and or more than 2 or have score 0
and names of the students that have score 1. Kindly note that i need the students name and their scores results beside them

i have been struggling on this for the pass few days and any help will be greatly appreciated

Sincerely

Junior123

Hello Mr Hasseb,

Firstly, let me Thank you for the prompt reply.
I have attached a demo worksheet in this email. Please note that the names in these sheet and their scores are frictional but hopely you could understand what i'm doing here.
If you need more clarifications, please do let me know

Thanks

Junior123

Hello Mr Haseeb,

Thanks...it worked on certain occations as i noticed a problem by ranking them
May i kindly ask,

1) If i wanted to do the same but without ranking them how will the formula be? ;- Meaning, i want cell B20 to have results which can have those more than 2 marks and pick up like e.g all the students scored only 1 and pickup results students have scored 0.

2) I have done in it using conditional format where those results which i have picked up are highligted through coloured cell. What i actually want to show on cell B20 are all those picked up by the conditional format(>2, 0 and all 1), the scores to be appeared there and name.

If possibile i do not wish to use VBA.

Hope this is not too confusing.

Please advise


Junior 123

Hello Haseeb,

Once Again my sincere Thanks for helping post the link so that the other guru's can render their assitances.

as i'm not a big fan of using VBA..since if the users do not enable their marco which normally most people do not even if it is digititally signed. Moreover excel VBA even with Password protected is quite easily cracked.
But can i ask, i have managed to use both Index and Match to get this data on a single cell...

{=INDEX(M$21:M$50,MATCH(MAX(R$21:R$50),R$21:R$50,0))&INDEX(M$21:M$50,MATCH(MINA(R$21:R$50),R$21:R$50,0))}

And need some expect advise, i tried the above it can pick out the highest and lowest value in my data which is great..
1) in cell b20 I wanted to separate the names, in between them, as currently it appears as Sally and Lay Hong together...so how do i add a either a "," or "/" between them?
2) but i need to more conditions and tried using if formula but end up with errors..., so if i need to add an if condition on top of this formula that if R21:R50 is "1" "students have to retake the test".


Please kindly advise

Junior123

Dear Sir Tigeravatar,

You are the Best!!
Since it my first time using this function, how do i add more conditions to it, for e.g, I want to choose all the lowest marks e.g 0
OR Students whom scored all 1 in their test to redo the test...could you please guide me through this.

Please advise

Junior 123

Dear Tigeravater,

Thanks for the reply, i have managed to resolved the problem...My sincere Thanks to you and Hasseb for helping me to resolve this.
And My note of Thanks to all the them whom contributes their ideas...this is one of the best forum...Let us keep this going


Junior 123