Hi, Mr. Humar Helped me with an IF condional formatting. I really thnk him coz without him i could never finish the cod.

These are the codes:

=IF(MAX(B2:H2)>100,true,false)

=IF(AND(COUNTA(B2:H2)=7,MIN(B2:H2)<50),true,false)=IF(AND(COUNTA(B2:H2)=7,MIN(B2:H2)>=50),true,false)

=IF(MAX(B2:H2)>100,"Error",

IF(AND(COUNTA(B2:H2)=7,MIN(B2:H2)<50),"Fail",

IF(AND(COUNTA(B2:H2)=7,MIN(B2:H2)>=50),"Pass","")))

These codes are soposed to do the following:If I have a student called Humar and he passed all the 7 subjects his name will turn to green color and he will get PASS

But if he fail in atleast 1 subject his name will turn to red color and he will get FAIL

to pass he must get atleast 50 in each subject

also if he bring more than hundred his name will turn yo yellow and he get "an error message"

if no marks are entered it will return empty with no colour.My Problem now

I need if the student falied in three (3) subjects his name will return to purple and it Must return a meesage:" Another Chance"

its mean if the student failed in 3 subjects can make in the vecation the subjects which he failed on an having another chance to do the exam.

but if the student failed on more than 3 subjects his name will turn to red and it will return a message ( I mean by message a cell wich return no chance or chance) the message will be ( no chance) thats he will stay ( remain in the same class for next year with no chance to make any exam.please i really need help on this manner.

I hope that Mr. humar can help me

I know that i was asking many question but he is a really good teacher.please Mr.Humar help me, you was with me from the beginning of these codes.

with all my respects,

regards,

Maram

Hi, From what you say, the "Fail" condition is now subdivided into two conditions:

1. Fail, but student can retake up to three subjects

2. Fail, but student cannot retake any subjects as they have failed 4 or more.The message part can be done with this formula:

=IF(MAX(B2:H2)>100,"Error", IF(AND(COUNTA(B2:H2)=7,COUNTIF(B2:H2,"<50")>3),"Fail, no retake", IF(AND(COUNTA(B2:H2)=7,MIN(B2:H2)<50),"Fail, retake allowed", IF(AND(COUNTA(B2:H2)=7,MIN(B2:H2)>=50),"Pass",""))))I have split the formula onto several lines for ease of viewing.

Change the wording of the two Fail messages as required.There are now five conditions:

Pass

Fail, retake allowed

Fail, no retake

At least one score >100

At least one missing scoreIn Excel 2003, there are only three conditional formats allowed, so including the original cell color, four colors are possible. So if you have Excel 2003, conditional formatting will not work.

If you are using Excel 2007, more than three conditional formats are allowed.

The additional formula in the second position is:

=IF(AND(COUNTA(B2:H2)=7,COUNTIF(B2:H2,"<50")>3),True,False)

with a format cell color of purple.I do not have Excel 2007 on this PC so I have not been able to test it.

If you are using Excel 2003, you have two choices:

1. Stay with three conditional formats with red for Fail (retake allowed or retake not allowed) and depend on the message to separate the two conditions.

2. Write a macro which is driven by the change event, so that if data is entered into any of the cells containing exam marks, the macro runs, and the macro changes the color of the cell containing the student's name.If you have Excel 2003 and you want to use option #2, I need to know which cells contain the names (the names that change color), and which cells contain the marks of the seven subject exams.

Regards

Thank you very much Mr.Humar am going to test it now

just give me some time.but mr. humar i have a prople my marks are not sequeenze i mean they are like this

name id nummbe mark1 grade mark2 grade

my marks are not like

50 100 90 98

i mean i cannot use b2:b7 for example coz the are foolowed up there are 2 cells after each other and in count if i canot use a;b;c;d;e

i hope that you understand me

they are like this

b;f;i,..... the marks i have

and yes i have 2007

regards

Hello Mr Humar thank you very Much the first frmula is working

can you check please what i have done i solve the problem for the marks in this way

=IF(MAX(C6;F6;I6;L6;O6;R6;U6)>100;"Error";IF(AND(COUNTA(C6;F6;I6;L6;O6;R6;U6)=7;COUNTIF(C6:C6:F6:F6:I6:I6:L6:L6:O6:O6:R6:R6:U6:U6;"<50")>3);"Fail, no retake";IF(AND(COUNTA(C6;F6;I6;L6;O6;R6;U6)=7;MIN(C6;F6;I6;L6;O6;R6;U6)<50);"Fail, retake allowed";IF(AND(COUNTA(C6;F6;I6;L6;O6;R6;U6)=7;MIN(C6;F6;I6;L6;O6;R6;U6)>=50);"Pass";""))))

see what i have done in the countif part is it right coz its worked with me

but for the colur part it didnot work its not coloring it

please help me

regards,

Maram

Hi, You originally said that your data looked like this:

Maram 89 50 10 19 80 100 90 Humar 100 90 98 99 97 91 99.5

Now you are saying that the data is not in consecutive cells:B C F I L O R U Maram 89 50 10 19 80 100 90 Humar 100 90 98 99 97 91 99.5

If you look at the Help files for COUNTIF() and MIN() or COUNTA(), you will see that the structure of the arguments is different.

As far as I can see you can't use COUNTIF() on a series of values - it has to be a single continuous range.This means that the approach to getting your required result has to be changed.

I doubt I can get round to this today due to work commitments.

Regards

Hi, I replaced the COUNTIF() with a series of tests for values less than 50 and used SUM() to add them, on the basis that TRUE is counted as 1 and FALSE is counted as zero.

=IF(MAX(C6,F6,I6,L6,O6,R6,U6)>100,"Mark error",

IF(AND(COUNTA(C6,F6,I6,L6,O6,R6,U6)=7,

SUM(C6<50,F6<50,I6<50,L6<50,O6<50,R6<50,U6<50)>3),"Fail, no retake",

IF(AND(COUNTA(C6,F6,I6,L6,O6,R6,U6)=7,

MIN(C6,F6,I6,L6,O6,R6,U6)<50),"Fail, retake allowed",

IF(AND(COUNTA(C6,F6,I6,L6,O6,R6,U6)=7,

MIN(C6,F6,I6,L6,O6,R6,U6)>=50),"Pass","Missing result"))))You can use the elements of this formula to create the series of TRUE/FALSE formulas for use in Conditional formatting.

As before, the formula has been split onto several lines for ease of viewing.

Regards

Thank you very much Mr. Humar am going to tryit and mergit with my program

I will bring for you the good news

thank you very much

with all my respects

maram

Thank you very Much Mr.Humar it is working ...... :)

am so happy.

Many thanks for you.

with all my respects

Maram

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History