Count if the value is positive and negative 'itself'

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,622
Office Version
365
Platform
Windows
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
BC
1data bdata c
2
30.299999999999750.20000000000131
4-0.19999999999909-0.20000000000131
50.199999999999090.20000000000131
6-0.19999999999909-0.20000000000131
70.100000000000660.29999999999975
Sheet3
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

ibmy

New Member
Joined
Mar 4, 2020
Messages
26
Office Version
2016
Platform
Windows
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 )

eu 5.1.xlsb
ABCDEFGHIJKL
1
2
31.099231.09937
41.099261.09939
0.3
0.2
0.30.20.30.2
51.099241.09937
-0.2
-0.2
-0.2-0.2-0.2-0.2
61.099261.09939
0.2
0.2
0.20.20.20.2
71.099241.09937
-0.2
-0.2
-0.2-0.2-0.2-0.2
81.099251.0994
0.1
0.3
0.10.30.10.3
9
Sheet44
Cell Formulas
RangeFormula
E4:F8E4=(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:

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

eu 5.1.xlsb
ABCDEF
1Data BData CResult 1
2
30.10.1
1
4-0.1-0.1
2
50.10.1
3
6-0.1-0.1
4
7
Sheet49
Cell Formulas
RangeFormula
F3:F6F3=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
ABCDEF
1Data BData CResult 1
2
30.10
 
4-0.1-0.1
#VALUE!
50.10.1
#VALUE!
6-0.1-0.1
#VALUE!
7
Sheet49
Cell Formulas
RangeFormula
F3:F6F3=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,622
Office Version
365
Platform
Windows
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

New Member
Joined
Mar 4, 2020
Messages
26
Office Version
2016
Platform
Windows
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
ABCDEF
1data bdata cexpected result
2
3-0.1-0.11
4-0.1-0.11
50.10.12
60.10.11
7-0.1-0.12
80.20.1
9-0.2-0.21
100-0.1
110.20
12-0.2-0.21
130.20.22
14-0.2-0.23
15-0.10
160.30.2
17-0.3-0.31
180.20.1
190.30.2
20-0.3-0.31
21-0.20
22-0.20
230.20.21
24-0.2-0.1
250.20
26-0.2-0.21
270.20.22
28-0.2-0.23
29-0.10
30-0.20
310.20.21
320.20.1
33-0.1-0.2
340.10.11
35-0.1-0.12
360.10.13
37-0.1-0.14
38-0.1-0.11
390.10
400.10.11
41-0.1-0.12
420.10.13
430.10.11
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
ABCDEF
1data bdata cexpected result
2
3-0.1-0.1
4-0.1-0.1
50.10.1
60.10.1
7-0.1-0.1
80.20.1
9-0.2-0.21
100-0.1
110.20
12-0.2-0.21
130.20.22
14-0.2-0.23
15-0.10
160.30.2
17-0.3-0.3
180.20.1
190.30.2
20-0.3-0.3
21-0.20
22-0.20
230.20.21
24-0.2-0.1
250.20
26-0.2-0.21
270.20.22
28-0.2-0.23
29-0.10
30-0.20
310.20.21
320.20.1
33-0.1-0.2
340.10.1
35-0.1-0.1
360.10.1
37-0.1-0.1
38-0.1-0.1
390.10
400.10.1
41-0.1-0.1
420.10.1
430.10.1
narrow 0.2


0.1 :-

eu 5.1.xlsb
ABCDEF
1data bdata cexpected result
2
3-0.1-0.11
4-0.1-0.11
50.10.12
60.10.11
7-0.1-0.12
80.20.1
9-0.2-0.2
100-0.1
110.20
12-0.2-0.2
130.20.2
14-0.2-0.2
15-0.10
160.30.2
17-0.3-0.3
180.20.1
190.30.2
20-0.3-0.3
21-0.20
22-0.20
230.20.2
24-0.2-0.1
250.20
26-0.2-0.2
270.20.2
28-0.2-0.2
29-0.10
30-0.20
310.20.2
320.20.1
33-0.1-0.2
340.10.11
35-0.1-0.12
360.10.13
37-0.1-0.14
38-0.1-0.11
390.10
400.10.11
41-0.1-0.12
420.10.13
430.10.11
narrow 0.1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,622
Office Version
365
Platform
Windows
Thanks for the extra samples. Try these

Request 1
ibmy 2020-06-08 1.xlsm
ABCDEF
1data bdata cresult
2
3-0.1-0.1
1
4-0.1-0.1
1
50.10.1
2
60.10.1
1
7-0.1-0.1
2
80.20.1
 
9-0.2-0.2
1
100-0.1
 
110.20
 
12-0.2-0.2
1
130.20.2
2
14-0.2-0.2
3
15-0.10
 
160.30.2
 
17-0.3-0.3
1
180.20.1
 
190.30.2
 
20-0.3-0.3
1
21-0.20
 
22-0.20
 
230.20.2
1
24-0.2-0.1
 
250.20
 
26-0.2-0.2
1
270.20.2
2
28-0.2-0.2
3
29-0.10
 
30-0.20
 
310.20.2
1
320.20.1
 
33-0.1-0.2
 
340.10.1
1
35-0.1-0.1
2
360.10.1
3
37-0.1-0.1
4
38-0.1-0.1
1
390.10
 
400.10.1
1
41-0.1-0.1
2
420.10.1
3
430.10.1
1
Sheet4
Cell Formulas
RangeFormula
F3:F43F3=IF(B3=C3,1+(N(F2)*(B3=-B2)),"")



Request 2 (change cell G1 for different values)
ibmy 2020-06-08 1.xlsm
ABCDEFG
1data bdata cresult0.2
2
3-0.1-0.1
 
4-0.1-0.1
 
50.10.1
 
60.10.1
 
7-0.1-0.1
 
80.20.1
 
9-0.2-0.2
1
100-0.1
 
110.20
 
12-0.2-0.2
1
130.20.2
2
14-0.2-0.2
3
15-0.10
 
160.30.2
 
17-0.3-0.3
 
180.20.1
 
190.30.2
 
20-0.3-0.3
 
21-0.20
 
22-0.20
 
230.20.2
1
24-0.2-0.1
 
250.20
 
26-0.2-0.2
1
270.20.2
2
28-0.2-0.2
3
29-0.10
 
30-0.20
 
310.20.2
1
320.20.1
 
33-0.1-0.2
 
340.10.1
 
35-0.1-0.1
 
360.10.1
 
37-0.1-0.1
 
38-0.1-0.1
 
390.10
 
400.10.1
 
41-0.1-0.1
 
420.10.1
 
430.10.1
 
Sheet5
Cell Formulas
RangeFormula
F3:F43F3=IF(AND(B3=C3,ABS(B3)=ABS(G$1)),1+N(F2)*(B3=-B2),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,099,004
Messages
5,465,955
Members
406,455
Latest member
mkadam

This Week's Hot Topics

Top