[Solved] Final result required Pass , Fail or Under Review

[mathi]

Hello,
I need help about final results of students.
Please check attached file and tell me how can i get final results as mentioned in H column or after I column.
If you filter only Latest in F column so you will be able to see clear results. Also subjects can not be fixed in formula like if math=pass, english=pass then pass otherwise fail because subject can be any subject instead of fixing 7 or 8 subjects.

Regards

 

[Lutfor Rahman Shimanto]

Hello,
I need help about final results of students.
Please check attached file and tell me how can i get final results as mentioned in H column or after I column.
If you filter only Latest in F column so you will be able to see clear results. Also subjects can not be fixed in formula like if math=pass, english=pass then pass otherwise fail because subject can be any subject instead of fixing 7 or 8 subjects.

Regards

Hello Mathi

Thanks for reaching out and sharing your difficulties with ExcelDemy Forum. By analyzing your problem, we understand that you need help with a formula to generate the final result. We can solve the issue by forming a complex formula. I developed the formula by combining the IF, SUMPRODUCT and FILTER Functions.

=IF(SUMPRODUCT((FILTER($E$2:$E$22, ($A$2:$A$22=J2)*($F$2:$F$22="latest"))="fail")*1)=0,IF(SUMPRODUCT((FILTER($E$2:$E$22, ($A$2:$A$22=J2)*($F$2:$F$22="latest"))="UR")*1)=0,"Pass","Under Review"),"Fail")

Steps:
Select cell L2 >> insert the mentioned formula >> drag the Fill Handle icon to L5.

​

I have attached the solution Workbook to help you understand better. Please let me know if you want to know how the formula works. Don't hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto

 

[mathi]

Hello Mathi

Thanks for reaching out and sharing your difficulties with ExcelDemy Forum. By analyzing your problem, we understand that you need help with a formula to generate the final result. We can solve the issue by forming a complex formula. I developed the formula by combining the IF, SUMPRODUCT and FILTER Functions.



Steps:
Select cell L2 >> insert the mentioned formula >> drag the Fill Handle icon to L5.

View attachment 472​

I have attached the solution Workbook to help you understand better. Please let me know if you want to know how the formula works. Don't hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto

Thank you very much Lutfor Rahman,
So formula is just taking unique name and then status to LATEST and then checking Pass , Fail and Under review,.
Yes that is good, let me apply it in my workbook to see the results.
Again thank you very much to help me for this solution.

 

[Lutfor Rahman Shimanto]

Thank you very much Lutfor Rahman,
So formula is just taking unique name and then status to LATEST and then checking Pass , Fail and Under review,.
Yes that is good, let me apply it in my workbook to see the results.
Again thank you very much to help me for this solution.

Thanks for thanking me, Mathi. You're very most welcome. Don't hesitate to contact us with further questions or assistance.

Regards
Lutfor Rahman Shimanto

 

[mathi]

Dear Mr. Lutfor Rahman,
I am applying this formula to all students results, Now there is one more request.
Please check attached file , in this file first sheet is for Raw result and second sheet is for visual summary,
Please tell me how can i make visual summary with formulas so when i enter any result in raw result it will automatically color the cells based on result in summary sheet by student name and class number in related subject.

 

[Lutfor Rahman Shimanto]

Dear Mr. Lutfor Rahman,
I am applying this formula to all students results, Now there is one more request.
Please check attached file , in this file first sheet is for Raw result and second sheet is for visual summary,
Please tell me how can i make visual summary with formulas so when i enter any result in raw result it will automatically color the cells based on result in summary sheet by student name and class number in related subject.

Dear Mathi

Thanks for reaching out again. Your described issue can be solved using a formula and the Conditional Formatting feature.

Steps:

Choose cell B6 >> insert the following formula >> drag the Fill Handle icon to G6.

=FILTER('Raw Results'!$E$2:$E$26, ('Raw Results'!$A$2:$A$26 = $A$1)*('Raw Results'!$B$2:$B$26 = $A$6)*('Raw Results'!$C$2:$C$26 = B$2)*('Raw Results'!$F$2:$F$26 = "latest"))

​

Later, select range B6:G6 >> go to Home >> click on Conditional Formatting >> choose New Rule.

​

The New Formatting Rule window will appear. Then, select the shown Rule Type >> insert the following Rule Description >> click on Format.

​

Next, the Format Cells window will pop up. Go to the Font tab >> choose the shown color.

​

Now, go to Fill >> click on the color Yellow >> hit OK.

​

Finally, click OK from the New Formatting Rule window.

​

Now, open the Conditional Formatting Rules Manager window >> Duplicate the previous rule and edit.

​

As a result, we will output like the below one.

​

I have attached the Workbook used to solve the problem. I hope that the idea helps you reach your goal. Good luck!

Regards
Lutfor Rahman Shimanto

 

[mathi]

You are really expert Mr. Lutfor Rahman, Let me apply it on my worksheet.
Regards

 

[Lutfor Rahman Shimanto]

You are really expert Mr. Lutfor Rahman, Let me apply it on my worksheet.
Regards

You are most welcome, Mathi.

Regards

 

[mathi]

Dear Mr. Lutfor Rahman,
I need one more addition. Please check attached file. Now i attached under review with date. when there is date and paper is under review so it will show you under review. Please try to enter date in A7. Now what i want that if date is empty and status is latest as mentioned in attached file 7th row so it need to show IN PROCESS. so when paper will finish i will add date so it will be under review. Can you please help in this situation also.
Also you can see we have many duplicates in students name and now we need to write unique names in other cells to get status for results. Is it possible if I add new student record in B column so result section also get the new unique name or roll number automatically. because I am thinking to change names with roll numbers.

 

[Lutfor Rahman Shimanto]

Dear Mr. Lutfor Rahman,
I need one more addition. Please check attached file. Now i attached under review with date. when there is date and paper is under review so it will show you under review. Please try to enter date in A7. Now what i want that if date is empty and status is latest as mentioned in attached file 7th row so it need to show IN PROCESS. so when paper will finish i will add date so it will be under review. Can you please help in this situation also.
Also you can see we have many duplicates in students name and now we need to write unique names in other cells to get status for results. Is it possible if I add new student record in B column so result section also get the new unique name or roll number automatically. because I am thinking to change names with roll numbers.

Dear Mathi

It is great to see you again. Thanks for posting your requirement with such clarity.

You asked for an Excel Formula that will display "IN PROCESS" when A7 (Date Value) is empty, and G7 (Status) is the latest. In contrast, it will show "UR" for a non-empty date value. I have developed a formula using IF and AND functions.

=IF(AND(A7<>"",G7="latest"),"UR","IN PROCESS")

Requirement 1.1 (Empty Date):

​

Requirement 1.1 (Non-Empty Date):

​

You have also needed assistance building such an Excel Formula to find unique names within a column. You create such a formula with the help of UNIQUE, INDIRECT, MAX and ROW functions.

=UNIQUE(INDIRECT("B2:B"&MAX(IF(B:B<>"",ROW(B:B)))))

Requirement 2 (Unique Names):

​

I am also attaching the solution workbook for better understanding. Thanks once again.

Regards
Lutfor Rahman Shimanto

 

[mathi]

Thank you very much Mr. Lutfor Rahman,
But can you please check why Rifat and Justin are passed while it need to be in process in result section.

 

[Lutfor Rahman Shimanto]

Thank you very much Mr. Lutfor Rahman,
But can you please check why Rifat and Justin are passed while it need to be in process in result section.

Dear Mathi,

You are absolutely correct. As you have added a new category for status, in this case, "IN PROCESS", you must modify the previous formula. Do not worry, and I have developed a formula that will work for your dataset. Follow the instruction to solve the problem.

Steps:
Choose cell M2 >> apply the following formula >> drag the Fill Handle icon to M7.

=IF(SUMPRODUCT((FILTER($F$2:$F$24, ($B$2:$B$24=K2)*($G$2:$G$24="latest"))="fail")*1)=0,
IF(SUMPRODUCT((FILTER($F$2:$F$24, ($B$2:$B$24=K2)*($G$2:$G$24="latest"))="IN PROCESS")*1)>0,
"IN PROCESS",
IF(SUMPRODUCT((FILTER($F$2:$F$24, ($B$2:$B$24=K2)*($G$2:$G$24="latest"))="UR")*1)>0,
"Under Review",
"Pass")),
"Fail")

​

I am attaching the solution workbook as well to help you understand better. Good luck!

Regards
Lutfor Rahman Shimanto