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, 9 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?