Conditional formatting/IF/both of these?

Jarred

New Member
Joined
Sep 22, 2014
Messages
18
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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

New Member
Joined
Sep 22, 2014
Messages
18
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 :)

Code:
=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

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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

*BCDEF
4***E5Formula
5*D552Run Pass
6*D66**
7*****
8*****
9*****
10F2073***
11F2114***
12F2128***

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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
CellNr.: / ConditionFormat
F51. / Formula is =F5="Run Pass"Abc
F52. / Formula is =F5="Run Pass, Raise QCP for 1 Ref outside of limits"Abc
F53. / 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>
 
Last edited:

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642

ADVERTISEMENT

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

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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
5Run Pass

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
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>
 
Last edited:

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,470
Members
409,884
Latest member
Msinmath
Top