Works in one place but not another.

[kojak43]

Yesterday, Adadin provided a neat formula that allowed dates over 90 days old to be counted on my Statistics worksheet. When I test it, it showed 6 jobs that were over 90 days old.
When I went to my DataEntry worksheet I had to search for those dates. I thought, well I have the makings of a conditional formatting formula. All I have to do it change it a little and use it there. So I changed it to read:
=AND($F$1-DATES>=90,STATUS<>"D") I added =today() to $F$1 in DataEntry sheet (Just like I did in Statistics worksheet).
And it just sits there like a lump. I expected if a date was more than 90 Days older than today, and it did not have a "D" in the Status column, it would turn Bold Red. Excel accepts the formula, i.e. it does not say there is an error, it justs does not work.
What have I done wrong?

[Aladin Akyurek]

On 2002-04-18 08:24, kojak43 wrote:
Yesterday, Adadin provided a neat formula that allowed dates over 90 days old to be counted on my Statistics worksheet. When I test it, it showed 6 jobs that were over 90 days old.
When I went to my DataEntry worksheet I had to search for those dates. I thought, well I have the makings of a conditional formatting formula. All I have to do it change it a little and use it there. So I changed it to read:
=AND($F$1-DATES>=90,STATUS<>"D") I added =today() to $F$1 in DataEntry sheet (Just like I did in Statistics worksheet).
And it just sits there like a lump. I expected if a date was more than 90 Days older than today, and it did not have a "D" in the Status column, it would turn Bold Red. Excel accepts the formula, i.e. it does not say there is an error, it justs does not work.
What have I done wrong?

AND returns a single logical value and $F$1-DATES is an array result with multiple elements.

Lets say dates are in E fro E3 on and status values in R from R3 on. Select all E-values, choose Formula Is for Condition 1, enter

=AND(TODAY()-E3>=90,R3<>"D")

and choose a formatting you wish.

Aladin

[Al Chara]

Make sure you actually selected a format to be used in your conditional format. Plenty of times I have caught myself hitting OK without actually selecting a formula.

Your conditional format worked for me. I assume that DATES and STATUS are named ranges. Make sure that they are setup correctly.

Ok Aladin, I didn't know that he had DATES and STATUS setup as arrays. I took them as single cell ranges.

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-04-18 08:52 ]

[kojak43]

=AND($F$1-DATES>=90,STATUS<>"D") I added =today() to $F$1 in DataEntry sheet What have I done wrong?

AND returns a single logical value and $F$1-DATES is an array result with multiple elements.



Aladin
[/quote]

Thanks. Naturally it worked.
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?
Thanks for all your help.
K

[Aladin Akyurek]

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;5 3;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;FAL SE}

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 ]