Formula For Pass or Resit

Hi, I am relatively new to Excel but find myself tasked with producing an E-reporting system using Excel for a small training centre.
I will try to describe the problem the best I can and any help would be really appreciated.

In the first column would be the participant names. The second column would be EXAM 1 (where a pass would be 75 or above out of 100), the achieved score would be input here. The third column would be EXAM 2 (same pass criteria as column 2). The fourth column would be for EXAM 1 RESIT (pass would be 85 or above out of a 100), column five would be EXAM 2 RESIT (same pass criteria as fourth column). Column six would be PASS / FAIL.

If a student scores 75 or above in both EXAM 1 & EXAM 2 then column six would show as PASS and both EXAM RESIT columns would remain empty. If however the score was 75 or above in EXAM 1 but EXAM 2 scores 74 or lower then column six would show FAIL. There would then be an exam resit for EXAM 2 and if the score was 85 or above then column six would now show PASS. (obviously this needs to work for both RESIT columns)

Basically I can get this working to a fashion but I am really struggling with the RESIT aspect of it. If anyone could help me with the formulas that would make this work I would really appreciate it, as I am stumped.

Thank you

If there are 2 re-sits (Exam 1 and Exam 2) with results of 80 and 86 respectively, what is result ?

if they pass Exam 1 and fail exam 2 would they have to resit both exams or just the one they failed

and if they got 80 in one exam and 70 on the other - the average is 75 would that be a fail or a pass

Originally Posted by JohnTopley

If there are 2 re-sits (Exam 1 and Exam 2) with results of 80 and 86 respectively, what is result ?

That would be a fail. If both Exam 1 & 2 scored below 75 then both Exams would need a re-sit and both would need a score of 85 or above to achieve a pass. Thank you for taking the time to reply.

Originally Posted by fawnlemur

if they pass Exam 1 and fail exam 2 would they have to resit both exams or just the one they failed

and if they got 80 in one exam and 70 on the other - the average is 75 would that be a fail or a pass

If they failed either exam then they would just retake the failed exam but would need the higher score on the exam being re-sat.
The averages don't affect the Pass or Fail, both exams need 75 or above, so if Exam 1 scored 75 and exam 2 scored 74, it's a fail. If exam 1 scored 76 and exam 2 scored 74, it is still a fail.
Thank you for replying.

@Dave: very smart solution!

Thank you all for your replies, I really appreciate the help

I am still having trouble so I have attached the sheet form the workbook showing exactly what it is I am doing.

Line 1 shows my attempt
Line 2 shows 'Flameretired' suggestion
Line 3 shows 'Fawnlemur' suggestion

I hope I am inputting the suggestions correctly, or if I have not explained my problem correctly I apologise.

Thank you once again for all the help.

I apologise, I was just putting a score under Resit LATSI to see if the outcome changed, and forgot to delete it before saving

I will look into the merged cells.

Thank you for the help

I did as you suggested and unmerged the cells, however I am now getting it as a PASS when one of the initial exams are below a score of 75.

Thank you for your help.

Thanks for all the replies and help, we are almost there but not quite.

The formula provided by Flameretired appears to be working correctly, the only issue remaining is that if there is a pass score i.e 75 75 in the initial exam columns the certification column says FAIL.
If I then put a pass score of 85 or above in the re-sit column it will change to a PASS.

Thanks again for all the help, its been driving me crazy.

Thank you for all the replies and help. I am relatively new to excel and I am learning as I go so I really appreciate all the help.

The reason why I have the extra columns displaying 75 is that no matter what the score for a re-sit is, as long as it is a pass then the student would receive a 75 score. I have used two columns one to show the actual score and to show a 75 as the awarded score.

I have tried the new formulas supplied by Flameretired and JohnTopley and both still don't work exactly.

With John Topley formula it all works apart from the fact if the LATSI or ATC theory assessment is failed the last column shows FAIL, however if you put a PASS score in either of the RE-SIT columns it will show a PASS in the final column.

Thank you for your reply and explanation.

From your explanation I understood how it works and what I was misunderstanding.

It works perfectly.

Thank you everyone for your help.

Good to know!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I just marked the thread as solved and then realized there is 1 thing left to solve.

The certification column defaults as FAIL when there is no data in the cells. Is it possible to make the cells in the certification column empty, until such time as data is input in the other cells.?

Originally Posted by Tony Valko

Show us the formula you ended up using.

It was the formula supplied by JohnTopley

=IF((COUNTIF(C5:D5,">=75")+COUNTIF(E5,">=85")+COUNTIF(G5,">=85"))=2,"PASS","FAIL")

Thank you, that appears to work

Whilst doing this I have been asked for another requirement, hopefully a little simpler regarding columns E & F and columns G & H

This is the requirement for both

If column E scores 85 or higher then a value of 75 is put in column F. The same applies to column G and H.

However if the score in column E or G is 84 or lower then I need the figure in column E or G to appear in columns F and G respectively.

Thanks again everyone for all the help. You will never know how appreciated it is.

In F5 and copy down

=IF(E5>=85,75,E5)

in H5 and copy down

=IF(G5>=85,75,G5)

Thanks for the replies this works

I know I have changed the worksheet a fair bit throughout the process and I have now uploaded the latest and what I am hoping is the final one.

Column L is the course score which is the average of all the exams a student has taken, for example if a student passes the first 2 assessments with a score of 75 in each then the course average would be 75.
If they fail 1 assessment then the course average would be the average of the 3 exams they have taken (2 assessments and 1 re-sit), the same applies if they were to re-sit both assessments.

At the moment column L works out the average for 4 exams (2 assessments and 2 Re-sits) even if a student has only taken 2 exams.

Is there something I can add to the column L formula for it to only make average calculations based on whether there is a value in a cell.

For example
Column C =75 Column D =75 Column L = 75
Column C =74 Column D =76 Column F = 85 Column L = 78.3

Thanks

So Average includes "Fails"??

Yes. It just needs to show the averages of the exams taken be it 2, 3 or 4 and either pass or fail

Are you just wanting =AVERAGE(C5:E5,G5)

TRy

=SUM(C5,D5,F5,H5)/(COUNTIF(C5:D5,">0")+COUNTIF(F5,">0")+COUNTIF(H5,">0"))

or

=SUM(C5:D5,F5,H5)/(COUNTIF(C5:D5,">0")+COUNTIF(F5,">0")+COUNTIF(H5,">0"))

Thank you for all the help, everything working correctly.

Will mark the post as solved now.