Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
In A1, enter:
=and(a1<"",a1=5,a1<=20) Copy the format to the other cells. Regards, Fred "apache007" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
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. "David-Melbourne-Austraalia" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
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 "apache007" wrote in message ... 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. "David-Melbourne-Austraalia" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
Do you need the <"" condition?
If the cell is empty or contains "", would it satisfy =and(a1=5,a1<=20) ? -- David Biddulph Fred Smith wrote: In A1, enter: =and(a1<"",a1=5,a1<=20) Copy the format to the other cells. Regards, Fred "apache007" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
"apache007" wrote:
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 ----- "apache007" wrote in message ... 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. "David-Melbourne-Austraalia" wrote: 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" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
"Fred Smith" wrote:
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+20TODAY() 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" wrote in message ... 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 "apache007" wrote in message ... 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. "David-Melbourne-Austraalia" wrote: 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" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
No, but the OP asked for it, so I thought I would show how to check for
non-blank. Regards, Fred "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Do you need the <"" condition? If the cell is empty or contains "", would it satisfy =and(a1=5,a1<=20) ? -- David Biddulph Fred Smith wrote: In A1, enter: =and(a1<"",a1=5,a1<=20) Copy the format to the other cells. Regards, Fred "apache007" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOWTO: What is the formula to have evaluate a range of value
"Fred Smith" wrote:
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 ------ "Fred Smith" wrote in message ... No, but the OP asked for it, so I thought I would show how to check for non-blank. Regards, Fred "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Do you need the <"" condition? If the cell is empty or contains "", would it satisfy =and(a1=5,a1<=20) ? -- David Biddulph Fred Smith wrote: In A1, enter: =and(a1<"",a1=5,a1<=20) Copy the format to the other cells. Regards, Fred "apache007" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to Evaluate Range and Sum Different Column | Excel Discussion (Misc queries) | |||
HOWTO Reference a named cell or range in a script | Excel Discussion (Misc queries) | |||
=EVALUATE("{ to work in a range of cells | Excel Worksheet Functions | |||
want sumif function's range to evaluate 2 columns | Excel Worksheet Functions | |||
HOW to Evaluate a range with IF ? | Excel Discussion (Misc queries) |