# Random Number Quiz

#### PuzzlerUK

##### New Member
Hi, I am looking to see if anyone can help with regards to an excel math formula to help me solve and generate an unusual math puzzle I came across recently.

I loved the puzzle and am interested in trying to develop a formula for it in Excel but to no avail.

The puzzle presented 25 random numbers from 1 to 100. From these 25 numbers, 5 numbers added up to a value provided. You had to examine all of the 25 numbers and work out which ones added up to the value. An interesting design.

I can generate 5 random numbers and work out how 5 of these can add up to a given value, however have no idea how to generate 25 random numbers of which only 5 of the random numbers add up to a given value.

Can anyone help?

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### shg

##### MrExcel MVP
I don't think that's going to happen with a formula. You'd have to ratchet through all the combinations and check their sum.

There is a way to find the number of combination that total to a given sum using generating functions. There are, for example, 460,300 5-combinations of {1,2,3,...,100} that total 252 (and the same number for 253).

Solver might eventually get there, but it has no special propensity for solving that type of problem.

You could create a table with 53,130 rows that contains all the combinations of 25 choose 5 and check the sum on each row with a formula. Once you do the non-recurring part, the answers for a given problem with be near instantaneous.

Last edited:

#### PuzzlerUK

##### New Member
Thanks, is it possible to generate 5 random numbers from 25 numbers (randomly chosen from 1 to 100) whose value adds up to a provided number for example 36.
There may be more than one solution using the other 20 numbers, but excel would provide one answer using a formula?

#### shg

##### MrExcel MVP
Simple in code, but no way I know of using formulas -- but there are better formula guys here than YT.

#### lrobbo314

##### Well-known Member
Of the 25 random numbers, are duplicates allowed?

No

#### diddi

##### Well-known Member
Definitely a VBA job. Generate the 25 numbers, then check the combination totals, counting each total in an array. then check the array for single solutions. you might end up with many solutions from the same set of numbers so you yet many puzzles in 1 go

#### Eric W

##### MrExcel MVP
If you're trying to create a puzzle where you generate 25 numbers, and there is a unique solution where 5 of them add up to a particular sum, I can think of a couple ways to do it. Neither is great, but might lead to something more workable.

The first is pretty trivial. Include the numbers 1-5, set the target to 15, and it's pretty obvious that any other number would result in a total greater than 15.

The second option is to select 20 numbers that are equivalent to 1 mod 6, then include 5 numbers that are equivalent to 0 mod 6. Then set your target to the sum of the 5 numbers that are equivalent to 0 mod 6. A little modulus arithmetic tells us that including any of the 1 mod 6 numbers would result in a number that has a remainder of 1-5, so it can't sum to the target number. We know the original creators of the puzzle didn't do this, since there are not 20 numbers equivalent to 1 mod 6 less than 100.

Next topic, if you want a formula to find certain values that sum to a given value, it can be done, but your condition of having 25 numbers to choose from is just out of reach. Consider:

Dynamic functions.xlsm
ABCDE
1Nums# of valsTargetCount
2670203315
3720
4850
5101187
65801187
7420
8610
960
10600
111001
12190
13970
14911
15290
16931
17510
18490
19140
2031
21441
2271
2390
2417
2559
2681
Sheet4
Cell Formulas
RangeFormula
D5D5=MATCH(D2,MMULT(--MID(BASE(ROW(INDIRECT("1:"&2^C2-1)),2,C2),TRANSPOSE(ROW(INDIRECT("1:"&C2))),1),OFFSET(A2,0,0,C2,1))*(LEN(SUBSTITUTE(BASE(ROW(INDIRECT("1:"&2^C2-1)),2,C2),"0",""))=E2),0)
D6D6=MATCH(D2,MMULT(--MID(BASE(SEQUENCE(2^C2),2,C2),SEQUENCE(,C2),1),OFFSET(A2,0,0,C2,1))*(LEN(SUBSTITUTE(BASE(SEQUENCE(2^C2),2,C2),"0",""))=E2),0)
B2:B26B2=MID(BASE(\$D\$6,2,\$C\$2),ROWS(\$B\$2:\$B2),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

The D5 formula works in Excel 2013 and newer, the D6 formula works in Excel 365 with the new array formulas. The D5 formula fails after 20 values because (in part) it uses ROW to create an array of values, and Excel 2013+ has only 2^20 rows. The D6 formula uses SEQUENCE instead, but it too fails after 20 due to lack of resources. After all, internally it's doing matrix multiplication on an array with millions of rows and dozens of columns.

Either version figures out all 2^n possibilities, then excludes any that use more or less than 5 numbers. This is very inefficient. There is an algorithm that calculates only the combinations we want, but I don't know how to build that into a formula, it's definitely a VBA task.

Given all this, this formula might work to solve some of your puzzles. If the numbers are randomly selected, there will probably be many solutions. So try the formula, see if it works. If not, sort the input list differently and try again. If you try again and still don't get an answer, it may be that whoever created the puzzle did use some math tricks to ensure only one answer, and one or more of the needed numbers are in the bottom 5 values.

Over all, solving this kind of problem is pretty tough. See:

Also see this thread, especially posts 8 and 7:

That has a couple of VBA routines designed to solve the general case of this problem.

So there you are, more than you ever wanted to know.

#### diddi

##### Well-known Member
this was my solution...
VBA Code:
``````Sub Puzzle()
Dim Puz(100) As Long, PuzTots(500) As Long, PuzNums(25) As Long
Dim Nums As Long, Tot As Long, Slns As Long, RNum As Long

' select 25 numbers, tag as 1 in the Puzzle array
While Row < 25
RNum = Int(Rnd() * 100) + 1
If Puz(RNum) = 0 Then
Puz(RNum) = 1
Row = Row + 1
End If
Wend

' assign the 25 selected numbers to the PuzNumbers array
For Row = 1 To 100
If Puz(Row) = 1 Then
Nums = Nums + 1
Cells(Nums + 2, 1) = Row
PuzNums(Nums) = Row
End If
Next Row

' cycle through the combinations and tally the totals in PuzTotals array
For Row1 = 1 To 21
For row2 = Row1 + 1 To 22
For row3 = row2 + 1 To 23
For row4 = row3 + 1 To 24
For row5 = row4 + 1 To 25
Tot = PuzNums(Row1) + PuzNums(row2) + PuzNums(row3) + PuzNums(row4) + PuzNums(row5)
PuzTots(Tot) = PuzTots(Tot) + 1
Next row5
Next row4
Next row3
Next row2
Next Row1

' pick the solutions from the PuzTot array where the count = 1
For Row = 1 To 500
If PuzTots(Row) = 1 Then
Slns = Slns + 1
Cells(Slns + 2, 3) = Row
End If
Next Row
End Sub``````

calculation was virtually instant despite the apparent length of the loops.

Replies
6
Views
131
Replies
11
Views
423
Replies
10
Views
219
Replies
0
Views
86
Replies
0
Views
400

1,185,983
Messages
5,955,139
Members
438,182
Latest member
karamarrott

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