3 consecutive

chrisl614

Member
Joined
Apr 6, 2019
Messages
30
Reaction score
0
Points
6
Excel Version(s)
2016
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
 
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)
 
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’m getting an error, I’m 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)

ae432b9383ae2c629e78c52a1115064f.jpg
73b52a76138c432158cb6d3b9b022643.jpg



Sent from my iPhone using Tapatalk
 
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.
 
@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
 
I tried the formula and I’m getting an error, I’m attaching the pictures so you can see.
Please see my signature. Maybe it's a delimiter problem.
See attached file.
 

Attachments

  • chrisl614-navic9922.xlsx
    8.2 KB · Views: 10
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’t upload the file through my iPhone (only pictures and I was having a hard time logging into the forum on my pc) I’m attaching an image for now so you can see what I’m talking about

IMG_9235.jpg
 
Last edited:
The problem with pictures is that they are unreadable. Your post is of no value in attempting to help you.
 
I was finally able to log in on my computer.
I've attached the file
 

Attachments

  • chrisl614-navic9922.xlsx
    8.8 KB · Views: 10
3 consecutive cells of the same value

Try new solution in attached file.
Did it help?
 

Attachments

  • chrisl614-navic9922-v2.xlsx
    9.7 KB · Views: 4
Didn't work. line 4 should be true based off the 3 consecutive zeros after the d9 not before. line 5 should be true based since there are 3 consecutive zeros after c5.
need it to be true only after 3 or more zeros after the first number greater then zero, in the row.
 

Attachments

  • chrisl614-navic9922-v2.xlsx
    9.9 KB · Views: 5
Didn't work. line 4 should be true based off the 3 consecutive zeros after the d9 not before.
Look at a new solution.
If this does not help, I give up.
 

Attachments

  • chrisl614-navic9922-v3.xlsx
    9.9 KB · Views: 8
Thanks! But the formula is still not able to accomplish what I was looking for. It seems like the closest it’s been is with this formula:

{=IF(MAX(FREQUENCY(IF(A2:J2=0,COLUMN(A2:J2)),IF(A2:J2<>0,COLUMN(A2:J2))))>=3,TRUE,FALSE)}

The only problem with this formula is that it doesn’t start looking for zero after the first number higher then zero.


Sent from my iPhone using Tapatalk
 
Thanks! But the formula is still not able ...
Okay, here's another attempt. This is ARRAY Formula
Code:
=OR(IFERROR(IF(COUNTIF(INDIRECT(CHAR(64+LOOKUP(2;1/(A1:J1>0);COLUMN(A1:J1)))&ROW(A1)&":J"&ROW(A1);TRUE);"=0")>0;TRUE;FALSE);FALSE);IFERROR(IF(COUNTIF(INDIRECT(CHAR(64+LARGE(IF(A1:J1>0;COLUMN(A1:J1));2))&ROW(A1)&":J"&ROW(A1);TRUE);"=0")>=3;TRUE;FALSE);FALSE))
 

Attachments

  • chrisl614-navic9922-v4.xlsx
    10.5 KB · Views: 6
Back
Top