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

Excel motto

Click here to reveal answer

Not everything I do at work revolves around Excel. Only the fun parts.

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: 90

Oct 21, 2024

kvsrinivasamurthy

K

M

Question

Mock Finance sheet

Replies: 3

Views: 310

Apr 25, 2024

Marvo

M

Solved

Join text in excel

2

Replies: 15

Views: 636

May 28, 2024

Burrgogi

L

Question

Help with IF formula with Negative Time YES OR NO

Replies: 1

Views: 565

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,223,227

Messages: 6,170,848

Members: 452,361

Latest member: d3ad3y3

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top