If Both C=1 And D>0 ,then ... ?

[ibmy]

Greetings experienced,

With my limited knowledge of Excel, only know basics, I was thinking to use function
"=IF(AND(N4=1,O3>0), , ) " & "(INDEX(O:O,ROW()-O3-1" ,in formula but do not know how to structure/complete it.

When cell in Column N =1 and above row of it, cell in Column O >0 , assign "1" in cell Column P above row Column O, equal to Value in Column O + 1

Column M = Data
Column N = Confirmation
Column O = Total number of 0
Column P = Expected Result

cell 431.xlsb
	M	N	O	P
1	K	Yes/No	Total 0	Expected Result
2
3	-0.3			1
4	0
5	0
6	0
7	0		4
8	0.1	1		1
9	0		1
10	1.3	1
11	-0.4
12	0
13	0
14	0		3
15	-1.8
16	-0.1			1
17	0
18	0		2
19	-0.1	1
20	0.9
21	0		1
22	0.3
23	-0.2
Sheet1

Cell O14 and O21 show total number 0 but N15 and N 22 show no =1 , so no need to assign "1" in Column P

Sample taken from 300k row data.

 

[DanteAmor]

Hi @ibmy

Could you explain the rules with a couple of examples from your minisheet?

When cell in Column N =1 and above row of it, cell in Column O >0 , assign "1" in cell Column P above row Column O, equal to Value in Column O + 1

For example, in cell N8 there is a 1 and in cell O7 there is a 4 (it is > 0), then the rules are fulfilled.

But your "expected result" is not clear to me.

As well as that example you can explain 2 or 3 examples with the result that you want to understand.



Edit.

I now understand where the result goes:

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[Tetra201]

Try placing the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) in cell P3 and drag-copying it down as needed:

=IFERROR(IF(AND(M3<>0,INDEX(N4:N103,MATCH(TRUE,M4:M103<>0,0))=1,INDEX(O4:O103,MATCH(TRUE,M4:M103<>0,0)-1)>0),1,""),"")

Note 1: I have assumed your longest streak of zeros in column M is 100. If it's shorter/longer, adjust ranges in the formula as needed.
Note 2: The formula might be slow for 300k rows of data.

 

[ibmy]

@DanteAmor , sorry for not explain the part of when the expected result is "1". My bad for only explain the part of when no need to sign "1" in Column P , not clearly enough.

No assign "1" in Column P : Cell O14 and O21 show total number 0 ( 3 , 1 ) but N15 and N22 show no =1
Assign "1" in Column P : Cell O7 ,O9 , O18 show total number 0 ( 4 , 1 , 2) ) and N8, N10 , N19 show = 1


@Tetra201 , Thanks for the formula. It runs not too fast or too slow but it is fine for 400k row data. The lower longest streak of zeros in column M I set in formula , the faster it runs.

I have 2 questions :

1 ) May I know what use of Column M in formula? I thought only need column N and column O in formula.

2) What if I insert 1 cell in Column P so the data look like this :

cell 431.xlsb
	M	N	O	P
1	K	Yes/No	Total 0	Expected Result
2
3	-0.3			1
4	0
5	0
6	0
7	0
8	0.1	1	4	1
9	0
10	1.3	1	1
11	-0.4
12	0
13	0
14	0
15	-1.8		3
16	-0.1			1
17	0
18	0
19	-0.1	1	2
20	0.9
21	0
22	0.3		1
23	-0.2
Sheet1

Can the formula be shorter and simpler? or maybe faster a bit...

 

[DanteAmor]

2) What if I insert 1 cell in Column P so the data look like this :
or maybe faster a bit...

Considering your comment from the previous post, the 1 and > 0 are in the same row.
If you are interested, with the following macro the result is immediate.

But I have a question, if there was a scenario where the first value is 0, for example:

varios 22may2023.xlsm
	A	B	C	D
1	K	Yes/No	Total 0	Expected Result
2				1
3	0
4	0.1	1	1	1
5	0
6	0
7	0
8	0.1	1	3	1
9	0
10	1.3	1	1
Hoja1

So the result should start in cell D2.
Please try the following macro:

Sub CountZero_v1()
  Dim a As Variant
  Dim i As Long, j As Long
  a = Range("A3:C" & Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(0 To UBound(a), 1 To 1)
  For i = UBound(a) To 1 Step -1
    If a(i, 2) = 1 And a(i, 3) > 0 Then
      b(i - a(i, 3) - 1, 1) = 1
    End If
  Next
  Range("D2").Resize(UBound(b)).Value = b
End Sub

--------------------------​

But if the above scenario does not exist, then also the following macro works.

Sub CountZero_v2()
  Dim a As Variant
  Dim i As Long, j As Long
  a = Range("A3:C" & Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = UBound(a) To 1 Step -1
    If a(i, 2) = 1 And a(i, 3) > 0 Then
      b(i - a(i, 3) - 1, 1) = 1
    End If
  Next
  Range("d3").Resize(UBound(b)).Value = b
End Sub

--------------------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Tetra201]

1 ) May I know what use of Column M in formula? I thought only need column N and column O in formula.

Here is how the formula works. It checks three conditions:
i) Is value in cell (formula row, column M) different from zero?
ii) Is value in cell (first row after the formula row with a non-zero value in column M, column N) equal to 1?
iii) Is value in cell (row immediately before the first row after the formula row with a non-zero value in column M, column O) greater than zero?
If each of the three conditions evaluates to TRUE, the formula returns 1, otherwise it returns an empty string or an error, which is trapped by the IFERROR wrapper.

2) What if I insert 1 cell in Column P... Can the formula be shorter and simpler? or maybe faster a bit...

In this case, the formula becomes just two-symbol shorter:

=IFERROR(IF(AND(M3<>0,INDEX(N4:N103,MATCH(TRUE,M4:M103<>0,0))=1,INDEX(O4:O103,MATCH(TRUE,M4:M103<>0,0))>0),1,""),"")

 

[ibmy]

@Tetra201 ,Thanks for explaination and shorter formula. The formula working great I am really appreaciate it

@DanteAmor ,Thanks for the macro. It working great.

Also, thanks you for details of how thinking another situation where there is might result in row 2.
So far, the data show no result in row 2 yet and only interested the result in the middle of data ( above row 2 ).
Then, I will be using "Sub CountZero_v2()" macro for now.

I have a question and need confirmation, is this the right way to change code if I want to use Column M, N, O, P ?

Sub CountZero_v2()
 
  Dim a As Variant
  Dim i As Long, j As Long
 
  a = Range("M3:O" & Range("M" & Rows.Count).End(3).Row).Value
 
  ReDim b(1 To UBound(a), 1 To 1)
  For i = UBound(a) To 1 Step -1
    If a(i, 2) = 1 And a(i, 3) > 0 Then
      b(i - a(i, 3) - 1, 1) = 1
    End If
  Next
  Range("P3").Resize(UBound(b)).Value = b
 
End Sub

 

[DanteAmor]

I have a question and need confirmation, is this the right way to change code if I want to use Column M, N, O, P ?

If that way is correct to start in column M