IF / Conditional Formatting

June 9, 2010 at 20:55:24
Specs: Windows Vista

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:





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,

See More: IF / Conditional Formatting

Report •

June 10, 2010 at 05:41:30

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(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",

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:
Fail, retake allowed
Fail, no retake
At least one score >100
At least one missing score

In 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:
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.


Report •

June 10, 2010 at 05:49:20
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

Report •

June 10, 2010 at 07:09:08
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


Report •

Related Solutions

June 11, 2010 at 05:46:51

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


Report •

June 13, 2010 at 06:22:07

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",
SUM(C6<50,F6<50,I6<50,L6<50,O6<50,R6<50,U6<50)>3),"Fail, no retake",
MIN(C6,F6,I6,L6,O6,R6,U6)<50),"Fail, retake allowed",
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.


Report •

June 13, 2010 at 06:27:43
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


Report •

June 14, 2010 at 03:46:57
Thank you very Much Mr.Humar

it is working ...... :)

am so happy.

Many thanks for you.

with all my respects


Report •

Ask Question