complicated If statements

[Khalil Handal]

I am working with 13 subjects, each subject has three marks (3 adjacent
cells, the third cell is the average of the previous 2) .
Cells AX15, AZ15, BB15 will have a formula to show the name of the subject
with the values of "F1" or "F2" or "F1, F2" depending on the marks in the
cells.
When AX15<>"" fill cell AZ15, if both cells (AX and AZ <>"" then fill in
cell BB15)

Example:
H15, I15, J15 has 3 marks (for English) J15 =average(H15:I15)
J14 has the 50% of the mark, I10 has the name of the subject

If J15<J14 then
if H15<J14 then AX15=I10 & "F1"
else if I15<J14 then AX15=I10 & "F2"
else if H15 and I15 < J14 then AX15=I10 & "F1" &
"F2"
else AX15=""

So, All Possible values for AX15 are:
" " or "English F1" or "English F2" or "English F1 , F2"

If the student has more than three subjects with average less then 50% then
AX15,AZ15, and BB15 will = "" and cell EX15="Fail"

This has to be done for therteen subjects for each student. Cells starts at
H15 until AT15.
Of course any formula has to be copyed down until line 64.

I hope I explaned myself clearly!

 

[Bill Kuunders]

Khalil,
Good to see you're still working with excel.!!!

I'm not sure about the back ground logic here but the example below may give
you enough to work with.

'=IF(AND(J15<J14,H15<J14),I10&" F1",IF(AND(J15<J14,I15<J14),I10&"
F2",IF(AND(H15<J14,I15<J14),I10&" F1 F2","")))



Greetings from Bill K
New Zealand

 

[Khalil Handal]

Hi Bill,
Your formula works well for one subject,
I will try o apply it for the other 12 subjects.

The idea is that if the student has > 50% in all subject he will pass else
there is two possibilites:
1- if he has <50% in three subjects he needs to re-do the exam
2- if he has <50 in four or more subjecs he fails.
I am thinking of having another intermediary sheet to do the calculations
and then copy all the values back to the riginal sheet; do you think this is
a good idea?

I will be in touch incase of more difficulties.

 

[Stephen POWELL]

Hello Khalil:
I may be oversimplifying this so please excuse me if I've got it wrong.
The formula SMALL((H15,I15,J15,...,AT15),4) will give you the fourth lowest
mark. If this is <50% then you know the student has 3 other marks less that
50%, but if it produces a value of 50% or more then you need to add a second
formula that changes the "4" to a "3" to see if the student had 3 marks under
50%.
Stephen Powell