# Finding all possible numbers

#### hail11

##### Board Regular
Hey I have read through the board and can not find this
looking for a vb/macro code.

i have 4 lists

a5:a9, a11:a15,a17:a21,a23:a27 these contain diffrent numbers in each.

i need to find all possible numbers and list them all in colum b

so

B1=a5 +a11 + a17+ a23
B2=a5 +a11 + a17+ a24
B3=a5 +a11 + a17+ a25
B4=a5 +a11 + a17+ a26
B5=a5 +a11 + a17+ a27
B6=a5 +a11 + a18+ a23
B7=a5 +a11 + a18+ a24
B8=a5 +a11 + a18+ a25
ect till all possibles have been calulated to cell B*

all suggestions welcome

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hey I have read through the board and can not find this
looking for a vb/macro code.

i have 4 lists

a5:a9, a11:a15,a17:a21,a23:a27 these contain diffrent numbers in each.

i need to find all possible numbers and list them all in colum b

so

B1=a5 +a11 + a17+ a23
B2=a5 +a11 + a17+ a24
B3=a5 +a11 + a17+ a25
B4=a5 +a11 + a17+ a26
B5=a5 +a11 + a17+ a27
B6=a5 +a11 + a18+ a23
B7=a5 +a11 + a18+ a24
B8=a5 +a11 + a18+ a25
ect till all possibles have been calulated to cell B*

all suggestions welcome

Hi Hail11:

Based on what I understood from your post, try ..

=A\$5+A\$11+A\$17+INDEX(A:A,22+ROWS(\$1:1))
for cell B1
and copy the formula in cell B1 down as needed.

Hi Hail11:

Based on what I understood from your post, try ..

=A\$5+A\$11+A\$17+INDEX(A:A,22+ROWS(\$1:1))
for cell B1
and copy the formula in cell B1 down as needed.

this comes up with a value error

also how would this get all possibles from the first 2 rows
a5:a10 ect

B4=a5 +a11 + a17+ a26
B5=a5 +a11 + a17+ a27
B6=a5 +a11 + a18+ a23
B7=a5 +a11 + a18+ a24
B8=a5 +a11 + a18+ a25

thanks

Hi Hail11:

First, let us look at the #VALUE error ... please tell us what is the entry in each of the following cells ...

A5
A11
A17
A23

Hi hail11

You can do it using formulas or vba.

There's lots of examples of how to write permutations with repetition in the web using vba.

I see 2 ways of doing it with formulas:

1 - a simple way. you list the permutations in auxilliary columns, indexing the ranges with a sequence of base 5 numbers, and then sum them.

Ex.:

E1: =IF(ROWS(\$E\$1:E1)>5^4,"",INDEX(\$A\$5:\$A\$9,MOD(INT((ROWS(\$E\$1:E1)-1)/5^3),5)+1))
F1: =IF(ROWS(\$F\$1:F1)>5^4,"",INDEX(\$A\$11:\$A\$15,MOD(INT((ROWS(\$F\$1:F1)-1)/5^2),5)+1))
G1: =IF(ROWS(\$G\$1:G1)>5^4,"",INDEX(\$A\$17:\$A\$21,MOD(INT((ROWS(\$G\$1:G1)-1)/5),5)+1))
H1: =IF(ROWS(\$H\$1:H1)>5^4,"",INDEX(\$A\$23:\$A\$27,MOD(ROWS(\$H\$1:H1)-1,5^1)+1))

Then in D1:

=SUM(E1:H1)

Copy down

2 - using an equivalent formula.

In B1:

=IF(ROWS(\$B\$1:B1)>5^4,"",SUM(MMULT(--({0,1,2,3,4}=MOD(INT((ROWS(\$B\$1:B1)-1)/5^{3;2;1;0}),5)),CHOOSE({1,2,3,4},\$A\$5:\$A\$9,\$A\$11:\$A\$15,\$A\$17:\$A\$21,\$A\$23:\$A\$27))*({0,1,2,3}={0;1;2;3})))

If you are not used to working with matrices or do not understand this formula, it may be preferable to use solution 1 or you'll not be able to adapt it to similar cases.

Hi Hail11:

First, let us look at the #VALUE error ... please tell us what is the entry in each of the following cells ...

A5
A11
A17
A23
A
4=Massage table
5=0.2
6=0.5
7=1
8=1.5
9=2
10=Soaker tub
11=0.1
12=0.5
13=1
14=1.5
15=2
16=First aid station
17=0.2
18=0.4
19=0.6
20=0.8
21=1
22=Traininers
23=0.2
24=0.5
25=0.1
26=1.5
27=2

I could do it like this down but the number of books i have.. :S

example

=SUM(\$A\$7,\$A\$13,\$A\$19,A25)
=SUM(\$A\$7,\$A\$13,\$A\$19,A26)
=SUM(\$A\$7,\$A\$13,\$A\$19,A27)
=SUM(\$A\$7,\$A\$13,\$A\$19,A28)
=SUM(\$A\$7,\$A\$13,\$A\$19,A29)
=SUM(\$A\$7,\$A\$13,\$A\$20,A25)
=SUM(\$A\$7,\$A\$13,\$A\$20,A26)
=SUM(\$A\$7,\$A\$13,\$A\$20,A27)
=SUM(\$A\$7,\$A\$13,\$A\$20,A28)
=SUM(\$A\$7,\$A\$13,\$A\$20,A29)
=SUM(\$A\$7,\$A\$13,\$A\$21,A25)
=SUM(\$A\$7,\$A\$13,\$A\$21,A26)
=SUM(\$A\$7,\$A\$13,\$A\$21,A27)
=SUM(\$A\$7,\$A\$13,\$A\$21,A28)
=SUM(\$A\$7,\$A\$13,\$A\$21,A29)
=SUM(\$A\$7,\$A\$13,\$A\$22,A25)
=SUM(\$A\$7,\$A\$13,\$A\$22,A26)
=SUM(\$A\$7,\$A\$13,\$A\$22,A27)
=SUM(\$A\$7,\$A\$13,\$A\$22,A28)
=SUM(\$A\$7,\$A\$13,\$A\$22,A29)
=SUM(\$A\$7,\$A\$13,\$A\$23,A25)
=SUM(\$A\$7,\$A\$13,\$A\$23,A26)
=SUM(\$A\$7,\$A\$13,\$A\$23,A27)
=SUM(\$A\$7,\$A\$13,\$A\$23,A28)
=SUM(\$A\$7,\$A\$13,\$A\$23,A29)
=SUM(\$A\$7,\$A\$14,\$A\$19,A25)
=SUM(\$A\$7,\$A\$14,\$A\$19,A26)
=SUM(\$A\$7,\$A\$14,\$A\$19,A27)
=SUM(\$A\$7,\$A\$14,\$A\$19,A28)
=SUM(\$A\$7,\$A\$14,\$A\$19,A29)
=SUM(\$A\$7,\$A\$14,\$A\$20,A25)
=SUM(\$A\$7,\$A\$14,\$A\$20,A26)
=SUM(\$A\$7,\$A\$14,\$A\$20,A27)
=SUM(\$A\$7,\$A\$14,\$A\$20,A28)
=SUM(\$A\$7,\$A\$14,\$A\$20,A29)
=SUM(\$A\$7,\$A\$14,\$A\$21,A25)
=SUM(\$A\$7,\$A\$14,\$A\$21,A26)
=SUM(\$A\$7,\$A\$14,\$A\$21,A27)
=SUM(\$A\$7,\$A\$14,\$A\$21,A28)
=SUM(\$A\$7,\$A\$14,\$A\$21,A29)
=SUM(\$A\$7,\$A\$14,\$A\$22,A25)
=SUM(\$A\$7,\$A\$14,\$A\$22,A26)
=SUM(\$A\$7,\$A\$14,\$A\$22,A27)
=SUM(\$A\$7,\$A\$14,\$A\$22,A28)
=SUM(\$A\$7,\$A\$14,\$A\$22,A29)
=SUM(\$A\$7,\$A\$14,\$A\$23,A25)
=SUM(\$A\$7,\$A\$14,\$A\$23,A26)
=SUM(\$A\$7,\$A\$14,\$A\$23,A27)
=SUM(\$A\$7,\$A\$14,\$A\$23,A28)
=SUM(\$A\$7,\$A\$14,\$A\$23,A29)
=SUM(\$A\$7,\$A\$15,\$A\$19,A25)
=SUM(\$A\$7,\$A\$15,\$A\$19,A26)
=SUM(\$A\$7,\$A\$15,\$A\$19,A27)
=SUM(\$A\$7,\$A\$15,\$A\$19,A28)
=SUM(\$A\$7,\$A\$15,\$A\$19,A29)
=SUM(\$A\$7,\$A\$15,\$A\$20,A25)
=SUM(\$A\$7,\$A\$15,\$A\$20,A26)
=SUM(\$A\$7,\$A\$15,\$A\$20,A27)
=SUM(\$A\$7,\$A\$15,\$A\$20,A28)
=SUM(\$A\$7,\$A\$15,\$A\$20,A29)
=SUM(\$A\$7,\$A\$15,\$A\$21,A25)
=SUM(\$A\$7,\$A\$15,\$A\$21,A26)
=SUM(\$A\$7,\$A\$15,\$A\$21,A27)
=SUM(\$A\$7,\$A\$15,\$A\$21,A28)
=SUM(\$A\$7,\$A\$15,\$A\$21,A29)
=SUM(\$A\$7,\$A\$15,\$A\$22,A25)
=SUM(\$A\$7,\$A\$15,\$A\$22,A26)
=SUM(\$A\$7,\$A\$15,\$A\$22,A27)
=SUM(\$A\$7,\$A\$15,\$A\$22,A28)
=SUM(\$A\$7,\$A\$15,\$A\$22,A29)
=SUM(\$A\$7,\$A\$15,\$A\$23,A25)
=SUM(\$A\$7,\$A\$15,\$A\$23,A26)
=SUM(\$A\$7,\$A\$15,\$A\$23,A27)
=SUM(\$A\$7,\$A\$15,\$A\$23,A28)
=SUM(\$A\$7,\$A\$15,\$A\$23,A29)
=SUM(\$A\$7,\$A\$16,\$A\$19,A25)
=SUM(\$A\$7,\$A\$16,\$A\$19,A26)
=SUM(\$A\$7,\$A\$16,\$A\$19,A27)
=SUM(\$A\$7,\$A\$16,\$A\$19,A28)
=SUM(\$A\$7,\$A\$16,\$A\$19,A29)
=SUM(\$A\$7,\$A\$16,\$A\$20,A25)
=SUM(\$A\$7,\$A\$16,\$A\$20,A26)
=SUM(\$A\$7,\$A\$16,\$A\$20,A27)
=SUM(\$A\$7,\$A\$16,\$A\$20,A28)
=SUM(\$A\$7,\$A\$16,\$A\$20,A29)
=SUM(\$A\$7,\$A\$16,\$A\$21,A25)
=SUM(\$A\$7,\$A\$16,\$A\$21,A26)
=SUM(\$A\$7,\$A\$16,\$A\$21,A27)
=SUM(\$A\$7,\$A\$16,\$A\$21,A28)
=SUM(\$A\$7,\$A\$16,\$A\$21,A29)
=SUM(\$A\$7,\$A\$16,\$A\$22,A25)
=SUM(\$A\$7,\$A\$16,\$A\$22,A26)
=SUM(\$A\$7,\$A\$16,\$A\$22,A27)
=SUM(\$A\$7,\$A\$16,\$A\$22,A28)
=SUM(\$A\$7,\$A\$16,\$A\$22,A29)
=SUM(\$A\$7,\$A\$16,\$A\$23,A25)
=SUM(\$A\$7,\$A\$16,\$A\$23,A26)
=SUM(\$A\$7,\$A\$16,\$A\$23,A27)
=SUM(\$A\$7,\$A\$16,\$A\$23,A28)
=SUM(\$A\$7,\$A\$16,\$A\$23,A29)
=SUM(\$A\$7,\$A\$17,\$A\$19,A25)
=SUM(\$A\$7,\$A\$17,\$A\$19,A26)
=SUM(\$A\$7,\$A\$17,\$A\$19,A27)
=SUM(\$A\$7,\$A\$17,\$A\$19,A28)
=SUM(\$A\$7,\$A\$17,\$A\$19,A29)
=SUM(\$A\$7,\$A\$17,\$A\$20,A25)
=SUM(\$A\$7,\$A\$17,\$A\$20,A26)
=SUM(\$A\$7,\$A\$17,\$A\$20,A27)
=SUM(\$A\$7,\$A\$17,\$A\$20,A28)
=SUM(\$A\$7,\$A\$17,\$A\$20,A29)
=SUM(\$A\$7,\$A\$17,\$A\$21,A25)
=SUM(\$A\$7,\$A\$17,\$A\$21,A26)
=SUM(\$A\$7,\$A\$17,\$A\$21,A27)
=SUM(\$A\$7,\$A\$17,\$A\$21,A28)
=SUM(\$A\$7,\$A\$17,\$A\$21,A29)
=SUM(\$A\$7,\$A\$17,\$A\$22,A25)
=SUM(\$A\$7,\$A\$17,\$A\$22,A26)
=SUM(\$A\$7,\$A\$17,\$A\$22,A27)
=SUM(\$A\$7,\$A\$17,\$A\$22,A28)
=SUM(\$A\$7,\$A\$17,\$A\$22,A29)
=SUM(\$A\$7,\$A\$17,\$A\$23,A25)
=SUM(\$A\$7,\$A\$17,\$A\$23,A26)
=SUM(\$A\$7,\$A\$17,\$A\$23,A27)
=SUM(\$A\$7,\$A\$17,\$A\$23,A28)
=SUM(\$A\$7,\$A\$17,\$A\$23,A29)
=SUM(\$A\$7,\$A\$17,\$A\$23,A30)
ect

Last edited:
The formula in B1 can be simplified. In B1:

=IF(ROWS(\$B\$1:B1)>5^4,"",SUMPRODUCT((({0;1;2;3;4}=MOD(INT((ROWS(\$B\$1:B1)-1)/5^{3,2,1,0}),5)))*CHOOSE({1,2,3,4},\$A\$5:\$A\$9,\$A\$11:\$A\$15,\$A\$17:\$A\$21,\$A\$23:\$A\$27)))
Copy down

Replies
4
Views
243
Replies
2
Views
155
Replies
2
Views
173
Replies
6
Views
228
Replies
8
Views
331

1,203,558
Messages
6,056,078
Members
444,844
Latest member
Taps07

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