Conditional formatting formula for cell

[Alpacino]

Hi all. Wonder if anyone can help.

How do you put this formula into a conditional formatting formula based on if the answer is “Pass” the cell is green, “Inter” the cell is yellow and fail it’s red.

PASS =IF(SUM(D3:$H3)<=$C3
INTER =IF(AND(SUM(D3:$H3)>$C3,SUM(E3:$H3)<$C3)
FAIL = any other value is fail

many thanks

Al

 

[kweaver]

Put this into A3:

=IF(SUM(D3:$H3)<=$C3,"PASS",IF(AND(SUM(D3:$H3)>$C3,SUM(E3:$H3)<$C3),"INTER","FAIL"))

And make the conditional for A3 be 3 conditions:

="PASS"=A3 (then set background to green)
="INTER"=A3 (set to yellow)
="FAIL"=A3 (set to red)

 

[Peter_SSs]

How do you put this formula into a conditional formatting formula based on if the answer is “Pass” the cell is green, “Inter” the cell is yellow and fail it’s red.

What cell or cells are you trying to colour?
Could you give a small set of sample data and explain your requirement in relation to that sample data?

 

[Alpacino]

Thanks for replying

https://www.dropbox.com/s/gbc9lkfd6memmyx/IMG_5177.jpg?dl=0

Ill try to explain. Basically the story is.... there is a school trip and only certain number of pupils based on if they scored highest. If however there are too many that have the same score they will have to be Interviewed “INTER” e.g. Maths there are 8 places, there are currently 14 pupils in class. 4 pupils scored 4, passed, 2 pupils scored 4, passed but 4 pupils scored 3, so that group is “INTER” and the rest fail.

hope that explains it.

Thank you

 

[Peter_SSs]

hope that explains it.

I think so. For this sample select D3:H5 and enter the CF rules (formulas) as shown. Make sure your rules end up in this order when you go in to Conditional formatting -> Manage Rules ... (you can select and move the rules up/down in there if required)

Excel Workbook
	C	D	E	F	G	H
3	6	0	1	2	4	2
4	8	0	4	4	2	4
5	6	1	0	3	5	3
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3	1. / Formula is =SUM(D3:$H3)<=$C3	Abc
D3	2. / Formula is =AND(SUM(D3:$H3)>$C3,SUM(E3:$H3)<$C3)	Abc
D3	3. / Formula is =D3<>0	Abc

 

[Alpacino]

Brilliant thank you so much &#55357;&#56842;

 

[Peter_SSs]

Brilliant thank you so much

No problem. They were basically your formulas.