IF + OR formula for range
Results 1 to 6 of 6

Thread: IF + OR formula for range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF + OR formula for range

    Hello,
    Can someone please help me with a formula for the following.

    If I have a list of values in the form (minutes:seconds)-
    3:51
    2:01
    4:01
    10:31
    0:39

    What formula allow me to say IF value is between 0-5:00, return '0-5:00', OR IF value is between 5:01-10:00, return 5:01-10:00, etc. I ultimately would like a count of the number of values within each range.

    Thank you

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF + OR formula for range

    Hi, if you have to count each group, below can work for you directly:

    ABCDE
    1TimeBins
    23:510
    32:010:00 - 5:005:005
    44:015:00 - 10:0010:000
    510:3110:00 - 15:0015:002
    60:3915:00 - 20:0020:001
    715:0020:00 - 0:001
    821:00
    90:41
    1016:00

    Sheet6



    Array Formulas
    CellFormula
    E3:E7{=FREQUENCY(A2:A10,D3:D6)}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    Pleae do remember to select E3:E7 before entering Ctrl+Shift+Enter
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: IF + OR formula for range

    Try a regular formula


    Actually in excel you must capture hours:minutes:seconds (hh:mm:ss) as shown below:

     ABCDE
    1minutes:seconds FromToResult
    200:03:51 00:00:0000:05:004
    300:02:01 00:05:0100:10:000
    400:04:01 00:10:0100:15:002
    500:10:31 00:15:0100:20:003
    600:00:39 00:20:0100:25:001
    700:12:05 00:25:0100:30:000
    800:17:05 00:30:0100:35:000
    900:18:10    
    1000:19:15    
    1100:20:20    

    CellFormula
    E2=COUNTIFS($A$2:$A$11,">="&C2,$A$2:$A$11,"<="&D2)


    ----------------------
    But you can change the format of the cell to show only minutes and seconds (mm:ss)

    Example:
     ABCDE
    1minutes:seconds FromToResult
    203:51 00:0005:004
    302:01 05:0110:000
    404:01 10:0115:002
    510:31 15:0120:003
    600:39 20:0125:001
    712:05 25:0130:000
    817:05 30:0135:000
    918:10    
    1019:15    
    1120:20    

    CellFormula
    E2=COUNTIFS($A$2:$A$11,">="&C2,$A$2:$A$11,"<="&D2)
    Last edited by DanteAmor; Jul 18th, 2019 at 10:38 AM.
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Nov 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF + OR formula for range

    Thank you both

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: IF + OR formula for range

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  6. #6
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF + OR formula for range

    Your'e welcome, thanks for the feedback.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

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
  •