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

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" wrote in message
...
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

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 C9:D9, 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ballastrae" wrote in message
...
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

Here's a sample file, with the CF implemented:
http://cjoint.com/?cmpotdmaIV
ballastrae_wks.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks Bob

"Bob Phillips" wrote:

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" wrote in message
...
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

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" wrote:

Here's a sample file, with the CF implemented:
http://cjoint.com/?cmpotdmaIV
ballastrae_wks.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"ballastrae" wrote
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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks for the help it a great help

"Max" wrote:

"ballastrae" wrote
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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Pleasure`, ballastrae !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ballastrae" wrote in message
...
Thanks for the help it a great help