ibmy
Board Regular
- Joined
- Mar 4, 2020
- Messages
- 103
- Office Version
- 2016
- Platform
- Windows
Hi, need expert help me on this study data.
Need 5 different result.
(1) The count start when Column B and Column C are positve value ( > 0 ).
The Result in Column F only show starting number (number 1) and max value.
(2) Same from point (1) above, the only difference is Column B and C are negative value ( < 0 )
(3) The count start when Column B and Column C are positive value( > 0 ) AND next row of value of Column D is same value from previous row in Column D
(4) Same from point (3) above, the only difference is Column B and C are negative value ( < 0 )
(5) This one, I would like the count start when value of Column D at 'specific number' and Column B and Column C are postive value or negative value. (same like point 1 and 2 above).
i) Value at Column D is 1.3 with Column B and Column C are positive value
ii) Value at Column D is 1.1 with Column B and Column C are negative value
iii) 1.7, 1.9, 2.0, etc ...
> Let me know what do I need to change in formula for specific value in Column D with Column B and Column C are postive or negative value
Side Note:
1. The lowest positive value in Column B and Column C is 0.1
2. The lowest negative value in Column B and Column C is -0.1
3. The value in Column D always positve.
p/s : My data about 60k-250k row
: sorry for bad English
Thanks, appreaciate all your help.
Need 5 different result.
(1) The count start when Column B and Column C are positve value ( > 0 ).
The Result in Column F only show starting number (number 1) and max value.
eu 5.6.xlsb | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | B | C | Result | |||||
2 | ||||||||
3 | 0.1 | 0.1 | 1 | |||||
4 | 0 | 0.2 | ||||||
5 | 0 | 0 | ||||||
6 | 0.1 | 0.3 | 1 | |||||
7 | 0.1 | 0.5 | ||||||
8 | 0.2 | 0.5 | 3 | |||||
9 | 0.3 | 0 | ||||||
10 | 0.4 | 0.4 | 1 | |||||
11 | 0.1 | -0.3 | ||||||
12 | 0.1 | 0.3 | 1 | |||||
13 | 0.1 | 0.6 | 2 | |||||
14 | -0.4 | -0.3 | ||||||
15 | -0.2 | 0.5 | ||||||
16 | 0.1 | 0.1 | 1 | |||||
17 | 0.1 | 0.3 | ||||||
18 | 0.1 | 0.1 | 3 | |||||
19 | -0.4 | 0 | ||||||
Sheet5 |
(2) Same from point (1) above, the only difference is Column B and C are negative value ( < 0 )
eu 5.6.xlsb | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | B | C | Result | |||||
2 | ||||||||
3 | -0.1 | -0.1 | 1 | |||||
4 | 0 | -0.2 | ||||||
5 | 0 | 0 | ||||||
6 | -0.1 | -0.3 | 1 | |||||
7 | -0.1 | -0.5 | ||||||
8 | -0.2 | -0.5 | 3 | |||||
9 | -0.3 | 0 | ||||||
10 | -0.4 | -0.4 | 1 | |||||
11 | -0.1 | 0.3 | ||||||
12 | -0.1 | -0.3 | 1 | |||||
13 | -0.1 | -0.6 | 2 | |||||
14 | 0.4 | 0.3 | ||||||
15 | 0.2 | -0.5 | ||||||
16 | -0.1 | -0.1 | 1 | |||||
17 | -0.1 | -0.3 | ||||||
18 | -0.1 | -0.1 | 3 | |||||
19 | 0.4 | 0 | ||||||
Sheet6 |
(3) The count start when Column B and Column C are positive value( > 0 ) AND next row of value of Column D is same value from previous row in Column D
eu 5.6.xlsb | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | B | C | D | Result | ||||
2 | ||||||||
3 | 0.1 | 0.1 | 1.4 | |||||
4 | 0 | 0.2 | 1.3 | |||||
5 | 0 | 0 | 1.3 | |||||
6 | 0.1 | 0.3 | 1.3 | 1 | ||||
7 | 0.1 | 0.5 | 1.3 | |||||
8 | 0.2 | 0.5 | 1.3 | 3 | ||||
9 | 0.3 | 0 | 1.3 | |||||
10 | 0.4 | 0.4 | 1.2 | |||||
11 | 0.1 | -0.3 | 1.5 | |||||
12 | 0.1 | 0.3 | 1.5 | 1 | ||||
13 | 0.1 | 0.6 | 1.5 | 2 | ||||
14 | -0.4 | -0.3 | 1.5 | |||||
15 | -0.2 | 0.5 | 1.5 | |||||
16 | 0.1 | 0.1 | 1.4 | |||||
17 | 0.1 | 0.3 | 1.7 | 1 | ||||
18 | 0.1 | 0.1 | 1.7 | 2 | ||||
19 | -0.4 | 0 | 1.7 | |||||
Sheet7 |
(4) Same from point (3) above, the only difference is Column B and C are negative value ( < 0 )
eu 5.6.xlsb | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | B | C | D | Result | ||||
2 | ||||||||
3 | -0.1 | -0.1 | 1.4 | |||||
4 | 0 | -0.2 | 1.3 | |||||
5 | 0 | 0 | 1.3 | |||||
6 | -0.1 | -0.3 | 1.3 | 1 | ||||
7 | -0.1 | -0.5 | 1.3 | |||||
8 | -0.2 | -0.5 | 1.3 | 3 | ||||
9 | -0.3 | 0 | 1.3 | |||||
10 | -0.4 | -0.4 | 1.2 | |||||
11 | -0.1 | 0.3 | 1.5 | |||||
12 | -0.1 | -0.3 | 1.5 | 1 | ||||
13 | -0.1 | -0.6 | 1.5 | 2 | ||||
14 | 0.4 | 0.3 | 1.5 | |||||
15 | 0.2 | -0.5 | 1.5 | |||||
16 | -0.1 | -0.1 | 1.4 | |||||
17 | -0.1 | -0.3 | 1.7 | 1 | ||||
18 | -0.1 | -0.1 | 1.7 | 2 | ||||
19 | 0.4 | 0 | 1.7 | |||||
Sheet8 |
(5) This one, I would like the count start when value of Column D at 'specific number' and Column B and Column C are postive value or negative value. (same like point 1 and 2 above).
i) Value at Column D is 1.3 with Column B and Column C are positive value
eu 5.6.xlsb | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | B | C | D | Result | ||||
2 | ||||||||
3 | 0.1 | 0.1 | 1.4 | |||||
4 | 0 | 0.2 | 1.3 | |||||
5 | 0 | 0 | 1.3 | |||||
6 | 0.1 | 0.3 | 1.3 | 1 | ||||
7 | 0.1 | 0.5 | 1.3 | |||||
8 | 0.2 | 0.5 | 1.3 | 3 | ||||
9 | 0.3 | 0 | 1.3 | |||||
10 | 0.4 | 0.4 | 1.5 | |||||
11 | 0.1 | -0.3 | 1.5 | |||||
12 | 0.1 | 0.3 | 1.3 | 1 | ||||
13 | 0.1 | 0.6 | 1.3 | 2 | ||||
14 | -0.4 | -0.3 | 1.5 | |||||
15 | -0.2 | 0.5 | 1.2 | |||||
16 | 0.1 | 0.1 | 1.2 | |||||
17 | 0.1 | 0.3 | 1.3 | 1 | ||||
18 | 0.1 | 0.1 | 1.3 | 2 | ||||
19 | -0.4 | 0 | 1.7 | |||||
Sheet9 |
ii) Value at Column D is 1.1 with Column B and Column C are negative value
eu 5.6.xlsb | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | B | C | D | Result | ||||
2 | ||||||||
3 | 0.1 | 0.1 | 1.4 | |||||
4 | 0 | 0.2 | 1.3 | |||||
5 | 0 | 0 | 1.3 | |||||
6 | -0.1 | -0.3 | 1.1 | 1 | ||||
7 | -0.1 | -0.5 | 1.1 | |||||
8 | -0.2 | -0.5 | 1.1 | 3 | ||||
9 | 0.3 | 0 | 1.3 | |||||
10 | 0.4 | 0.4 | 1.5 | |||||
11 | 0.1 | -0.3 | 1.5 | |||||
12 | -0.1 | -0.3 | 1.1 | 1 | ||||
13 | -0.1 | -0.6 | 1.1 | 2 | ||||
14 | -0.4 | -0.3 | 1.5 | |||||
15 | -0.2 | 0.5 | 1.2 | |||||
16 | 0.1 | 0.1 | 1.2 | |||||
17 | -0.1 | -0.3 | 1.1 | 1 | ||||
18 | -0.1 | -0.1 | 1.1 | 2 | ||||
19 | -0.4 | 0 | 1.7 | |||||
Sheet10 |
iii) 1.7, 1.9, 2.0, etc ...
> Let me know what do I need to change in formula for specific value in Column D with Column B and Column C are postive or negative value
Side Note:
1. The lowest positive value in Column B and Column C is 0.1
2. The lowest negative value in Column B and Column C is -0.1
3. The value in Column D always positve.
p/s : My data about 60k-250k row
: sorry for bad English
Thanks, appreaciate all your help.