I've read and reread your explaination and thought to have an array I had to enter via Shift,Control,Enter. I thought what was done with Dates and Status was a Named Range. (This is getting harder and harder to understand)and if I used those names, I would not have to write e.g. E3:E2000, I could just say Dates.
I understand that AND returns a single result, i.e. 1 or 0, after two conditions are evaluated. But why does an array, located in a single cell have multiple elements? Or do you mean that Dates is one element from one cell and Status is another element from a different cell? (based on my formula)
No, that is not what you are saying, you are saying $F$1-Dates = Array result with multiple elements. Why is it any easier for excel to understand (TODAY()-E3-90,R3<>"D")?
Would it stumble over =AND(Today()-DATES-90,Status<>"D")?
Does this EVER get easier?
You might want to have a look at
http://www.mrexcel.com/wwwboard/messages/8961.html
where I describe how SUMPRODUCT works. Its contents apply also to formulas that you enter with control+shift+enter. Consider the formula:
=SUMPRODUCT((TODAY()-DATES>=90)*(STATUS<>"D"))
Activate the cell of this formula, go to the formula bar, select the TODAY()-DATES bit, and hit F9.
You'll see a constant array of differences:
[0]
{107;107;107;107;107;107;68;68;68;68;68;68;53;53;49;49;49;49;30;30;30;30;30;30;30;18;18;18;18;18;18;18;18;18;18;18;8;8;53;53;53;49;49;18;18;8;8;8}
Now hit the escape key, select the TODAY()-DATES>=90 and hit F9. You'll see a constant array of logical values:
[1]
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Select the STATUS<>"D" bit and hit F9. You'll see another constant array of logical values:
[2]
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}
Hit escape, select the (TODAY()-DATES>=90)*(STATUS<>"D") bit, and F9. You'll get again a consant array, but this time, of 1's and 0's. Multiplying logical values are re-expressed as binary numbers:
TRUE*TRUE == 1
TRUE*FALSE == 0
FALSE*TRUE == 0
FALSE*FALSE == 0
Hence:
[3]
{1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
SUMPRODUCT sums this arrays of 1's and 0's and returns 6.
You can say, non-technically, that SUMPRODUCT or control+shift+enter with array-formulas applies F9 to the arguments.
=AND((TODAY()-DATES>=90),(STATUS<>"D"))
normally entered, will
always return the value corresponding to the evaluation of the first elements of
[1] and
[2], that is:
=AND(TRUE,TRUE) == TRUE
If you array-enter (with control-shift-enter) the above AND-formula, you'll get FALSE as result. A single FALSE anywhere in
[1] or
[2] is sufficient for AND to return FALSE.
When you use the AND formula in conditional formatting cell by cell, the result for each cell be always be the same: either TRUE or FALSE.
Excel understands the formula thus perfectly, but not the way you thought it ought to understand.
On the other hand,
=AND(TODAY()-E3>=90,R3<>"D")
computes a result for just E3.
The above formula becomes in E4:
=AND(TODAY()-E4>=90,R4<>"D")
and computes a result for just E4.
Hope this helps.
Aladin
This message was edited by Aladin Akyurek on 2002-04-18 16:10
