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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
AB
1DataSequence
2-0.11
30.12
4-0.13
50.3 
60.7 
70.21
8-0.22
9-0.21
100.22
110.7 
12-0.51
130.52
14-0.53
150.54
160.5 
170.3 
Count sequence
Cell Formulas
RangeFormula
B2:B17B2=IF(A2=-N(A1),B1+1,IF(A2=-A3,1,""))
 
Upvote 0
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
ABCDEF
1DataResult
2
3-0.1
4-0.11
50.1
6-0.13
7-0.1
80.6
90.21
10-0.22
11-0.21
120.22
130.7
14-0.51
150.5
16-0.5
170.54
180.5
190.3
Sheet1
 
Upvote 0
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
BC
1Data
2
3-0.1 
4-0.11
50.1 
6-0.13
7-0.1 
80.6 
90.21
10-0.22
11-0.21
120.22
130.7 
14-0.51
150.5 
16-0.5 
170.54
180.5 
190.3 
20
First Last
Cell Formulas
RangeFormula
C3:C19C3=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,""))
 
Upvote 0
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
ABCDEFG
1Data BData CResult 1Result 2
2
3-0.1-0.1
4-0.1-0.111
50.10.12
6-0.1-0.133
7-0.1-0.1
80.60.5
9-0.4-0.2
100.20.211
11-0.2-0.222
120.20.8
130.70.1
14-0.5-0.511
150.50.52
16-0.5-0.53
170.50.544
180.50.5
190.30.3
Sheet1

- 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.
 
Upvote 0
26Aug19.xlsx
ABCD
1
2-0.1111
30.122 
4-0.1333
50.3   
60.7   
70.2111
8-0.2222
9-0.2111
100.2222
110.7   
12-0.5111
130.522 
14-0.533 
150.5444
160.3 1 
170.3 1 
180.3111
19-0.322 
200.3333
210.4   
Sheet38
Cell Formulas
RangeFormula
B2:B21B2=IF(A2=-A1,B1+1,IF(A2=-A3,1,""))
C2:C21C2=IF(A2=-A1,B1+1,IF(ABS(A2)=ABS(A3),1,""))
D2D2=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:D21D3=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.
 
Upvote 0
@*Amit Tandon ,the formula is for single data (1 column) right?

Not for double data (2 Column)
 
Upvote 0
- 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
BCDEFG
1Data BData CResult 1Result 2
2
3-0.1-0.1  
4-0.1-0.111
50.10.12 
6-0.1-0.133
7-0.1-0.1  
80.60.5  
9-0.4-0.2  
100.20.211
11-0.2-0.222
120.20.8  
130.70.1  
14-0.5-0.511
150.50.52 
16-0.5-0.53 
170.50.544
180.50.5  
190.30.3  
Sheet1
Cell Formulas
RangeFormula
F3:F19F3=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")
G3:G19G3=IF(F3="","",IF(OR(F3=1,F4<>F3+1),F3,""))
 
Upvote 0
@*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
 
Upvote 0
@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 :-

eu 5.1.xlsb
ABCDEF
1data bdata cresult 1
2
30.30.2 
4-0.2-0.2 
50.20.2 
6-0.2-0.2 
70.10.3 
8
Sheet11
Cell Formulas
RangeFormula
F3:F7F3=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")


eu 4.29.xlsb
ABCDEF
1data b data cresult 1
2
30.10 
4-0.1-0.1 
50.10.1 
6-0.1-0.1 
7-0.1-0.1 
8
Sheet2
Cell Formulas
RangeFormula
F3:F7F3=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")

eu 5.1.xlsb
ABCDEF
1data bdata cresult 1
2
3-0.10 
40.10.1#VALUE!
5-0.1-0.1#VALUE!
60-0.2 
7
Sheet11
Cell Formulas
RangeFormula
F3:F6F3=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")


eu 5.1.xlsb
ABCDEF
1data bdata cresult 1
2
3-0.1-0.2 
40.10.1#VALUE!
5-0.1-0.1#VALUE!
60.10.1#VALUE!
7-0.1-0.1#VALUE!
8-0.1-0.1 
9
Sheet11
Cell Formulas
RangeFormula
F3:F8F3=IF(B3=C3,IF(B3=-B2,F2+1,IF(B3=-B4,1,"")),"")
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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