Count if the value is positive and negative 'itself'

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
100
Office Version
  1. 2016
Platform
  1. Windows
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
 
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
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.099390.30.20.30.20.30.2
51.099241.09937-0.2-0.2-0.2-0.2-0.2-0.2
61.099261.099390.20.20.20.20.20.2
71.099241.09937-0.2-0.2-0.2-0.2-0.2-0.2
81.099251.09940.10.30.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.11
4-0.1-0.12
50.10.13
6-0.1-0.14
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Thanks for the extra samples. Try these

Request 1
ibmy 2020-06-08 1.xlsm
ABCDEF
1data bdata cresult
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
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.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 
Sheet5
Cell Formulas
RangeFormula
F3:F43F3=IF(AND(B3=C3,ABS(B3)=ABS(G$1)),1+N(F2)*(B3=-B2),"")
 
Upvote 0
Solution

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top