Conditional Formatting Excel 2007

June 5, 2010 at 06:10:12
Specs: Windows Vista

Hi Every One

Am Facing a Proplem In Ecxel 2007

Am Developing a student Report Program
My Problem is:
I Have 7 main subjects, the student must pass in each subject.
pass mark is 50
its like if he bring 7 ( pass) thats mean he passed, but from the 7 pass if he got only 6 (pass) thats mean he failed.
also i need to color the student name
i mean if he pass all the subjects colorit as green, but fail in one subject color it as red
of course if he failed on two or three or four or 5 or 6 or 7 subjects also colorit red.
please help me on this tow manners.
regards
Maram


See More: Conditional Formatting Excel 2007

Report •


#1
June 5, 2010 at 10:40:51

Hi,

You didn't say where any of these results are, so for this example I have a name in cell B2 and seven results in cells B3 to B9.

Select cell B2
From the Ribbon select Home - Styles - Conditional Formatting,
From the drop down select 'Manage Rules' and select 'New Rule'
In the dialog box select the last item in the list 'Use a formula to determine which cells to format'
Enter this formula in the box

=IF(OR(B3<50,B4<50,B5<50,B6<50,B7<50,B8<50,B9<50),TRUE,FALSE)

Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and false.
Click the format button and from the 'Fill' Tab select a red color, click OK
Click OK and select 'New Rule'
Select 'Use a formula to determine which cells to format'
Enter this formula in the box
=IF(AND(B3>=50,B4>=50,B5>=50,B6>=50,B7>=50,B8>=50,B9>=50),TRUE,FALSE)

Click the format button and from the 'Fill' Tab select a green color, click OK
Click Apply and OK

You could just have the name cells formatted red and then just use the second conditional formula to turn them green if all marks are 50 or greater.

You can 'Copy' and 'Paste Special' - Formats to add the conditional formatting to other student's names.

You could change the first formula to this:
=IF(AND(COUNTA(B3:B9)=7,OR(B3<50,B4<50,B5<50,B6<50,B7<50,B8<50,B9<50)),TRUE,FALSE)
then the name would have no color until all 7 marks had been entered.

Regards


Report •

#2
June 5, 2010 at 11:51:19

Hi Mr.Humar

My Sheet look like this:

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

here maram must return fail coz their is 10 in the cell after mark 90 and maram cell must be filled with red color
humar must return pass in the cell after 99.5 and humar must return green

first i need when the student has 7 passes ( 7 subjects marks grater than 50) return word pass.

if the student has at least one mark smaller than 50 it will return fail

because it is nessecarry for the student to brig 7 pass.

and i need the student name to return to red color if he fail
because iam working with 650 students names

and i need to colour it, as i say in my previous post.

if you would like i can send you my sheet.

best regards
maram


Report •

#3
June 5, 2010 at 21:01:49

Hi,

You didn't say which column the names were in.

Assuming that names are in column A and the first name is in cell A2, use the two formulas I suggested before - just change the cells referenced to match what you are using.

Here are the two formulas I have used for a Name in cell A2 and results in cells B2 to H2:
=IF(AND(COUNTA(B2:H2)=7,OR(B2<50,C2<50,D2<50,E2<50,F2<50,G2<50,H2<50)),TRUE,FALSE)
=IF(AND(B2>=50,C2>=50,D2>=50,E2>=50,F2>=50,G2>=50,H2>=50),TRUE,FALSE)

If any marks are missing, no color will be applied to the cell
If all results are present and any result is less than 50, the first format will be applied
and if all results are 50 or greater then the second format will be applied.

You can use the same formulas in cell I2 to show pass or fail (or nothing if any marks are missing):
=IF(AND(COUNTA(B2:H2)=7,OR(B2<50,C2<50,D2<50,E2<50,F2<50,G2<50,H2<50)),"Fail",
IF(AND(B2>=50,C2>=50,D2>=50,E2>=50,F2>=50,G2>=50,H2>=50),"Pass",""))
I have split the formula onto two lines, for ease of viewing - join it back together before pasting it into cell I2.

Regards


Report •

Related Solutions

#4
June 5, 2010 at 23:18:47

Thank you very much Mr.Humar
Its Worked with me.

But i still have one more issue

the marks are out of 100

i need if the student bring more than 100 in a subject i need the name to colored to green.

am trying to doit, but its not working.
regards,
maram


Report •

#5
June 5, 2010 at 23:21:50

sorry Mr. Humar

also for the pass and fail

if the student bring in a subject more than a 100 i want it to give an error "Error" or any thing.

regards,
maram


Report •

#6
June 6, 2010 at 05:15:43

Hi,

As a mark of >100 is an error, and it doesn't matter whether all the other marks have been entered and it does not matter whether the marks indicate a pass or fail, so you need the new condition to be tested first.

Move the existing two formulas down and add this one first:
=IF(OR(B2>100,C2>100,D2>100,E2>100,F2>100,G2>100,H2>100),TRUE,FALSE)
As you already have green for pass and red for fail, I suggest using a different color for this - I used yellow.

As before this new formula can be used for the text - it is also added at the beginning so that it is the first test performed:
=IF(OR(B2>100,C2>100,D2>100,E2>100,F2>100,G2>100,H2>100),"Error",
IF(AND(COUNTA(B2:H2)=7,OR(B2<50,C2<50,D2<50,E2<50,F2<50,G2<50,H2<50)),"Fail",
IF(AND(B2>=50,C2>=50,D2>=50,E2>=50,F2>=50,G2>=50,H2>=50),"Pass","")))

I have split the formula onto three lines, for ease of viewing - join it back together before pasting it into a cell.

You could add a modification of the first conditional format formula on its own to all the cells containing the individual marks, so that any cell with a mark greater than 100 is highlighted. Add the formula to the conditional format for the first cell in the range, testing only that cell. If the first cell with a mark is B2, then the test is for B2>100. (Use B2, not $B$2 as you want the formula to change for each cell in the range). Now Copy the cell and 'Paste Special' - 'Formats' to all the other cells that contain or will contain marks. All these cells will now respond to a mark of >100.

In your response you said am trying to do it, but its not working. It would be helpful if you said what you had tried and what was not working and also included any formulas you had used. This would help the learning process as it may be possible to identify what was wrong and why you were not getting the expected results.

Regards


Report •

#7
June 6, 2010 at 09:39:36

Hi,

Just looked at the formulas again - they can be simplified, although the logic remains the same.

The formula for the text response is:
=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","")))
(Note formula has been split onto 3 lines)

The three conditional format formulas can be simplified using the above approach.

Regards


Report •

#8
June 6, 2010 at 10:51:59

Hi,

Mr. Humar you mean for the conditional format:

the previous one was

=IF(OR(B2>100,C2>100,D2>100,E2>100,F2>100,G2>100,H2>100),TRUE,FALSE)

so you mean it will be like this:

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


and for the second formula the previous one was:

=IF(AND(COUNTA(B2:H2)=7,OR(B2<50,C2<50,D2<50,E2<50,F2<50,G2<50,H2<50)),TRUE,FALSE)


so it will be:

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

and for the last formula the previous one was:

=IF(AND(B2>=50,C2>=50,D2>=50,E2>=50,F2>=50,G2>=50,H2>=50),TRUE,FALSE)

the new formula will be:

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

I hope that am doing right.

regards,
Maram


Report •

#9
June 6, 2010 at 11:28:28

Hi,

Yes - they look right.

I tried it with your formulas and it worked fine.

Regards


Report •

#10
June 7, 2010 at 20:01:20

Hello Mr. Humar

Mr. Humar am facing a problem

I Need if a student failed in 4 subjects ( I mean its ok to fail on 1 or 2 o3 maximum 4 subjects) he will have a second chance to do the exam in the vecation.
here i need to return (second chance) or i dont know what the proper message or text will be.

but if the student failed in more than 4 subjects he will stay in the same grade next year.
here it must be failed with no chance.

its complicated i really dont know how to doit

can i mergit with the previous code for the pass and fail
or its better to keep that to now the failer and make another code.

and for the conditinal formatting :
if he faild in (1 or 2 or 3 maximum 4 subjects) it return maybe purple color for the student name

and if he failed in more than 4 it will return black color
fir the student name.

please help me

regards
maram


Report •

#11
June 7, 2010 at 20:09:12

Maybe we need a vlookup

because the student is failing in the both situation
the only difference its ( if he only failed (minimum 1 maximum 4) he will have a second chance to do the exam. (fail but he have another chance)
but if he fail on more the 4 subject he will stay in the same class and he will nut shift to the upper grade

please help me
regards,
maram


Report •

#12
June 9, 2010 at 12:16:48

please some one help me.

Report •


Ask Question