ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Using Conditional Formatting to Pass/Fail (https://www.excelbanter.com/excel-discussion-misc-queries/261841-using-conditional-formatting-pass-fail.html)

 mrsatroy April 19th 10 04:52 PM

Using Conditional Formatting to Pass/Fail

I want to use conditional formatting to return a pass fail number based on a
numeric value. However the number has to fall between 2 values. i.e A product
will pass if the numeric value it achieves is between 2 and 8 but if the
value is less than 2 or greater than 8 it will fail. I can't seem to get the
if statement to understand the concept of pass being between 2 and 8.
--
mrsatroy

 Jim Thomlinson April 19th 10 05:01 PM

Using Conditional Formatting to Pass/Fail

Conditonal formats return formats and not numbers. The CF should be farily
easy. if Cell value is not Between 2 and 8 then add your pattern. If you want
a value returned then in a differnet cell use a formula something like

=IF(AND(A1=2, A1<=8), 1, 0)
--
HTH...

Jim Thomlinson

"mrsatroy" wrote:

I want to use conditional formatting to return a pass fail number based on a
numeric value. However the number has to fall between 2 values. i.e A product
will pass if the numeric value it achieves is between 2 and 8 but if the
value is less than 2 or greater than 8 it will fail. I can't seem to get the
if statement to understand the concept of pass being between 2 and 8.
--
mrsatroy

 Michael_R April 19th 10 05:05 PM

Using Conditional Formatting to Pass/Fail

Assuming you got your values in column B, starting in B2:

The formula for the pass-rule
=AND(B2=2,B2<=8)

The formula for the fail-rule
=OR(B2<2,B28)

"mrsatroy" wrote:

I want to use conditional formatting to return a pass fail number based on a
numeric value. However the number has to fall between 2 values. i.e A product
will pass if the numeric value it achieves is between 2 and 8 but if the
value is less than 2 or greater than 8 it will fail. I can't seem to get the
if statement to understand the concept of pass being between 2 and 8.
--
mrsatroy

 All times are GMT +1. The time now is 11:05 PM.