Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: 3 consecutive

  1. #1
    Seeker chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    6
    Articles
    0
    Excel Version
    2016

    3 consecutive



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a worksheet with 12 columns.
    Column b2:k2 have numbers in them. I need a formula that can provide some kind of indicator when 3 consecutive cells have the number 0 in them after at least one of the cells prior to the 0 have a different number in them.
    Example: 0,0,2,0,4,12,1,0,0,0 (this would be true)
    0,0,0,0,0,8,9,0,0,8 (this would be false)


    Sent from my iPhone using Tapatalk

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    877
    Articles
    0
    Excel Version
    Excel 2013
    Take your data to A1. There are no empty cells. Try this ARRAY formula (finished with Ctrl+Shift+Enter)
    This formula counts three consecutive zero numbers.
    Code:
    =IF(MAX(FREQUENCY(IF(A1:J1=0;COLUMN(A1:J1));IF(A1:J1<>0;COLUMN(A1:J1))))=3;TRUE;FALSE)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Seeker chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    6
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by navic View Post
    Take your data to A1. There are no empty cells. Try this ARRAY formula (finished with Ctrl+Shift+Enter)
    This formula counts three consecutive zero numbers.
    Code:
    =IF(MAX(FREQUENCY(IF(A1:J1=0;COLUMN(A1:J1));IF(A1:J1<>0;COLUMN(A1:J1))))=3;TRUE;FALSE)
    I tried the formula and I知 getting an error, I知 attaching the pictures so you can see.

    Also I need the formula to start looking for 3 consecutive zeros after a number greater then zero appears in one of the cells prior.
    For example:
    0,0,0,0,0,0,6,0,7,8 (this would be false)
    0,0,0,0,0,0,6,0,0,0 (this would be true)




    Sent from my iPhone using Tapatalk

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    132
    Articles
    0
    Excel Version
    2019
    You are missing a closed Parens ie. ) in your formula and it appears that you have not clicked on Control Shift Enter to make it an array formula as instructed.

  5. #5
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,635
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @chtris
    Hi and welcome
    please do not quote entire posts unnecessarily. They make thread hard to read.
    Also, please do not copy paste images in posts, but attach the worksheet. Pictures cannot be worked with.
    Thanks
    Thank you Ken for this secure forum.

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    877
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by chrisl614 View Post
    I tried the formula and I知 getting an error, I知 attaching the pictures so you can see.
    Please see my signature. Maybe it's a delimiter problem.
    See attached file.
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #7
    Seeker chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    6
    Articles
    0
    Excel Version
    2016

    3 consecutive

    Seems to be close to work but the formula might need some tweaking. Take a look at the file. (I appreciate the help)
    I can稚 upload the file through my iPhone (only pictures and I was having a hard time logging into the forum on my pc) I知 attaching an image for now so you can see what I知 talking about

    Click image for larger version. 

Name:	IMG_9235.jpg 
Views:	7 
Size:	10.3 KB 
ID:	9014
    Last edited by chrisl614; 2019-04-07 at 12:15 AM.

  8. #8
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    132
    Articles
    0
    Excel Version
    2019
    The problem with pictures is that they are unreadable. Your post is of no value in attempting to help you.

  9. #9
    Seeker chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    6
    Articles
    0
    Excel Version
    2016
    I was finally able to log in on my computer.
    I've attached the file
    Attached Files Attached Files

  10. #10
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    877
    Articles
    0
    Excel Version
    Excel 2013

    3 consecutive cells of the same value

    Try new solution in attached file.
    Did it help?
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Page 1 of 2 1 2 LastLast

Posting Permissions

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