Conditional Formatting with two cells

[ballastrae]

I work in quality and I am trying to write a certificate that will indicate
if a product does not meet specifications. The problem that I have is the
product has a min and a max value. Example: min 45 max 78. I would like the
results section to flag in either bold, italic or a color. The result that I
key in equals the 45 and 78, is good between this range-so I am trying to set
it up for this example if my test product value is below 45 and/or above 78.

I guess I could word this a little better--=to 48 or greater, between 48 and
78, =to 78
Here is an example of what I have:
Description Measure Min. Max. Result Result Result
Wax Percentage 48.0 78.0 48.72 78.0 100.0

The min and max are locked and the "Result" section is what has to be keyed
in when the data is found. So, this is exactly like it would look like on a
certificate of conformance. Also, I may have many different samples which I
would have to key the results for.

So my excel sheet may look like B9 and D9 for the Max and Min and the
“Result†could be F9, G9, H9, I9, etc.

I juct can't seem to write conditional that will work

 

[Bob Phillips]

What are you looking for, conditional formatting, if so where, or a formula
for F9?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Max]

Description Measure Min. Max. Result Result Result

Wax Percentage 48.0 78.0 48.72 78.0 100.0

Assume the Min and Max values are in C99, Results in E9:G9

Select E9:G9 (with E9 active)

Click Format > Conditional Formatting
& make the following settings:

Condition 1, Formula is:
=AND($C9<>"",$D9<>"",E9>=$C9,E9<=$D9)
Click Format button > Patterns tab > Green > OK

Click "Add"

Condition 2, Formula is:
=AND($C9<>"",E9<>"",E9<$C9)
Click Format button > Patterns tab > Blue > OK

Repeat to add condition 3..
Condition 3, Formula is:
=AND($D9<>"",E9>$D9)
Click Format button > Patterns tab > Red > OK

Click OK at the main dialog

Result values within E9:G9 which fall within the tolerance
(between min to max) will trigger green fill,
those below the min will trigger blue fill,
and those above the max will trigger red fill

The fill colours chosen above resemble typical "temperature" ratings <g>:
"just nice" - green, "too cold" - blue, "too hot" - red

The additional checks for no values input into either C9 and/or D9,
and for no value(s) within E9:G9 (eg: should cell(s) are cleared),
will help to ensure that the 3 cond formats are correctly triggered

 

[Max]

Here's a sample file, with the CF implemented:
http://cjoint.com/?cmpotdmaIV
ballastrae_wks.xls

 

[ballastrae]

Thanks Bob

What are you looking for, conditional formatting, if so where, or a formula
for F9?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[ballastrae]

Max,
Thanks for the help. It was great!!!!!!!! Thanks for the file.
I was looking on the site for how to do if their was a way to do for
pass/fail for certain sections if you can help with this.
Ballastrae

 

[Max]

Thanks for the help. It was great!!!!!!!! Thanks for the file.

You're welcome !

I was looking on the site for how to do if their was a way to do for
pass/fail for certain sections if you can help with this.

Guess you mean *write* the comparison results of E9:G9 against the limits
out (we did this earlier using the 3 conditional format formulas)

For the same sample set-up,
we could put in say, I9:

=IF(AND($C9<>"",$D9<>"",E9>=$C9,E9<=$D9),"Pass",
IF(AND($C9<>"",E9<>"",E9<$C9),"Under",
IF(AND($D9<>"",E9>$D9),"Over","")))

then copy I9 to K9

The above will yield in I9:K9, the results: Under, Pass, Over

Adapt the checking status messages: "Under", "Pass", "Over" to suit ..

 

[ballastrae]

Thanks for the help it a great help

You're welcome !


Guess you mean *write* the comparison results of E9:G9 against the limits
out (we did this earlier using the 3 conditional format formulas)

For the same sample set-up,
we could put in say, I9:

=IF(AND($C9<>"",$D9<>"",E9>=$C9,E9<=$D9),"Pass",
IF(AND($C9<>"",E9<>"",E9<$C9),"Under",
IF(AND($D9<>"",E9>$D9),"Over","")))

then copy I9 to K9

The above will yield in I9:K9, the results: Under, Pass, Over

Adapt the checking status messages: "Under", "Pass", "Over" to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik

 

[Max]

Pleasure`, ballastrae !
Thanks for the feedback ..