Conditional formatting/IF/both of these?

[Jarred]

Hi all, hopefully just a quick one for someone!

I'm setting up a control chart in Excel 2010 for some laboratory testing and I'm trying to have a Cell display "Run Pass" (Highlight in Green), "Run Fail" (Highlight in Red) or "Run Pass, raise QCP for one ref out of limits" (Green highlight) based on a number of scenarios.

What I have is a reference sample that is tested in duplicate in every run and it has to meet some criteria in order for the run to pass:
1: At least one of values should be within a certain range in order for the run to pass. If both are outside this range run fails, if both are in run passes. If one is in and one is out, run passes but we need to raise it in our quality system and I'd like it to display some sort of test rather than just "pass".
2: The difference between the two duplicates must be less than a value set in another Cell

Pretty much I want Cell F5 on Sheet 2 to display the Run Pass/Fail + appropriate highlights if Cell E5 on Sheet 2 is less than Cell F207 on Sheet 1, AND, if Cell D5 and D6 on Sheet 2 are Between Cells F211 and F212 on Sheet 1.

If Both D5 and D6 (sheet 2) are not between F211 and F212 on sheet 1 I want it to display the "Run Fail" text and appropriate highlight, BUT if one of them IS between, but the other not, I want it to show the Pass highlight, but a different text than just "Run Pass", something like: "Run Pass, Raise QCP for 1 Ref outside of limits"

Pretty complicated I'm sorry. Far beyond my (limited) expertise in excel!

Thanks in advance!!

Jarred.

 

[skywriter]

I'm working on something for you but I have questions.

Pretty much I want Cell F5 on Sheet 2 to display the Run Pass/Fail + appropriate highlights if Cell E5 on Sheet 2 is less than Cell F207 on Sheet 1, AND, if Cell D5 and D6 on Sheet 2 are Between Cells F211 and F212 on Sheet 1.

So the text you want displayed here is "Run Pass/Fail" is this correct?
So when you say D5 and D6 are between F211 and F212, then F211 is less than F212 and D5 will be greater than F211, but less than F212 and the same for D6

Example D5 is 4 D6 is 5 and F211 is 3 and F212 is 6.

If Both D5 and D6 (sheet 2) are not between F211 and F212 on sheet 1 I want it to display the "Run Fail" text and appropriate highlight, BUT if one of them IS between, but the other not, I want it to show the Pass highlight, but a different text than just "Run Pass", something like: "Run Pass, Raise QCP for 1 Ref outside of limits"

So here the first part of your first request still holds true, you want that checked also?
The part about E5 being less than F207?

 

[Jarred]

I'm working on something for you but I have questions.

So the text you want displayed here is "Run Pass/Fail" is this correct?
So when you say D5 and D6 are between F211 and F212, then F211 is less than F212 and D5 will be greater than F211, but less than F212 and the same for D6

Example D5 is 4 D6 is 5 and F211 is 3 and F212 is 6.

"Run Pass" if yes or "Run Fail" if no effectively. So yeah, If D5 = 4 and D6 = 5 and F211 is 3 and F212 is 6 I would have it display "Run Pass". If either D5 or D6 is say, 1 I want it to say "Run Pass, but raise QCP for one Ref Outside of Limits". If BOTH D5 and D6 are outside of 3 to 6 I want it to say "Run Fail"

Ive come up with this so far and it does pretty much what I need it to to satisfy the above, I just need to figure out how to have the same cell include what is described after your quote below

=IF(AND(D5,D6>=Sheet1!F211,D5,D6<=Sheet1!F212),"Run Pass", "Run Fail")

So here the first part of your first request still holds true, you want that checked also?
The part about E5 being less than F207?

Yes, If E5 is less than F207 It'll pass the run and display "Run Pass", if its larger it'll display "Run Fail"

Thanks in advance!

 

[skywriter]

Your conditional formatting would be based on the words in the cell.

<sheet1!f207,if(and(d5><sheet1!f212,d6><sheet1!f212),"run pass",if(or(and(d5=""><sheet1!f212),and(d6><sheet1!f212)),"run pass,="" raise="" qcp="" for="" 1="" ref="" outside="" of="" limits","run="" fail")),"run="" fail")[="" code]

Sheet2

*	B	C	D	E	F
4	*	*	*	E5	Formula
5	*	D5	5	2	Run Pass
6	*	D6	6	*	*
7	*	*	*	*	*
8	*	*	*	*	*
9	*	*	*	*	*
10	F207	3	*	*	*
11	F211	4	*	*	*
12	F212	8	*	*	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F5 =IF(E5<sheet1!f207,if<span style=" color:008000; ">(AND(D5>Sheet1!F211,D5<sheet1!f212,d6>Sheet1!F211,D6<sheet1!f212)< span="">,"Run Pass",IF(OR(AND(D5>Sheet1!F211,D5<sheet1!f212)< span="">,AND(D6>Sheet1!F211,D6<sheet1!f212)< span="">)</sheet1!f212)<>,"Run Pass, Raise QCP for 1 Ref outside of limits","Run Fail")</sheet1!f212)<>),"Run Fail")</sheet1!f212)<></sheet1!f212,d6></sheet1!f207,if<span> <tbody> </tbody>

<tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format F5 1. / Formula is =F5="Run Pass" Abc F5 2. / Formula is =F5="Run Pass, Raise QCP for 1 Ref outside of limits" Abc F5 3. / Formula is =F5="Run Fail" Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4 </sheet1!f212)),"run></sheet1!f212),and(d6></sheet1!f212),"run></sheet1!f212,d6></sheet1!f207,if(and(d5>

 

[skywriter]

The full formula keeps getting cut off when I try to paste it. Let me know if you have problems copying and pasting from above. Maybe the full formula is there and it just looks funny.

 

[skywriter]

Okay the Mr. Excel HTML maker seems to be a better choice.

<sheet1!f207,if(and(d5><sheet1!f212,d6><sheet1!f212),"run pass",if(or(and(d5=""><sheet1!f212),and(d6><sheet1!f212)),"run pass,="" raise="" qcp="" for="" 1="" ref="" outside="" of="" limits","run="" fail")),"run="" fail")[="" code]

Excel 2010</sheet1!f212)),"run></sheet1!f212),and(d6></sheet1!f212),"run></sheet1!f212,d6></sheet1!f207,if(and(d5>

	F
5	Run Pass

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula F5 =IF(<font color="Blue">E5<sheet1!f207,if(<font color="Red">AND(D5>Sheet1!F211,D5<sheet1!f212,d6>Sheet1!F211,D6<sheet1!f212< font="">),"Run Pass",IF(OR(AND(D5>Sheet1!F211,D5<sheet1!f212< font="">),AND(D6>Sheet1!F211,D6<sheet1!f212< font="">)</sheet1!f212<>),"Run Pass, Raise QCP for 1 Ref outside of limits","Run Fail"</sheet1!f212<>)),"Run Fail")</sheet1!f212<></sheet1!f212,d6></sheet1!f207,if( <tbody> </tbody>

<tbody>
</tbody>

 

[skywriter]

The formula is getting messed up somehow. The html makers are either cutting parts off or changing the formula. I don't know if it's because of the sheet names in the formula or the length of the formula is causing a problem.