Number sequence formula?

[adavies]

Hi All,

I am trying to figure out a number sequence formula.

Here's how the sheet will look

A1 = Name
B1 = number sequence (2,4,B (repeat))

However B1 will reference A1 and if it is blank it will not input a sequence number (2,4,B) It will be left blank, but the next line should carry on where the sequence left off.

What would be the best formula to use for this?

 

[adavies]

It says there is a problem with the number 4 in the formula?

 

[Andrew Poulsom]

It worked when I tried it. Make sure you paste the formula from my post.

=IF(ISBLANK(A1),"",LOOKUP(MOD(COUNTA(A$1:A1),3),{0,"B";1,2;2,4}))

You do have the comma as the list separator don't you?

 

[adavies]

Yes my mistake I entered the formula incorrectly. It works but A8 is blank but it is still showing a number?

 

[Andrew Poulsom]

If I remove Trish from A8, B8 becomes blank instead of B. Are you sure the cell is truly blank?

 

[adavies]

A8 is not truly blank as it has a formula in that cell but there is nothing else other than the formula... is there a way it could ignore the formula in that cell? Because as soon a I delete the formula it works fine and dandy - and I need that formula left there

 

[Peter_SSs]

Modifying Andrew's formula:

If the blank cells have formulas returning "", or are actually blank, then try B1 (copied down):
=IF(A1="","",LOOKUP(MOD(COUNTIF(A$1:A1,">=A"),3),{0,"B";1,2;2,4}))

Mr Excel.xls
	A	B	C	D
1	John	2
2	bob	4
3
4	Chris	B
5	Jen	2
6	Roger	4
7
8	Trish	B
9
2 4 B

 

[Andrew Poulsom]

Also try:

=IF(A1="","",LOOKUP(MOD(SUMPRODUCT(--(A$1:A1<>"")),3),{0,"B";1,2;2,4}))

which will cater for "" values returned by a formula.