Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Count

  1. #1
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha,

    I would like to count these events as 1. Lost again

    d1-c1>30
    c1>a1
    d1>b1

    Mahalo

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I assume you are looking for an If statement.

    =IF(AND(D1-C1>30,C1>A1,C1>A1), "true", "false")

    Replace "true" with what you want to see in the cell if its true.
    Replace "false" with what you want to see in your cell if its false

    Hope this is what you are looking for
    Derek

  3. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Derek,

    Not quite. I have time values in ABCD

    10:00 10:40 10:10 10:41

    It meets the criteria, but doesn't count as 1

    Mahalo

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

    Default

    question - if you have time values in the cells, how will D1-C1>30 ever be true? am i missing something? do you have dates as well?

  5. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    d1-c1 is 31 mins

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    oh, i see. i thought it would count that as .517. have never been good with time formats...thanks

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hang on. =D1-31 returns .31, not 31, so you will have amend the test condition from 30 to .30 (or will it be .5?)

  8. #8
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried formatting target cell to number, general, and time..no go. changed 31 to .31 and :31 doesn't count it ..

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

    Default

    On 2002-02-28 20:31, Brian from Maui wrote:
    Aloha,

    I would like to count these events as 1. Lost again

    d1-c1>30
    c1>a1
    d1>b1

    Mahalo
    Brian,

    Consider the following sample, where the funny numbers are Excel's internal representation of actual times,

    {0.416666666666667,0.444444444444444,0.423611111111111,0.445138888888889;
    0.40625,0.427083333333333,0.409722222222222,0.430555555555556;
    0.427083333333333,0.447916666666667,0.430555555555556,0.456944444444444}

    in A1:D3.

    In E1 enter & copy down:

    =((D1-C1)*1440>30)*(C1>A1)*(D1>B1)

    You can then sum column E to get desired count:

    =SUM(E:E)

    or

    =SUM(E1:E3)

    However, you can also arrive at the desired count by means of a single formula:

    In F1 enter:

    =SUMPRODUCT(((D1:D3-C1:C3)*1440>30)*(C1:C3>A1:A3)*(D1:D3>B1:B3))

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-01 00:35 ]

  10. #10
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    "AWESOME"



    Did you read my funny story?

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
  •