How to change this

[adavies]

Hi All,

Right now this formula initiates this sequence 2,4,B

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

How would I change it to make the sequence go B,2,4 ?

Help appreciated

 

[GlennUK]

Well, the calculation part gives a sequence of 1,2,0,1,2,0 etc, and your lookup is sequenced 0,1,2, so you could change the order of the stored values in the LOOKUP area:

{0,4;1,"B";2,2}

 

[adavies]

Thank You, now how can I modify

How can I modify the same formula to do this

There is only four cells with names,

The setup looks like this

A5-A8 = Names
the formula references those cells looking for name entries, if there are no name entries then that line is left blank

E5-E8 = b,2,4 or 2
This is where the number sequence goes

I would like it to only produce 1 B, if there is already a B then it should skip B in the sequence and go like this B,2,4,2

but if there are only 2 cells or less that have name entries than the B is omitted from the sequence thus only 2 or 4 be entered

It sound hard for a beginner at excel, but maybe an expert might be able to figure this out. I would assume an array formula be used like the one I have already but more added?

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

 

[GlennUK]

So, are you saying you want the sequences to be like this:
2
2,4
B,2,4
B,2,4,2
B,2,4,2,4
B,2,4,2,4,2
B,2,4,2,4,2,4

and so on?

 

[adavies]

ADVERTISEMENT

yes kind of...

There are only 4 cells, so the most it would go is B,2,4,2

but if there are only 2 name entries or less in A5 to A8 then it would only be 2 or 4


Is this formula even possible or should I look into an alternative?

 

[GlennUK]

Try this:

=IF(A5="","",LOOKUP(SUMPRODUCT(--($A$5:A5<>""))+IF(SUMPRODUCT(--($A$5:$A$8<>""))<3,1,0)-1,{0,"B";1,2;2,4;3,2}))

 

[adavies]

Thanks, That is exactly what I was looking for