Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Works in one place but not another.

  1. #1
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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


  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •