## Zen archery problem

**vicktor schausberger writes:**

Zen Archery

In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are

97,101,139,41,37,31,29,89,23,**19**,8,13,

131,19,73,97,**19**,139,79,67,61,17,113,127

Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?

There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8.

**Answer:**

The text above seems to be copied from this website: http://www.merriampark.com/comb.htm

Let´s create a user defined function that checks every combination and filters values where the sum is equal to 200.

I don´t understand why 19 occurs twice in the 24 numbers on the target. Because of this, the udf returns duplicate rows, therefore some combinations are not unique.

**User defined function**

Function SumComb(sum As Range, rng As Variant, num As Integer) Dim Arr(), i, Ans As Single Dim svVal() As Variant Dim tmp As Variant ReDim Arr(num - 1) ReDim svVal(num - 1, 0) rng = rng.Value For i = 0 To num - 1 Arr(i) = i + 1 Next i Comb = Application.WorksheetFunction.Combin(UBound(rng, 1), num) For j = 1 To Comb tmp = 0 For i = 0 To num - 1 tmp = tmp + rng(Arr(i), 1) Next i If tmp = sum Then For i = 0 To num - 1 svVal(i, UBound(svVal, 2)) = rng(Arr(i), 1) Next i ReDim Preserve svVal(UBound(svVal, 1), UBound(svVal, 2) + 1) End If Arr(num - 1) = Arr(num - 1) + 1 k = num For i = num - 1 To 0 Step -1 If Arr(i) = UBound(rng, 1) - num + i + 2 Then k = i Arr(i - 1) = Arr(i - 1) + 1 Else If k <> 0 Then k = i Else k = num End If Exit For End If Next i If k <> num Then For i = k To num - 2 Arr(i + 1) = Arr(i) + 1 Next i End If Next j ReDim Preserve svVal(UBound(svVal, 1), UBound(svVal, 2) - 1) SumComb = Application.Transpose(svVal) End Function

**Where to copy udf?**

Press Alt+F11

**Explaining user defined function**

**How to enter array formula**

**Download excel 2007 *.xlsm file **

### Category: Excel

This post explains how to lookup a value and return multiple values. No array formula required.

Comments(439) Filed in category: Excel, VLOOKUP and return multiple values

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. […]

Comments(249) Filed in category: Concatenate, Excel, Textjoin

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: […]

Comments(161) Filed in category: Charts, Excel, Interactive

### 6 Responses to “Zen archery problem”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Sweeet, keep working

well, today june 14 I discovery the answer of my question; little late, sorry about it, also apologise I didn't mention the web where the problem come from.

Now I have a real question and is not belong to any website.

I have a dynamic database B1:G1990. and also in K1:P1 a dynamic new list of numbers. So let me call the database the BG list, and the other KP list.

from my BG list I need to calculate all the GAPS.

gap example. 12-15-17-18-31-42 this is the number now the gaps is the differences among the numbers eg: 3-2-1-13-11 (12-15=3 etc.generate the list with the same gaps and remove duplicates from KP list.

the second number in my list BG is 7-14-15-22-37-38 now calculate the INTERVALS.

interval example. 12-15-17-18-31-42 row #1

7-14-15-22-37-38 row #2

the interval is the differences among the rows. in this case are 5-1-2-4-6-4 .

so the code must generate all intervals in the BG list and compare the results with the row #1 in BGlist and remove any number with the sames interval. example: if any of the new calculation is for example 7-16-19-14-25-46 this number have the same interval as row #2 so remove.

any help like the one above would be nice, I hope to see it soon.

vicktor schausberger,

I have a dynamic database B1:G1990. and also in K1:P1 a dynamic new list of numbers. So let me call the database the BG list, and the other KP list.from my BG list I need to calculate all the GAPS.

gap example. 12-15-17-18-31-42 this is the number now the gaps is the differences among the numbers eg: 3-2-1-13-11 (12-15=3 etc.generate the list with the same gaps and remove duplicates from KP list.

Can you explain "generate the list with the same gaps and remove duplicates from KP list" in greater detail?

thanks," generate a list of combination base on the gaps".

example about what is GAPS, in the combination 1-5-6-11-18-30

the abs value difference will be 1-5=4; 5-6=1; 6-11=5; etc.

so for the code I must Input the list of gaps, (in my case is 2000)

just in this example the gaps are 4-1-5-7-12 so the list generate by this gaps gonna be 1-5-6-11-18-30

2-6-7-12-19-31

3-7-8-13-20-32

4-8-9-14-21-33 until the last number is iqual to 53

5-9-10-15-22-34 the last in the list is 24-28-29-34-41-53.

about remove from KP list what really is I am doing a code like in the zen archery problem but instead of 5 number I want 6.

now lets put together, after your code, let's name your code as "THE ZEN CODE" (in my case is 6 instead of 5) generate the list, this list has to be compare with the list I am requesting here, and any duplicate must be delete fro the zen code.

so the code I am looking for must be give me the chance to paste my gap list or refere to the sheet name is filed. thanks, I know you are a genius.

The images of the worksheet you upload here, is special software, and is possible for any forum?

Thanks for your attention.

Hei Oskar, jeg prøvervmeg på norsk. Takk for en flott side med masse gode eksempler på VBA og UDF. Det er en flott måte å lære Excel/VBA på, selv om det kan være vanskelig å skjønne andres koder. Jeg harvstudert din zen archery løsning og den er meget bra. Jeg harprøvd å endre rng til A2:A9 (med tallene 1 til 9) og sum til 16 og num til 4. Da får jeg ingen svar bare mange #verdi i D:H -kolonnen. Det skulle jo være flere løsninger på dette f.eks 1,3,4,8. Kan du forklare meg hvorfor jeg ikke får din UDF til å virke?