HOWTO: What is the formula to have evaluate a range of value

[apache007]

Hi All,

Let say I have data Coloum A1:A100 containes number data
and I want to HIGHLIGHT using Conditional Formatting, those cells that have
the following criteria:

1. Is not empty
2. Has a value between 5 and 20.

I don't know the formula for Criteria #2.

Thanks.

 

[Fred Smith]

In A1, enter:
=and(a1<>"",a1>=5,a1<=20)

Copy the format to the other cells.

Regards,
Fred

 

[David-Melbourne-Australia]

Hi Apache007

If I've understood correctly, you want to highlight cells in A1:A100 which
are not empty and contain a value between 5 and 20 using Conditional
Formatting.

To achieve this, you only need one criteria - Go into Conditional Formatting
and set the following:

Condition 1
Cell Value Is between 5 and 20

and then obviously set the highlighting you want under the Format button.

You don't need to check for non-blank cells as a blank cell simply fails to
meet the requirement of being between 5 and 20.

Hope this helps.

 

[apache007]

Hi David,

The thing is that Criteria 2 must be a formula, because I want to evaluate a
DATE condition that meet value between 5 and 20.

On my cell: I want to caculate If
8 Jun 2010 - Today () is it between 5 and 20.
If so, Formatting applied.

 

[Fred Smith]

Do you mean between 5 and 20 days before today, or after today? If after,
use:
=and(a1<>"",a1+5>=today(),a1+20<=today())

Regards
Fred

 

[David Biddulph]

Do you need the <>"" condition?
If the cell is empty or contains "", would it satisfy =and(a1>=5,a1<=20) ?

 

[Joe User]

On my cell: I want to caculate If
8 Jun 2010 - Today () is it between 5 and 20.
If so, Formatting applied.

If you want to apply the format when the cell value is between TODAY() plus
5 days and TODAY() plus 20 days inclusive, you only need the one criteria:

Cell Value Is between =TODAY()+5 and =TODAY()+20

Thus, if today is 6 Feb 2010, the format is applied when the cell value is
between 11 Feb 2010 and 26 Feb 2010 inclusive. The format will not be
applied when the cell value is 10 Feb 2010 or less or 27 Feb 2010 or more.

If I have misunderstood your requirement, please provide a precise
description like the paragraph above. That is, if today is dd mmm yyyy
(fill in the date), for what dates (earliest and latest) do you want the
format to be applied?

I am suspicious of my interpretation because your example uses a date that
is so far outside the range (of days).


----- original message -----

 

[Joe User]

Do you mean between 5 and 20 days before today, or after today?
If after, use:
=and(a1<>"",a1+5>=today(),a1+20<=today())

If A1+5>=TODAY() is true, then A1+20>TODAY() is true. So
AND(A1+5>=TODAY(),A1+20<=TODAY()) is always false.

Moreover, I believe A1+5>=TODAY() is true when A1 is 5 days or less before,
not after, TODAY().

I presume you meant to write AND(A1<>"",TODAY()<=A1-5,A1-20<=TODAY()), which
is true if the date in A1 is between 5 and 20 days after today.

But the condition A1<>"" is superfluous for a "Formula Is" conditional
format. It is useless in a normal formula because if A1="", the expressions
A1+5 and A1+20 cause a #VALUE error.

It would be more correct to write:

AND(A1<>"",TODAY()+5<=A1,A1<=TODAY()+20)

But the condition A1<>"" is still superfluous for a normal formula as well
as for a "Formula Is" conditional format. Since text is always considered
greater than a numeric value, it is sufficient to write:

AND(TODAY()+5<=A1,A1<=TODAY()+20)

That is false when A1="" because A1<=TODAY()+20 is false.


----- original message -----

 

[Fred Smith]

No, but the OP asked for it, so I thought I would show how to check for
non-blank.

Regards,
Fred

 

[Joe User]

No, but the OP asked for it, so I thought I would
show how to check for non-blank.

But if it's superfluous, we can educate the OP on that point.

There is never an instance where AND(A1<>"",A1+5>=B1,...) does what you (or
the OP) might think it does.

See my more detailed explanation in a response to your later posting.


----- original message ------