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]

Your earlier post said that columns B & C needed to be the same. When I copy your first sheet from post #10 and increase the number of decimal places displayed, you will see that what appears initially to be equal in columns B & C in fact are not. That is why my formula is not producing any results.

Can you shed any light on that?

eu 5.1.xlsb
	B	C
1	data b	data c
2
3	0.29999999999975	0.20000000000131
4	-0.19999999999909	-0.20000000000131
5	0.19999999999909	0.20000000000131
6	-0.19999999999909	-0.20000000000131
7	0.10000000000066	0.29999999999975
Sheet3

 

[ibmy]

Ok, now no more "No count" error, only #VALUE! error still happen when there is some 'data pattern' I not show in post #5 .

Firstly I would like to apologize for example in post #5 because the data is made up by myself, not real data but the idea is there.
When apply formula on real data, I noticed there some 'data pattern' cause the formula not work. ( will show 'data pattern' not working below )

Spoiler: Cause of many decimal point value and how I fix it

eu 5.1.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		1.09923	1.09937
4		1.09926	1.09939		0.3	0.2		0.3	0.2		0.3	0.2
5		1.09924	1.09937		-0.2	-0.2		-0.2	-0.2		-0.2	-0.2
6		1.09926	1.09939		0.2	0.2		0.2	0.2		0.2	0.2
7		1.09924	1.09937		-0.2	-0.2		-0.2	-0.2		-0.2	-0.2
8		1.09925	1.0994		0.1	0.3		0.1	0.3		0.1	0.3
9
Sheet44

Cell Formulas
Range	Formula
E4:F8	E4	=(B4-B3)*10000

One decimal point value (Column E & F) is current row substract previous row (Column B & C). Then I 'copy-paste special value' to Column H & I. This is where problem of many decimal point comes in like you said in post #11 . I face this problem a month ago and some of formula back then do not work and I do not know why this happen and about to ask in mrexceI. Then I figured it out myself and fix it by copy all of value in Column H & I into Notepad. From Notepad I copy and paste back on excel (Column K & L) , and the result is I got is "Real" 1 decimal point value.

I still want to know why after I copy-paste special value, I got so many decimal point value but I think will do that later on another thread. Fix this problem in excel app will save me a time

After apply formula on "Real" 1 decimal point value, 'No Count' error is no more.

But there is some data pattern that cause #VALUE! error, example shown below:

Spoiler: Data pattern not working

1. Example of 'data pattern', formula is working :

eu 5.1.xlsb
	A	B	C	D	E	F
1		Data B	Data C			Result 1
2
3		0.1	0.1			1
4		-0.1	-0.1			2
5		0.1	0.1			3
6		-0.1	-0.1			4
7
Sheet49

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

2. Data pattern cause #VALUE! error is when C3 is not 0.1. Can be any number 0, 0.1, -0.1 ,0.2 , -0.2 ,etc....

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.1	-0.1			#VALUE!
7
Sheet49

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

p/s: I think it is also my mistake for not enough description in post #5 . What I actually want in post #5 is same as post #1 , same rule but with 2 column.

 

[Peter_SSs]

What I actually want in post #5 is same as post #1 , same rule but with 2 column.

In that case I am not sure that I know what you actually want. Can you give a new set of sample data and the expected results and clearly explain what you want in relation to that sample data.

Also, since you want us to work with your sample data, please don't hide it behind Spoiler tags - there is no point to that other than making helpers click more to see it.

 

[ibmy]

Sorry for late reply, been busy with data research stuff.

I have 2 request.

Request 1 :

Rule:
1. Value for both column must be same
2. Starting value for both column can be negative or postive
3. In order to continue the count, current row must opposite negative/postive from previous value

4 . Reset the count when:
i - Value for both column are not same
ii - Repeat of postive/negative value from previous row

eu 5.1.xlsb
	A	B	C	D	E	F
1		data b	data c			expected result
2
3		-0.1	-0.1			1
4		-0.1	-0.1			1
5		0.1	0.1			2
6		0.1	0.1			1
7		-0.1	-0.1			2
8		0.2	0.1
9		-0.2	-0.2			1
10		0	-0.1
11		0.2	0
12		-0.2	-0.2			1
13		0.2	0.2			2
14		-0.2	-0.2			3
15		-0.1	0
16		0.3	0.2
17		-0.3	-0.3			1
18		0.2	0.1
19		0.3	0.2
20		-0.3	-0.3			1
21		-0.2	0
22		-0.2	0
23		0.2	0.2			1
24		-0.2	-0.1
25		0.2	0
26		-0.2	-0.2			1
27		0.2	0.2			2
28		-0.2	-0.2			3
29		-0.1	0
30		-0.2	0
31		0.2	0.2			1
32		0.2	0.1
33		-0.1	-0.2
34		0.1	0.1			1
35		-0.1	-0.1			2
36		0.1	0.1			3
37		-0.1	-0.1			4
38		-0.1	-0.1			1
39		0.1	0
40		0.1	0.1			1
41		-0.1	-0.1			2
42		0.1	0.1			3
43		0.1	0.1			1
broad

Request 2 :

A formula that can edit for 'specific value' to count only 0.1 value, 0.2, 0.3,etc...

0.2 :-

eu 5.1.xlsb
	A	B	C	D	E	F
1		data b	data c			expected result
2
3		-0.1	-0.1
4		-0.1	-0.1
5		0.1	0.1
6		0.1	0.1
7		-0.1	-0.1
8		0.2	0.1
9		-0.2	-0.2			1
10		0	-0.1
11		0.2	0
12		-0.2	-0.2			1
13		0.2	0.2			2
14		-0.2	-0.2			3
15		-0.1	0
16		0.3	0.2
17		-0.3	-0.3
18		0.2	0.1
19		0.3	0.2
20		-0.3	-0.3
21		-0.2	0
22		-0.2	0
23		0.2	0.2			1
24		-0.2	-0.1
25		0.2	0
26		-0.2	-0.2			1
27		0.2	0.2			2
28		-0.2	-0.2			3
29		-0.1	0
30		-0.2	0
31		0.2	0.2			1
32		0.2	0.1
33		-0.1	-0.2
34		0.1	0.1
35		-0.1	-0.1
36		0.1	0.1
37		-0.1	-0.1
38		-0.1	-0.1
39		0.1	0
40		0.1	0.1
41		-0.1	-0.1
42		0.1	0.1
43		0.1	0.1
narrow 0.2

0.1 :-

eu 5.1.xlsb
	A	B	C	D	E	F
1		data b	data c			expected result
2
3		-0.1	-0.1			1
4		-0.1	-0.1			1
5		0.1	0.1			2
6		0.1	0.1			1
7		-0.1	-0.1			2
8		0.2	0.1
9		-0.2	-0.2
10		0	-0.1
11		0.2	0
12		-0.2	-0.2
13		0.2	0.2
14		-0.2	-0.2
15		-0.1	0
16		0.3	0.2
17		-0.3	-0.3
18		0.2	0.1
19		0.3	0.2
20		-0.3	-0.3
21		-0.2	0
22		-0.2	0
23		0.2	0.2
24		-0.2	-0.1
25		0.2	0
26		-0.2	-0.2
27		0.2	0.2
28		-0.2	-0.2
29		-0.1	0
30		-0.2	0
31		0.2	0.2
32		0.2	0.1
33		-0.1	-0.2
34		0.1	0.1			1
35		-0.1	-0.1			2
36		0.1	0.1			3
37		-0.1	-0.1			4
38		-0.1	-0.1			1
39		0.1	0
40		0.1	0.1			1
41		-0.1	-0.1			2
42		0.1	0.1			3
43		0.1	0.1			1
narrow 0.1

 

[Peter_SSs]

Thanks for the extra samples. Try these

Request 1

ibmy 2020-06-08 1.xlsm
	A	B	C	D	E	F
1		data b	data c			result
2
3		-0.1	-0.1			1
4		-0.1	-0.1			1
5		0.1	0.1			2
6		0.1	0.1			1
7		-0.1	-0.1			2
8		0.2	0.1
9		-0.2	-0.2			1
10		0	-0.1
11		0.2	0
12		-0.2	-0.2			1
13		0.2	0.2			2
14		-0.2	-0.2			3
15		-0.1	0
16		0.3	0.2
17		-0.3	-0.3			1
18		0.2	0.1
19		0.3	0.2
20		-0.3	-0.3			1
21		-0.2	0
22		-0.2	0
23		0.2	0.2			1
24		-0.2	-0.1
25		0.2	0
26		-0.2	-0.2			1
27		0.2	0.2			2
28		-0.2	-0.2			3
29		-0.1	0
30		-0.2	0
31		0.2	0.2			1
32		0.2	0.1
33		-0.1	-0.2
34		0.1	0.1			1
35		-0.1	-0.1			2
36		0.1	0.1			3
37		-0.1	-0.1			4
38		-0.1	-0.1			1
39		0.1	0
40		0.1	0.1			1
41		-0.1	-0.1			2
42		0.1	0.1			3
43		0.1	0.1			1
Sheet4

Cell Formulas
Range	Formula
F3:F43	F3	=IF(B3=C3,1+(N(F2)*(B3=-B2)),"")

Request 2 (change cell G1 for different values)

ibmy 2020-06-08 1.xlsm
	A	B	C	D	E	F	G
1		data b	data c			result	0.2
2
3		-0.1	-0.1
4		-0.1	-0.1
5		0.1	0.1
6		0.1	0.1
7		-0.1	-0.1
8		0.2	0.1
9		-0.2	-0.2			1
10		0	-0.1
11		0.2	0
12		-0.2	-0.2			1
13		0.2	0.2			2
14		-0.2	-0.2			3
15		-0.1	0
16		0.3	0.2
17		-0.3	-0.3
18		0.2	0.1
19		0.3	0.2
20		-0.3	-0.3
21		-0.2	0
22		-0.2	0
23		0.2	0.2			1
24		-0.2	-0.1
25		0.2	0
26		-0.2	-0.2			1
27		0.2	0.2			2
28		-0.2	-0.2			3
29		-0.1	0
30		-0.2	0
31		0.2	0.2			1
32		0.2	0.1
33		-0.1	-0.2
34		0.1	0.1
35		-0.1	-0.1
36		0.1	0.1
37		-0.1	-0.1
38		-0.1	-0.1
39		0.1	0
40		0.1	0.1
41		-0.1	-0.1
42		0.1	0.1
43		0.1	0.1
Sheet5

Cell Formulas
Range	Formula
F3:F43	F3	=IF(AND(B3=C3,ABS(B3)=ABS(G$1)),1+N(F2)*(B3=-B2),"")