#### Ufoo

##### Member
Hello excel gurus, please see the attached sheet and help. Thanks

#### Attachments

• 17.5 KB Views: 14

#### salim hasan

##### Member
in the cells(B4) Try this formula
=IF(B2<>"",0,SUM(A\$4,1))
and fill to right

#### Attachments

• 24 KB Views: 2

#### Fluff13

##### Active Member
Maybe in C2 copied right
=IF(C4<>0,"",LOOKUP(2,1/(\$B2:B2<>""),\$B2:B2)+OFFSET(\$A4,0,COUNT(\$B2:B2)))

#### Ufoo

##### Member
in the cells(B4) Try this formula
=IF(B2<>"",0,SUM(A\$4,1))
and fill to right
Thanks. But it is not working. You might build up on your idea to improve it

#### Ufoo

##### Member
Maybe in C2 copied right
=IF(C4<>0,"",LOOKUP(2,1/(\$B2:B2<>""),\$B2:B2)+OFFSET(\$A4,0,COUNT(\$B2:B2)))
Working perfectly. Thanks

#### Fluff13

##### Active Member
You're welcome & thanks for the feedback

#### John Jairo V

##### Well-Known Member
Hi, to all!

Another similar approach, without volatile functions:

[C2] : =IF(C4,"",LOOKUP(9E+307,\$B2:B2)+INDEX(\$B4:\$M4,COUNT(\$B2:B2))) <-- Drag it right.
or (positive numbers):
[C2] : =IF(C4,"",-LOOKUP(,-\$B2:B2)+INDEX(\$B4:\$M4,COUNT(\$B2:B2))) <-- Drag it right.

Blessings!

#### Peter Bartholomew

##### Well-Known Member
This is a dynamic array solution. Let k be defined to refer to
= SEQUENCE(1, 21, 0)
i.e. integers from 1 to 20. The required number sequence is given by
= IF( MOD( k, 4 ), "", 5 + (1 + k/4) * k/8 )

Because dynamic arrays do not evaluate accumulations, I summed the series analytically and entered the resulting formula.

#### Attachments

• 17.4 KB Views: 3