complicated If statements

K

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!
 
B

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
 
K

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.
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top