Wednesday, July 06, 2011

Nosewheel Roulette Spreadsheet

I have completed the nosewheel roulette flight and computed the results, but just to keep things both geeky and suspenseful around here, I will first present my computations for debugging and critique.

I filled a spreadsheet (Open Office - I told you we were going to be geeky) column (column A) with the numbers 0 to 128. (Yes, the numbers should only go to 127, but someone picked 128. Because a wheel is round, that wraps him back to zero, but I didn't put him in the zero box because it was a test of my algorithm having it there).

In the next column (B) of the spreadsheet I put an x next to all the numbers that were spoken for by someone, based on the posted comments. In the next column I put something else I ended up ignoring and in the final column (D), I calculated the distance each guess was from the actual by taking the lesser of:
- the difference between the guess and the answer
AND
- 128 minus the difference between the guess and the answer.

In other words, I measured the shorter distance around the wheel between your guess and the actual final position. If the difference between your guess and the answer was greater than 64, you're getting closer to the answer round the other way.

In the spreadsheet this formula was:
=IF(B4="x";MIN(ABS(F$3-A4);128-(ABS(F$3-A4))))
(There's probably a neater way to do that with modular arithmetic, but I was watching TV at the time and my brain kept resetting).

In the formula, B is the column that has an x to indicate that the number is spoken for, F$3 is the measured answer, and A4 is the guess in row 4. I copied this formula down the whole column (the spreadshhet automatically increments A4 for each row). And then finally I sorted the spreadsheet row-by-row on value in column D, the result of that formula.

I'll leave this overnight for you to find any glaring errors or controversies, before I present the results, your SunglassesShop.com sunglasses winners, and the booby towel designee.

11 comments:

sean said...

Just re-created your spreadsheet and it works okay for me.

However, I have added an additional column:
=IF(D1=MIN(D:D),"WINNER",IF(D1=MAX(D:D),"BOOBY",""))
(please excuse the excel formatting... doing this at work ;) )

Sarah said...

Ooooh I can't wait. Somehow, I am guessing the "booby" prize is aptly named.

Michael5000 said...

I'm feeling the geeky more than the suspenseful, but as you know that's just fine with me.

Here's what's troubling me: why 128 and not 360? Canadian Rules Degrees?

Michael5000 said...

Oh, I've got it. It would all make sense if I read things in chronological order.

Chris Thompson said...

It feels a little bit like Oscar night!

Math before coffee. Bad idea. I hope someone is on hand to certify the results. I would hate to think that I would lose out on the bobby prize because of a math error! :-)

Sean? Show off! But if you really wanted to bedazzle us you would have made the "BOOBY" field blink on and off.

leisuresuitwally said...

Just to clarify: Did the reader who picked 128 get assigned the 0 spot (essentially there are only 127 possible choices)? If that's the case, how will you choose the winner of the bobby prize with two equidistant integers from the winning number?

leisuresuitwally said...

*booby

Aviatrix said...

In the case of two people picking the same number, by any method, the person whose comment appears first has priority.

leisuresuitwally said...

The point is now moot, but there are two booby prize numbers (a circle with an odd number of integers).
Great contest. Did you get a new camera?

Aviatrix said...

The camera is borrowed. Mine is still at the repair place. And 0 through 127 makes an even number, doesn't it?

leisuresuitwally said...

Facepalm