If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Help, required single formula, instead of using 3

Thread starter Kishan
Start date Oct 18, 2019
Tags

advance column formula make single

K

Kishan

Well-known Member

Oct 18, 2019

#1

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

To get final result in the column K, I am using 2 more formula in the column E & G, please can someone make a single formula in the column K. </SPAN></SPAN>

Using example....</SPAN></SPAN>

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

1

2

3

4

5

P3

1

1

1

6

1

7

2

8

1

9

1

2

0

2

10

1

3

0

3

11

X

0

1

1

12

1

1

0

1

13

X

0

1

1

14

1

1

0

1

15

X

0

1

1

16

X

0

2

2

17

X

0

3

3

18

1

1

0

1

19

1

2

0

2

20

1

3

0

3

21

1

4

0

4

22

X

0

1

1

23

1

1

0

1

24

1

2

0

2

25

1

3

0

3

26

1

4

0

4

27

X

0

1

1

28

1

1

0

1

29

1

2

0

2

30

1

3

0

3

31

1

4

0

4

32

1

5

0

5

33

1

6

0

6

34

1

7

0

7

35

1

8

0

8

36

X

0

1

1

37

1

1

0

1

38

1

2

0

2

39

2

0

1

1

40

1

1

0

1

41

X

0

1

1

42

2

0

2

2

43

1

1

0

1

44

45

46

Sheet2

Cell Formulas
Range	Formula
E6	=IF(C6=1,1,0)
E7	=IF(C7=1,1+E6,0)
E8	=IF(C8=1,1+E7,0)
G6	=IF(C6=1,0,1)
G7	=IF(C7=1,0,1+G6)
G8	=IF(C8=1,0,1+G7)
K6	=IF(E6>0,E6,G6)
K7	=IF(E7>0,E7,G7)
K8	=IF(E8>0,E8,G8)

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>

Last edited: Oct 18, 2019

Excel Facts

Copy PDF to Excel

Click here to reveal answer

Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Sort by date Sort by votes

M

Marcelo Branco

MrExcel MVP

Oct 18, 2019

#2

Maybe...

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

C

[/td][td="bgcolor: #DCE6F1"]

D

[/td][td="bgcolor: #DCE6F1"]

E

[/td][td="bgcolor: #DCE6F1"]

F

[/td][td="bgcolor: #DCE6F1"]

G

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

5

[/td][td]

P3

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7

[/td][td]

2

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

2

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

3

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11

[/td][td]

X

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13

[/td][td]

X

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15

[/td][td]

X

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16

[/td][td]

X

[/td][td][/td][td][/td][td][/td][td]

2

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17

[/td][td]

X

[/td][td][/td][td][/td][td][/td][td]

3

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

19

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

2

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

20

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

3

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

21

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

4

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

22

[/td][td]

X

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

23

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

24

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

2

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

25

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

3

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

26

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

4

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

27

[/td][td]

X

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

28

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

29

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

2

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

30

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

3

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

31

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

4

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

32

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

5

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

33

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

6

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

34

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

7

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

35

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

8

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

36

[/td][td]

X

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

37

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

38

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

2

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

39

[/td][td]

2

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

40

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

41

[/td][td]

X

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

42

[/td][td]

2

[/td][td][/td][td][/td][td][/td][td]

2

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

43

[/td][td]

1

[/td][td][/td][td][/td][td][/td][td]

1

[/td][/tr]
[/table]

Formula in G6 copied down
=IF(C6=1,COUNTIF(C6:INDEX(C$6:C6,IF(COUNTIF(C$6:C6,"<>1")=0,1,LOOKUP(2,1/(C$6:C6<>1),ROW(C$6:C6)-ROW(C$6)+1))),1),COUNTIF(C6:INDEX(C$6:C6,IF(COUNTIF(C$6:C6,1)=0,1,LOOKUP(2,1/(C$6:C6=1),ROW(C$6:C6)-ROW(C$6)+1))),"<>1"))

Hope this helps

M.

Upvote 0

F

Finalfight40

Active Member

Oct 18, 2019

#3

Ignore this, found a problem with it.

Last edited: Oct 18, 2019

Upvote 0

Eric W

MrExcel MVP

Oct 18, 2019

#4

How about:

K6: =IF(C6=1,IF(C5=1,K5+1,1),IF(C5<>1,K5+1,1))

Upvote 0

K

Kishan

Well-known Member

Oct 18, 2019

#5

Marcelo Branco said:
Maybe...

Formula in G6 copied down
=IF(C6=1,COUNTIF(C6:INDEX(C$6:C6,IF(COUNTIF(C$6:C6,"<>1")=0,1,LOOKUP(2,1/(C$6:C6<>1),ROW(C$6:C6)-ROW(C$6)+1))),1),COUNTIF(C6:INDEX(C$6:C6,IF(COUNTIF(C$6:C6,1)=0,1,LOOKUP(2,1/(C$6:C6=1),ROW(C$6:C6)-ROW(C$6)+1))),"<>1"))

Hope this helps

M.

Marcelo Branco, thank you for your help the formula worked fine.</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Kishan

</SPAN></SPAN>

Upvote 0

K

Kishan

Well-known Member

Oct 18, 2019

#6

Eric W said:
How about:

K6: =IF(C6=1,IF(C5=1,K5+1,1),IF(C5<>1,K5+1,1))

Eric W, thanks for a formula, the formula look to me tiny and magical

results splendid as appeal</SPAN></SPAN>

Kind Regards,</SPAN>
Kishan

</SPAN></SPAN>

Upvote 0

You must log in or register to reply here.

Similar threads

O

Solved

How to calculate annual average growth (i.e., price appreciation) rate for an investment portfolio.

Replies: 3

Views: 144

Oct 21, 2024

kvsrinivasamurthy

K

M

Question

Mock Finance sheet

Replies: 3

Views: 339

Apr 25, 2024

Marvo

M

Solved

Join text in excel

2

Replies: 15

Views: 686

May 28, 2024

Burrgogi

L

Question

Help with IF formula with Negative Time YES OR NO

Replies: 1

Views: 578

May 23, 2023

etaf

E

Solved

Help with issues calculating using dates

2

Replies: 16

Views: 1K

May 31, 2023

Anthony47

A

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,224,817

Messages: 6,181,149

Members: 453,021

Latest member: Justyna P

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top