Thank you for sharing your valuable feedback. Sorry, I am unable to understand your requirement, please share your requirement with sample data to support@indzara.com to check further. Best wishes.
@@Muzammiljamil6 Yes, Sir. I will definitely do my best. Please let us know if you have any specific question/problem you are trying to solve. We will do our best to do a video on that. Thanks & Best wishes.
Thank you so much for the video. How about, If you have a list of Employee Worked on Weekends restrict them until all other employee work on weekends. and those who worked on weekends will work on normal day only. Worth making video for it. Thanks
Since you posted this video to respond to a query raised by one of your viewer…I am hoping to get solution to my problem as well… Let me give an example here… Suppose we have 6 students and their scores in three different subjects…Student names in column A (row 1 to 6) subjects Math, Science and Social Science in column B, C, D…total of all scores is in Column E… Now if we apply standard Rank…we have get same rank issues to two students or more basis same value in their total score.. What I want is, if two students have same overall score…followed by same rank…the student with higher score in Math should get higher rank… Can you please help me in this scenario…
Thank you for reaching out to us. Following are the steps to solve your requirement: Note: Column A - Students, B - Subject 1, C - Subject 2, D - Math, E - Total 1. In column F, you need to write the rank function using the COUNTIF function: =COUNTIFS($E$2:$E$10,">"&$E$2:$E$10)+1 2. In Column G you need to write the COUNTIFS function to get the rank of the math present in column D for the students with same rank: =COUNTIFS(F2#,F2#,$D$2:$D$10,">"&$D$2:$D$10)+1 3. Add the math rank with the actual rank and subtract by 1: =F2#+G2#-1 Best wishes.
Thank you for watching our video. I am unable to understand your exact requirement, requesting to share your requirement to support@indzara.com with some sample requirement to check further. Best wishes.
You are explaining well. I have a different problem, I have to find Rank for students. Few students are failed the rest has passed. When I calculate Rank, it is finding the overall rank including the ones who have failed. I have to find rank excluding failed students. How can I do that? I can mail you my excel file.
Thank you for watching our video. Following is the formula to get the rank without duplicates on the score column: =RANK([@Score],[Score],1)+COUNTIF($C$3:C3,[@Score])-1 Best wishes.
Hi experts. So I need to create a spreadsheet to rank 122 branches based on percentage achieved ranked on a scale of 1- 5. However, the tricky part is that the formula has to include 2 hurdles placed. So branches which score 97.0% - 98.4% will score a 3.0 - 3.9 respectively, and branches scoring 98.5% will score a 4.0 upwards, with the top percentile scoring a 5. It's not you basic 1-5 scale because of those conditions
Thank you for reaching out to us. The following are the steps to solve your requirements. 1. Create 2 sheets, 1 HELP and another sheet named DATA 2. The DATA Sheet has Data Table with Column A Branch, Column B Score %, Column C has Score % converted to a number and rounded to 1 digit decimal (=ROUND([@[Score %]]*100,1)), Column D Rank 3. In the HELP sheet, paste the below formula on the respective cells: On Cell A1 =ROUND(LET(min, MIN(DATA!$C$2:$C$15), SEQUENCE((97 - min)*10,,min,0.1)),1) On cell B1: =ROUND(SEQUENCE(COUNTA(A1#),,1,2/(COUNTA(A1#))),1) On cell C1: =ROUND(SEQUENCE(15,,97,0.1),1) On cell D1: =ROUND(SEQUENCE(15,,3,1/16),1) On cell E1: =ROUND(SEQUENCE((MAX(DATA!$C$2:$C$15)-98.5)*10+1,,98.5,0.1),1) On cell F1: =ROUND(SEQUENCE(COUNTA(E1#),,4,1/(COUNTA(E1#)-1)),1) On cell G1: =VSTACK(A1#,C1#,E1#) On cell H1: =VSTACK(B1#,D1#,F1#) 4. After this rank column D in the DATA sheet: =XLOOKUP([@Score],HELP!$G$1#,HELP!$H$1#,"") The minimum score will have rank 1 and the max score will have rank 5 with the mentioned condition. Hope this answers your requirements. Best wishes.
That's what i was looking for.. ! Thanks for the good video
You are welcome.
Best wishes.
thank you so much your video help me
You are welcome. We are glad that our video was helpful.
Best wishes.
Many thanks
You are welcome.
Best wishes.
What if I have ties in the rank for department how can I break those ties?
Helloo,thanks for such an amazing info,plz tell me what r the other formulas we can calculate by merging in this way,i mean two or more formulas
Thank you for sharing your valuable feedback.
Sorry, I am unable to understand your requirement, please share your requirement with sample data to support@indzara.com to check further.
Best wishes.
@@indzara sir I m trying to say that please teach us more formulas,like.If+rank,If+and
@@Muzammiljamil6 Yes, Sir. I will definitely do my best. Please let us know if you have any specific question/problem you are trying to solve. We will do our best to do a video on that.
Thanks & Best wishes.
Excellent Sir. I am looking for such a video. Very very informative.
Thank you for sharing your valuable feedback.
Best wishes.
Thank you sir... This video had solved one of my very complex code in macro
You are welcome. Very glad to hear.
Best wishes.
Thank you so much for the video. How about, If you have a list of Employee Worked on Weekends restrict them until all other employee work on weekends. and those who worked on weekends will work on normal day only. Worth making video for it. Thanks
You are welcome and thank you for sharing a suggestion with a scenario. I have included your suggestion in our recommendation list.
Best wishes.
Thank you
You are welcome.
Best wishes.
Great, Big problem solved
You are welcome.
Best wishes.
Since you posted this video to respond to a query raised by one of your viewer…I am hoping to get solution to my problem as well…
Let me give an example here…
Suppose we have 6 students and their scores in three different subjects…Student names in column A (row 1 to 6) subjects Math, Science and Social Science in column B, C, D…total of all scores is in Column E…
Now if we apply standard Rank…we have get same rank issues to two students or more basis same value in their total score..
What I want is, if two students have same overall score…followed by same rank…the student with higher score in Math should get higher rank…
Can you please help me in this scenario…
Thank you for reaching out to us.
Following are the steps to solve your requirement:
Note: Column A - Students, B - Subject 1, C - Subject 2, D - Math, E - Total
1. In column F, you need to write the rank function using the COUNTIF function:
=COUNTIFS($E$2:$E$10,">"&$E$2:$E$10)+1
2. In Column G you need to write the COUNTIFS function to get the rank of the math present in column D for the students with same rank:
=COUNTIFS(F2#,F2#,$D$2:$D$10,">"&$D$2:$D$10)+1
3. Add the math rank with the actual rank and subtract by 1:
=F2#+G2#-1
Best wishes.
This is extremely close to what is I'm looking for, is there any way to turn this into "Percentile if"?
Thank you for watching our video.
I am unable to understand your exact requirement, requesting to share your requirement to support@indzara.com with some sample requirement to check further.
Best wishes.
How to get unique rank in case of conflicting scores ?
You can use the below similar formula to get the unique series of rank:
=RANK([@[1 Month]],[1 Month],0)+COUNTIF([1 Month],[@[1 Month]])-1
Best wishes.
@@indzara but this is ranking without condition. What of i want two criterions and then remove duplicate ranks?
You are explaining well. I have a different problem, I have to find Rank for students. Few students are failed the rest has passed. When I calculate Rank, it is finding the overall rank including the ones who have failed. I have to find rank excluding failed students. How can I do that?
I can mail you my excel file.
Thank you for sharing your valuable feedback. Requesting to share your sheet with sample data to support@indzara.com to check further.
@@indzara Thank you for reaching out to help. I have attached my worksheet with sample data to the MailID you suggested.
What is there are duplicate ranks?
Thank you for watching our video.
Following is the formula to get the rank without duplicates on the score column:
=RANK([@Score],[Score],1)+COUNTIF($C$3:C3,[@Score])-1
Best wishes.
Thanks, this worked but i am looking at ranking with multiple conditions and then removing duplicates
Hi experts. So I need to create a spreadsheet to rank 122 branches based on percentage achieved ranked on a scale of 1- 5. However, the tricky part is that the formula has to include 2 hurdles placed.
So branches which score 97.0% - 98.4% will score a 3.0 - 3.9 respectively, and branches scoring 98.5% will score a 4.0 upwards, with the top percentile scoring a 5. It's not you basic 1-5 scale because of those conditions
Thank you for reaching out to us.
The following are the steps to solve your requirements.
1. Create 2 sheets, 1 HELP and another sheet named DATA
2. The DATA Sheet has Data Table with Column A Branch, Column B Score %, Column C has Score % converted to a number and rounded to 1 digit decimal (=ROUND([@[Score %]]*100,1)), Column D Rank
3. In the HELP sheet, paste the below formula on the respective cells:
On Cell A1
=ROUND(LET(min, MIN(DATA!$C$2:$C$15), SEQUENCE((97 - min)*10,,min,0.1)),1)
On cell B1:
=ROUND(SEQUENCE(COUNTA(A1#),,1,2/(COUNTA(A1#))),1)
On cell C1:
=ROUND(SEQUENCE(15,,97,0.1),1)
On cell D1:
=ROUND(SEQUENCE(15,,3,1/16),1)
On cell E1:
=ROUND(SEQUENCE((MAX(DATA!$C$2:$C$15)-98.5)*10+1,,98.5,0.1),1)
On cell F1:
=ROUND(SEQUENCE(COUNTA(E1#),,4,1/(COUNTA(E1#)-1)),1)
On cell G1:
=VSTACK(A1#,C1#,E1#)
On cell H1:
=VSTACK(B1#,D1#,F1#)
4. After this rank column D in the DATA sheet:
=XLOOKUP([@Score],HELP!$G$1#,HELP!$H$1#,"")
The minimum score will have rank 1 and the max score will have rank 5 with the mentioned condition.
Hope this answers your requirements.
Best wishes.
Thanks Sir...its a cool and great video..kudos Master Indzara
You are welcome.
Best wishes.
Ranking is not Coming perfect for a group if there are same score.
Please provide more details. Please email a support request to support@indzara.com
Best wishes.