As in the attachment, using Excel 2007, I have a record of students' marks of 5 lessons (actually they are 15 lessons but I give a sample of 5)
Each student has the right of 3 attempts to pass the exam.
What I'm looking for is, how to let Excel writing the final result of each student in letters.
For example:
Pass in 1st attempt;
Pass in 2nd attempt;
Pass in 3rd attempt; or
Fail.



Please, see the attached file.

Thanks.

Attached Files

3-attempt exam.xlsx (14.1 KB, 10 views)

See the attached

Attached Files

3-attempt exam_2.xlsx (20.2 KB, 11 views)

Great thanks XOR
I've solved it today morning but actually in different looooong way.
I just wonder how AND function works in this section?
IF(AND(E5>=50,H5>=50,K5>=50,N5>=50,Q5>=50),"Pass in 2nd attempt"

I thought, when AND is used all conditions should be met, not partially like OR.
But in your solution, if E5 and N5 are >=50 it would give TRUE.

So, what is the difference between AND and OR if I use in this IF statement?

Another question if you don't mind:

If I wrote the functions for 15 columns (15 subjects for grade 10) in the worksheet but another grade, let's say grade 8, has 12 subjects, is there a way to cancel (#REF!) error and solve it automatically without correction each function?
i.e. how to make a dynamic function?

Appreciate it.

I am not sure what you mean.

You wrote:
I thought, when AND is used all conditions should be met, not partially like OR.
But in your solution, if E5 and N5 are >=50 it would give TRUE.

Yes, all conditions must be met for AND to return TRUE
If E5 and N5 are >=50 but any of H5, K5 or Q5 is < 50 my formula returns a blank.

AND returns TRUE if all conditions are met, otherwise it returns FALSE.
OR returns TRUE if at least one condition is met. Only if none of the conditions are met it returns FALSE.

As to your last clause - can't you upload a file showing the problem? Please include so much data that it is possible to test and state manually examples of expected results.

Sorry for being not clear

If you see the first record of Alice in the attached file 3-attempt exam_3:
she got 22 in Geography ONLY in the 2nd attempt, hence, not all the conditions are TRUE for this IF statement:
[[IF(AND(E5>=50,H5>=50,K5>=50,N5>=50,Q5>=50),"Pass in 2nd attempt"]]

However, the result is [[Pass in 2nd attempt]]!! Of course it is correct.
But how, while NOT all the conditions are met, as it is supposed with AND function?!

The other question is:
As I distributed the file for other classes' teachers, I phased that some teachers do not have the same number of subjects; some have 4, others have 5.
The problem is when they deleted the unneeded column (History subject) the Final Result function gives #REF!
How to overcome this problem automatically?
I mean, how to delete #REF! from the function automatically?

Please, see the attached file 3-attempt exam_4

Thanks for your patience.

Attached Files

	3-attempt exam_3.xlsx (14.2 KB, 9 views)
	3-attempt exam_4.xlsx (13.5 KB, 7 views)

I have misunderstood so far and maybe I still do, but for sure I know it is a lot more complicated than I first thought. I will see if I can come up with a solution but I am not sure.

I have no solution to the problem shown in file: 3-attempt exam_4.xlsx

As to the first part of your question I have made some helper cells (T4:Z18). I hope the formulas in column R now show the expected results.

Attached Files

3-attempt exam_5.xlsx (22.0 KB, 9 views)

Thanks a million

To clear the problem here is the scenario:
There are two grades (7 & 8) in my school.
Grade 7 has 4 subjects and grade 8 has 5 subjects.
I have to create one Excel sheet for both grades.
I have made one with 5 subjects for grade 8 with all its functions.

When I used this file for grade 7 (which has 4 subjects), I have to delete the column number 5 (the 5th subject), right!

The problem is:
#REF! appears in the functions as below:
=IF(AND(E4>=50,H4>=50,K4>=50,N4>=50,#REF!>=50),"Pass in 1st attempt",IF(AND(E5>=50,H5>=50,K5>=50,N5>=50,#REF!>=50),"Pass in 2nd attempt",IF(AND(E6>=50,H6>=50,K6>=50,N6>=50,#REF!>=50),"Pass in 3rd attempt","")))

as in the attached file.

The question is:
How to delete or overcome this (#REF!) automatically?
Notice, please, that I will use this file for more different grades.

I hope it is clear now.
And sorry for ambiguity.

Accept my respect.

Attached Files

3-attempt exam_4.xlsx (13.5 KB, 7 views)

I am afraid we are really talking past each other which well may be due to the fact that English is not my mother tongue.

In #7 I wrote:

I have misunderstood so far and maybe I still do, but for sure I know it is a lot more complicated than I first thought.

By that I meant that the formula in 3-attempt exam_3 was completely wrong (even if it might have returned "correct" results.

That was why I (in my post #8) uploaded a file (3-attempt exam_5) with a completely different approach with helper cells. At present this is what I think returns the correct result.

In your most recent post (#9) you seem to ignore my post #8.

Didn't you see that in #8 I also wrote:

I have no solution to the problem shown in file: 3-attempt exam_4.xlsx

that is the problem with deleting columns.

Can't you just make different templates with the necessary number of columns?