Count if the value is positive and negative 'itself'

[ibmy]

Hi,

Column A // Column B

-0.1 // 1
0.1 // 2
-0.1 // 3
0.3
0.7
0.2 // 1
-0.2 // 2
-0.2 // 1
0.2 // 2
0.7 //
-0.5 // 1
0.5 // 2
-0.5 // 3
0.5 // 4
0.5 //
0.3 //


Rule:
1. The starting value can be negative or postive
2. Reset the count if :-
i- value is not positive or negative 'itself' ( 0.1 -> -0.1 -> 0.3)
ii- repeat of postive or negative itself ( 0.1 -> -0.1 -> -0.1)

p/s: sorry for bad English

 

[Peter_SSs]

See if this is what you want.

BTW, I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

20 05 29.xlsm
	A	B
1	Data	Sequence
2	-0.1	1
3	0.1	2
4	-0.1	3
5	0.3
6	0.7
7	0.2	1
8	-0.2	2
9	-0.2	1
10	0.2	2
11	0.7
12	-0.5	1
13	0.5	2
14	-0.5	3
15	0.5	4
16	0.5
17	0.3
Count sequence

Cell Formulas
Range	Formula
B2:B17	B2	=IF(A2=-N(A1),B1+1,IF(A2=-A3,1,""))

 

[ibmy]

Thanks @Peter_SSs

Sorry for trouble you and other helpers such not use XL2BB before, now I have learnt how to use XL2BB.

Can you help me on formula to show "Starting number 1 and Max value only" in result column, same objective in post 1. Hopefully I can save a lot of time when apply filter on result column.

Book1
	A	B	C	D	E	F
1		Data				Result
2
3		-0.1
4		-0.1				1
5		0.1
6		-0.1				3
7		-0.1
8		0.6
9		0.2				1
10		-0.2				2
11		-0.2				1
12		0.2				2
13		0.7
14		-0.5				1
15		0.5
16		-0.5
17		0.5				4
18		0.5
19		0.3
Sheet1

 

[Peter_SSs]

now I have learnt how to use XL2BB.

Great news! That makes it much easier for helpers.

Can you help me on formula ...

Like this?

20 06 07.xlsm
	B	C
1	Data
2
3	-0.1
4	-0.1	1
5	0.1
6	-0.1	3
7	-0.1
8	0.6
9	0.2	1
10	-0.2	2
11	-0.2	1
12	0.2	2
13	0.7
14	-0.5	1
15	0.5
16	-0.5
17	0.5	4
18	0.5
19	0.3
20
First Last

Cell Formulas
Range	Formula
C3:C19	C3	=IF(AND(B3=-B4,B2<>-B3),1,IF(AND(B3=-B2,B4<>-B3),ROW()-LOOKUP(9^9,C$1:C2,ROW(C$1:C2))+1,""))

 

[ibmy]

Thanks @Peter_SSs , it works great. ?????

There is new data (Column C) that interest me in my research. The value must same as value in Column B.
I would like 2 different results like previous post ( Usual counting & Show starting (1) and max only).
Is it possible to formula ?

Book1
	A	B	C	D	E	F	G
1		Data B	Data C			Result 1	Result 2
2
3		-0.1	-0.1
4		-0.1	-0.1			1	1
5		0.1	0.1			2
6		-0.1	-0.1			3	3
7		-0.1	-0.1
8		0.6	0.5
9		-0.4	-0.2
10		0.2	0.2			1	1
11		-0.2	-0.2			2	2
12		0.2	0.8
13		0.7	0.1
14		-0.5	-0.5			1	1
15		0.5	0.5			2
16		-0.5	-0.5			3
17		0.5	0.5			4	4
18		0.5	0.5
19		0.3	0.3
Sheet1

Spoiler: What I have tried :-

- Apply previous formula provided by you.
- Then apply filter for both Column B and Column C to show value of postive & its negative pair ( 0.1 & -0.1 , 0.2 & -0.2 , 0.3 & -0.3 ,etc ....).
- Also apply filter on the Result Column.
- Let say I want to research 'data pattern' on 0.1 & -0.1 .Then I look for available value on the Result Column and check for which one is significant.
- The problem rise when the count is start on Column B (formula result) but the value in Column C is not necessary same as value in Column B.
- It is time consuming. Hopefully there is a formula that can save me a lot of time by filter out value in Column C is same as Column B.

p/s: I have to deal with data range 60k-90k rows everyday.

: Thanks for previous formula. I use it for single data research ( Column B and Column C) , But now, I would like to research on the connection between two of those data.

 

[Amit Tandon]

26Aug19.xlsx
	A	B	C	D
1
2	-0.1	1	1	1
3	0.1	2	2
4	-0.1	3	3	3
5	0.3
6	0.7
7	0.2	1	1	1
8	-0.2	2	2	2
9	-0.2	1	1	1
10	0.2	2	2	2
11	0.7
12	-0.5	1	1	1
13	0.5	2	2
14	-0.5	3	3
15	0.5	4	4	4
16	0.3		1
17	0.3		1
18	0.3	1	1	1
19	-0.3	2	2
20	0.3	3	3	3
21	0.4
Sheet38

Cell Formulas
Range	Formula
B2:B21	B2	=IF(A2=-A1,B1+1,IF(A2=-A3,1,""))
C2:C21	C2	=IF(A2=-A1,B1+1,IF(ABS(A2)=ABS(A3),1,""))
D2	D2	=IF((A2=-A3)*(A2<>-A1),1,IF((A2<>-A3)*(A2=-A1),ROW()-LOOKUP(2,1/(D1:D$2=1),ROW(D1:D$2))+1,""))
D3:D21	D3	=IF((A3=-A4)*(A3<>-A2),1,IF((A3<>-A4)*(A3=-A2),ROW()-LOOKUP(2,1/(D$2:D2=1),ROW(D$2:D2))+1,""))

Column D formula is for your latest query. Column B and C illustrate 2 separate options for your initial query - refer results from row 16 onwards.

 

[ibmy]

@*Amit Tandon ,the formula is for single data (1 column) right?

Not for double data (2 Column)

 

[Peter_SSs]

- Apply previous formula provided by you.
- Then apply filter for both Column B and Column C to show value of postive & its negative pair ( 0.1 & -0.1 , 0.2 & -0.2 , 0.3 & -0.3 ,etc ....).
- Also apply filter on the Result Column.
- Let say I want to research 'data pattern' on 0.1 & -0.1 .Then I look for available value on the Result Column and check for which one is significant.
- The problem rise when the count is start on Column B (formula result) but the value in Column C is not necessary same as value in Column B.
- It is time consuming. Hopefully there is a formula that can save me a lot of time by filter out value in Column C is same as Column B.

I didn't really understand all that but the formulas below reproduce the sample results that you have given.

ibmy 2020-06-08 1.xlsm
	B	C	D	E	F	G
1	Data B	Data C			Result 1	Result 2
2
3	-0.1	-0.1
4	-0.1	-0.1			1	1
5	0.1	0.1			2
6	-0.1	-0.1			3	3
7	-0.1	-0.1
8	0.6	0.5
9	-0.4	-0.2
10	0.2	0.2			1	1
11	-0.2	-0.2			2	2
12	0.2	0.8
13	0.7	0.1
14	-0.5	-0.5			1	1
15	0.5	0.5			2
16	-0.5	-0.5			3
17	0.5	0.5			4	4
18	0.5	0.5
19	0.3	0.3
Sheet1

Cell Formulas
Range	Formula
F3:F19	F3	=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")
G3:G19	G3	=IF(F3="","",IF(OR(F3=1,F4<>F3+1),F3,""))

 

[Amit Tandon]

@*Amit Tandon ,the formula is for single data (1 column) right?

Not for double data (2 Column)

1) Columns B and C illustrate 2 separate options - if you need to have 1 in rows 16 and 17 then use column C formula, else column B formula.

2) Column D formula uses only column A values to arrive at your expected result ie. single column is considered. This does not consider column B values. You need to enter the formula in cell D2 and copy down. In case that is your requirement

 

[ibmy]

@Peter_SSs , some row is working, some is not.

It is my mistake (sorry ?‍), the example I posted in #5 is not full cover of 'data pattern'.


Some of not working :-

Spoiler: No Count

eu 5.1.xlsb
	A	B	C	D	E	F
1		data b	data c			result 1
2
3		0.3	0.2
4		-0.2	-0.2
5		0.2	0.2
6		-0.2	-0.2
7		0.1	0.3
8
Sheet11

Cell Formulas
Range	Formula
F3:F7	F3	=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")

eu 4.29.xlsb
	A	B	C	D	E	F
1		data b	data c			result 1
2
3		0.1	0
4		-0.1	-0.1
5		0.1	0.1
6		-0.1	-0.1
7		-0.1	-0.1
8
Sheet2

Cell Formulas
Range	Formula
F3:F7	F3	=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")

Spoiler: Error #VALUE!

eu 5.1.xlsb
	A	B	C	D	E	F
1		data b	data c			result 1
2
3		-0.1	0
4		0.1	0.1			#VALUE!
5		-0.1	-0.1			#VALUE!
6		0	-0.2
7
Sheet11

Cell Formulas
Range	Formula
F3:F6	F3	=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")

eu 5.1.xlsb
	A	B	C	D	E	F
1		data b	data c			result 1
2
3		-0.1	-0.2
4		0.1	0.1			#VALUE!
5		-0.1	-0.1			#VALUE!
6		0.1	0.1			#VALUE!
7		-0.1	-0.1			#VALUE!
8		-0.1	-0.1
9
Sheet11

Cell Formulas
Range	Formula
F3:F8	F3	=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")