# How to change this

##### Board Regular
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

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### GlennUK

##### Well-known Member
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:
Code:
``{0,4;1,"B";2,2}``

##### Board Regular
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

##### Well-known Member
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?

##### Board Regular
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

##### Well-known Member
Try this:
Code:
``=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}))``

##### Board Regular
Thanks, That is exactly what I was looking for

Replies
3
Views
191
Replies
10
Views
246
Replies
11
Views
207
Replies
5
Views
141
Replies
4
Views
287

1,195,670
Messages
6,011,074
Members
441,581
Latest member
rp4717

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back