Solving the 0-1 Knapsack problem with Excel

Given a list of items each with a value and a weight, the Knapsack problem seeks to find the set of items with the largest combined value within a given weight limit. There are a nice dynamic programming solution which I decided to implement in a spread sheet. I used Google Sheets but the solution is exported as an excel-sheet.

The solution builds a Knapsack table for round 0,1,…,limit. In each round r the solution for the problem with limit r is constructed as a column in the table, so the table has to be as wide as the maximum limit. Once the table is built, the solution can be found using backtracking. This is all described pretty well on Wikipedia, https://en.wikipedia.org/wiki/Knapsack_problem#0/1_knapsack_problem.

The main challenge was to translate this algorithm from procedural pseudocode to a spreadsheet. Building the table is simple enough (once you learn the OFFSET command in Excel which allows you to add or subtract a variable number of rows and columns from a given position), but the backtracking was a bit more tricky.

Assuming that the weights are stored in column A from row 3 and the corresponding values are stored in column B, the table starts in column D. The round numbers are stored in row 1 from columnD. Row 2 are just 0’s and all other entries are (the one below is from D3):

=IF($A3>D$1; D2; MAX(D2; OFFSET(INDIRECT(ADDRESS(ROW(); COLUMN()));-1;-$A3) + $B3))

The table stops after round 40, so we find the solution with weight at most 40.

The backtracking to find the actual solution after building the table is done in column AT and AU. In the first column, the number of weight spent after 40 rounds are calculated from bottom to top row using the formula (from AT3):

=IF(OFFSET(INDIRECT(ADDRESS(ROW(); COLUMN()));0;-AT23-2) <> OFFSET(INDIRECT(ADDRESS(ROW(); COLUMN()));-1;-AT23-2); A22 + AT23; AT23)

The solution is shown in column AU where for each row we simply check if the accumulated weight increased with this item.

Gram-Schmidt process for integers

Given a set of linearly independent vectors, the Gram-Schmidt process produces a new set of vectors spanning the same subset, but the new set of vectors are now mutually orthogonal. The algorithm is usually stated as working over a vector space, but will in this post be altered to work over a module which is a vector space where the scalars are elements in a ring instead of a field. This could for instance be the case if we are given a set of integer vectors we wish to orthogonalise.

The original version can be described as follows: Given a set of vectors \(v_1, \ldots, v_n\) we define \(u_1 = v_1\) and

\(u_k = v_k – \text{proj}_{u_1}(v_k) – \text{proj}_{u_2}(v_k) – \cdots – \text{proj}_{u_{k-1}}(v_k)\)

for \(k = 2,3,\ldots,n\). The projection operator is defined as

\(\text{proj}_u(v) = \frac{\langle u, v \rangle}{\langle u, u \rangle} u\).

If we say that all \(v_i\) have integer entries, we see that \(u_i\) must have rational entries, and simply scaling each vector with a multiple of all the denominators of the entries will give a vector parallel to the original vector but with integer entries. But what if we are interested in an algorithm that can only represent integers?

The algorithm is presented below. Note that the algorithm works over any module with inner product (aka a Hilbert Module).

# Given: Set of vectors V.
# Returns: A set of mutually orthogonal vectors spanning 
# the same subspace as the vectors in V.

U := Ø
k := 1

while V ≠ Ø do
  poll vector v from V
  w := kv

  for (u,m) in U do
    w -= m〈v,u〉u
  
  # Optional: Divide all entries in w by gcd of entries

  if V ≠ Ø do
    n :=〈w,w〉
    for (u,m) in U do
      m *= n
    Put (w,k) in U
    k *= n
  else do
    # No more iterations after this one
    Put (w,k) in U

return first coordinates of elements in U

When used on integers, the entries in the vectors grow very fast, but this may be avoided by dividing \(w\) by the greatest common divisor of the entries.

DIY preamp for Leslie 760

I have recently bought a used Leslie 760 amplifier, but unfortunately it came without the pedal used to change the speed of the rotator and which also acts as preamp. A used pedal and the necessary cables is priced at about €400 so I decided to build one myself.

Luckily, some nice people has posted scanned versions of the old manuals for Leslie amps and put them online: http://www.captain-foldback.com/Leslie_sub/leslie_manuals.htm.

The amplifier does not have a normal jack-plug for connection with an instrument but has instead a 9-pole plug. At first glance, this seems a bit confusing, but it is quite simple: Pole 1 is ground and pole 2 is sound input. The rotator is activated by grounding pole 6 (slow) or pole 7 (fast).

With this information it was easy to build everything I needed: I built a small box with room for two jack plugs to be put on the amp. The first plug is mono for instrument connection, and is attached to pole 1 and 2. The other plug is for stereo which is connected to pole 6 and 7 (and ground). At the other end of this stereo cable I attached a switch pedal with two buttons: One for switching between grounding the two poles of the stereo jack and another for switching the connection on/off. I got everything for about €25 on musik-ding.de.

I still needed a preamp and found one by the brand ‘Art’ at 4Sound.dk for €35, so for about 60€ I got everything I needed to run the amp.

You are more than welcome to write me at mail@jonaslindstrom.dk if you have any questions.