Puzzles, poker and pain

[chris]

1. Tomorrow sees the US Puzzle Championship, used as the qualifying test to select the national teams for the World Puzzle Championships in the countries of most, though certainly not all, of the people reading this. Theoretically I think you were meant to have registered by yesterday - whoops - but I registered yesterday and was still directed to the puzzles. (But perhaps I'll be disqualified for late registration! Who knows?) This year's puzzles look really good; I haven't tried the qualifier for several years, but this year's look fun. They also look accessible; I don't know how many I'll be able to finish, but I feel like I have a shot at most of them. (The last few, which will go completely over my head, look really inspired.) Threepeating US champ motris comments, as does test compiler Nick Baxter.

I'll be going in to the puzzles tomorrow "cold", but this test looks like it has a lot more to offer to more modest solvers - like me! - than previous years' tests do. If you've ever been attracted to the thought of taking part, this year looks like a really good one to try, even if it's your first one. Let Really Smart Guys, a lovely near-live blog written at the 2008 World Puzzle Championship, inspire you! Conversely, if you're frustrated by the annual puzzle championship schedule for national-class solvers apparently being one event long, the monthly-ish Oguz Atay Puzzle Contest is similarly very fine; I enjoyed stinking the place up in its fourth edition.

2. The World Series of Poker is in progress at the moment; in fact, it's about half-way through. Numbers are similar to those from last year; some tournaments are attracting more players than last year, some slightly fewer. My gut feeling is that it bodes well for the main event; while I haven't seen anyone quote an over/under for entrance figures and I'm not sure how the online qualifier numbers compare to last year's, I'd guess at about 7,000 - a little more than last year's 6,844 but below 2006's 8,773. The big story so far is that Phil Ivey has won two tournaments in the first half of the event; Brock Parker won two short-handed ("6-max") tournaments in quick succession and Ville Wahlbeck has impressed by so far taking first, second and third places in three of the five $10,000-buyin events he has so far entered.

3. Many people have observed the phonetic similarity of the name Johnny Marr, who plays guitar (for the Smiths, as it happens), to the French phrase "j'en ai marre", often translated "I'm fed up". However, "j'en ai marre" is just a sentence fragment; you would use it in the context "j'en ai marre de ((quelque chose))", or "I'm fed up with ((something))". There is a lovely bit of British English slang, "mardy", which could be translated as "fed up" in a similar way. (A BBC h2g2 author has more.) Accordingly, it's got me wondering whether the phonetically similar "marre de" and "mardy" might have some sort of linguistic link. Etymology or coincidence? (Or, alternately, perhaps someone doesn't like Tuesdays...)

4. Here is an Excel question. Suppose I have a table like so:

Day	Rain	Temp
Mon	14	8
Tue	19	10
Wed	12	10
Thu	22	13
Fri	11	12

and I'm looking to try to find the average Temp on days when there was more than 13 units of Rain. How would you do this? My approach, which doesn't seem to work (in Excel 2000 on a PC), is to create another column at the right-hand end where the cell is empty if there was no more than 13 units of Rain or contains the Temp value if there were more than 13 units of rain, like so:

Day	Rain	Temp	AltTemp
Mon	14	8	8
Tue	19	10	10
Wed	12	10	(empty cell)
Thu	22	13	13
Fri	11	12	(empty cell)

...whereby taking an average of the new right-hand column will give me the answer I need. Furthermore, should the rain value on Tuesday turn out to have been 9 instead of 19, changing the 19 to 9 should change the AltTemp from 10 to (empty cell), and so the average AltTemp should change from being calculated based on three figures to being based on only two.

Trouble is, I can't come up with a function I want to get this. I have tried something like =IF(B2>13,C2,#N/A) or =IF(B2>13,C2,#NULL!) - and so on for the remaining rows - but the average of a series which includes some numbers and some #N/As (or some #NULL!s) is #N/A, where I just want to skip over the cells with the #N/As in when performing the average. Can't help feeling this is going to be a really easy question for someone who knows better than me, but I've been working on this on the night shifts so haven't had anyone to ask and I haven't been able to work it out from the help. Accordingly, is it clear what I want - and, if so, how do I do it, please?

Comments

[(Anonymous)]

And of course the singer, Marre Aussi, though it's allegedly ex post facto and not a thing on purpose at all. What I'd do, because I'm lazy, is make a second copy of the entire table, sort it by the rain column, crop the ones above 13 off, and then do an average on that. Of course that's substantially less useful if your table is updating daily than if it's a static data set!

jdcxxx

did someone say Excel?

[althea_valara]

Assuming your table is in A1:C6... if I do =AVERAGE(B2,B3,B5) I get 18.33. So that's the number we need to get. The formula that will do the trick is this:

=SUMIF(B2:B6,">13")/COUNTIF(B2:B6,">13")

That gives you 18.33 as well. :)

I'm looking forward to the puzzles tomorrow! I might practice some tonight, but knowing me, probably not.

Re: did someone say Excel?

[(Anonymous)]

And I just realised you wanted the average temp, not the average rainfall. Oops. This is easy, too; you just need to add one thing to your formula.

=SUMIF(B2:B6,">13",C2:C6)/COUNTIF(B2:B6,">13")

There you go. :)

I am a BIG BIG BIG fan of SUMIF, so it's really worth learning.

Re: did someone say Excel?

[althea_valara]

That was me, obviously. Forgot to check the box to login.

Since I'm making another comment anyway: since I work with spreadsheets for a living, you're always free to shoot me an email if you get stuck. I can usually figure out the answer.

Re: did someone say Excel?

[chris]

Thank you, that's very kind of you! I appreciate it.

Suppose I have my list of mixed numbers and #N/As in column D, and I have produced them through some more complicated function or another. Would the averaging function then be something like

=SUMIF(D2:D6,ISNUMBER)/COUNTIF(D2:D6,ISNUMBER)

or similar? *checks* No, that doesn't work. Can you see why I would expect it to work, though?

OK, let's simplify. Suppose I have a list that is just

6
2
-3
#N/A
4
7
#N/A
2

and I want to find the sum of the numbers therein.
=SUMIF(A1:A8,ISNUMBER) gives zero.
=SUMIF(A1:A8,">0") gives a numerical answer, but gives me the wrong answer because it doesn't take the -3 into account.
=SUMIF(A1:A8,"<>0") gives #N/A.
=SUMIF(A1:A8,"!=0") gives zero.
=SUMIF(A1:A8,">0")+SUMIF(A1:A8,"<0") works, but is ungainly.

By extension, we can get the average of the numbers in the list from =(SUMIF(A1:A8,">0")+SUMIF(A1:A8,"<0"))/(COUNTIF(A1:A8,">0")+COUNTIF(A1:A8,"<0")) - but is there a neater way of doing it?

Next question. I would like to run the PERCENTILE function on a range of data, like the mixture of numbers and #N/As above, to work out what the (e.g.) 74th percentile of the list is. Now these #N/A cells don't actually have to be #N/As, but they need to be things that clearly aren't going to be treated as numbers by the PERCENTILE funtion, aren't going to make the PERCENTILE function choke and crucially can be produced as the result of another function such as IF. Is there is a cunning way to do that, please?

Re: did someone say Excel?

[althea_valara]

Suppose I have my list of mixed numbers and #N/As in column D

Excel treats errors such as #N/A and #DIV/0! specially. It's like Excel is thinking, "There should be a valid number here, but we don't know what it is/can't compute it, and thus I can't sum these together." Go figure. Happily, Excel will ignore alphabetic characters in a list of numbers to sum. So rather than returning #N/A or #NULL, I return "" instead. Or you can return "nope" or "FRAK" or anything alpha and it will just skip over it.

By extension, we can get the average of the numbers in the list from =(SUMIF(A1:A8,">0")+SUMIF(A1:A8,"<0"))/(COUNTIF(A1:A8,">0")+COUNTIF(A1:A8,"<0")) - but is there a neater way of doing it?

When it gets that complicated, I'd start considering pivot tables. Not sure if you know how to do them or not, but I'll explain anyway in case you or others don't know.

First, let's alter our data table somewhat.

DAY RAIN TEMP
Mon 14 8
Tue 19 10
Wed 12 10
Mon 22 13
Tue 11 12
Wed 8 11
Mon 13 9
Tue 20 11
Wed 14 12
Mon 11 10
Tue 10 10
Wed 17 11

And let's say you want the average temp on Wednesdays when the rain fall is over 13.

1) First, select your table by highlighting cells A1:C13. Then go to Data->PivotTable and PivotChart Report.
2) On the first page, leave both default options ("Microsoft Excel list or database" & "PivotTable") and click Next.
3) The next page lets us choose our range. Since we already did this by highlighting the data first, we can just click Next.
4) The final page lets you choose where to put the table. I usually leave it on New Worksheet. But before you click Next...
5) Click on Layout. This lets you set up the table:
5a)Drag Day to the PAGE area.
5b)Drag Rain to the ROW area.
5c)Drag Temp to the DATA area.
5d)Excel defaults to doing Sums on Pivots. We want Averages. So double click on the "Sum of Temp" field and change it to Average. Then click OK.
6) Click OK and Finish to complete the table.


You'll see this, which gives you the average temp per units of rain:


We want to limit it to Wednesday. Click the dropdown next to Day and choose Wed. Now you see this:



And finally we want to limit it to units of rain over 13. Click the dropdown next to Rain. Uncheck items that are over 13. You have this:




The grand total of 11.5 is your average temp on Wednesdays when rainfall is over 13. :)

For your PERCENTILE function, like I said up above, returning an alphabetic result from your IF statement should work.

Re: did someone say Excel?

[chris]

Happily, Excel will ignore alphabetic characters in a list of numbers to sum. So rather than returning #N/A or #NULL, I return "" instead.

That exactly does what I want it to do for both AVERAGE and PERCENTILE, and may do it for other things as well. Thank you very much! :-)

I also appreciate the Pivot Table explanation, which is something I had not looked into. More investigation here required, I reckon.

[oldbloke]

I see you've already been alerted to the glory of SUMIF and COUNTIF, so I shall say no more on that.

Re the poker, though, Vicky Coren's latest Grauniad article points out that enough millions of people play the game that it really ought to get more coverage in the sports sections, and goes on to do a 'which Brits medalled' thing. We have a decent tally so far!

Yes, I verbed 'medal': blame Mario Kart. Please somebody tell me the trick to getting medals for the Banana Cup. I can win all 4 races and still not even get a bronze! Like, WTF???

The grammar of made--up words

[chris]

Verbing weirds language. At the end of para one, I verbed - nay, gerunded - "threepeat", though I felt suitably dirty about it.

While it was a concise way to convey what I meant, I'm not even sure it was a clear way to do so. After all, he has already threepeated, past tense, and should he win again then he will have fourpeated. He has threepeated and he may yet fourpeat; that's not really the same as threepeating, is it?

Perhaps I should have gone with "three-wins-on-the-trot-and-counting" after all!

[synecdochic]

At the WSOP, it's not "medaling", it's "braceleting". *g*

[chris]

Now here's a thing: the $2,500 eight-game mixed event was braceleted by hgfalling, a somewhat active LJ user. I've been wondering whether LJ had any bracelet winners; if it didn't before (I never researched it...) then it certainly does now. I wonder whether DW has any bracelet owners yet - and, if not, why not? ;-)

[anatsuno]

Um, no, J'en ai marre can very easily be used on its own too. This is usually the sentence reserved for semi-explosive 'I can't take it anymore!' type situations, with either a precise or a vague, englobing 'it'. (note that it is familiar but not vulgar language - contains no swear word'. So, I could just suddenly kick the machine that was vexing me and cry out "J'en ai MARRE!" :D

[chris]

I see! That blows an attractive but evidently incorrect theory out of the water. I'll take #3 as "coincidence", in that case. :-)

[ringbark]

Well, in col D, we put =IF(B1>13,C1,"NO")
and then at the bottom, it's =AVERAGE(D1:D5)
The key is that text is ignored, but errors are not.

[chris]

Yep, that works for AVERAGE and also for PERCENTILE as well, which is what my follow-on question was going to be. Thank you very much!

[althea_valara]

Tried: Wolves & Sheeps in Fences, Magic Puzzle'Rs, Sudoku (the first one), and Missing Operation Ken-Ken

Completed: Battleships, Switch Cheese, Writer's Block, and at the very last minute, 2-3 Maze.

So, 45 points. That seems to be my level, because I always get around 50 points.

I need to get faster at Battleships. It's an easy puzzle, but I am slow at it. I missed the word search this year--it's a favorite and I'm good at it. Wish I would have had time for Fences, because that's one of my favorite puzzles (though I haven't done them in years). I had a lot of fun with the 2-3 maze.

I always wish I could beat previous year's scores, but I kind of knew I wouldn't this year, so I just decided to have fun with it. Which I did. :) Magic Puzzle'Rs looks really fun, so I think I'll try to finish that one.

[chris]

Officially, I think I score 69. Unofficially... well, about an hour through, I had to go and pick up some medicine for Meg. This took 45 minutes as I had to try two different stores. If you're prepared to give me those 45 minutes back at the end, I'm going to claim 111 - 45 extra points' worth of puzzles, minus 3 minutes for overtime. PS This is wrong, see below

Here's the tale.

1. Printed out puzzles. Huh, my printer does print out back to front, not front to back. (Makes sense, considering which page should end up on top.) Didn't think of that; now I see why they do point out the ideal printing order.
2. Got half-way on the Battleships. Skip it.
3. Writer's Block. +10.
4. Dabble at the sudoku... er, no.
5. Missing ops KenKen: saw a couple of ways in, not too many tricks. I am particularly pleased because I don't think I've actually managed to solve more than one or two KenKens in the past. +10.
6. Triangular skyscrapers; not going to happen.
7. Wrote down some grid numbers for the counting puzzle.
8. Maybe a couple of differences. Remembered I wanted to save these until the end.
9. 2-3 maze: started from the end and worked back. Got some of the way but not very far.
10. Intermission for a mission of mercy.
11. 2-3 maze: continued, worked further back, looked promising, worked from the start and the two joined up. +15.
12. Magic Puzzle"R"s. Surprisingly well-behaved; the letters in there gave a strong start. I put in words until I had about four "R"s left to place, then placed the remaining "R"s, then the rest fit in. Phew! +20.
13. Battleships: still didn't work itself out. Bum, I normally really like Battleships.
14. Sum Thing: I liked this, there is a satisfying way in. Logically deduced the first line. Wasn't sure where to go from there, but guessed right first time and it all worked out. +10.
15. Only a couple of minutes left. Spotted a couple more differences, re-entering my answers after each one. +4 with about 15 seconds' server time left to go.
Into extra time:
16. Co-ordinate Pairs. I was thinking about this one last night, and I reckon I've got an idea for a better way to do this puzzle than the version that was actually used. Tried some fairy chess moves of increasing sizes, lots of rubbing-out, found one that worked. +10.
17. Eminent D'OHmain: got about the left-hand third, think it's right, but am not sure. Not sure where this one is going, really.
18. Sweet Sixteen. After getting Sum Thing, I've decided I'm OK at these addition puzzles. I liked this; it splits up into smaller parts nicely. Once you've worked out the target, you can solve the middle, then it's all a bit like trying to do a magic square, except triangular. Extended the general principle and a good guess worked first time. +15.
19. C Notes. Apparently I'm not that good at these addition puzzles. Couldn't really get started.
20. Lucky Sevens. Spent about 20 minutes on this, playing with combinations of infrequently-appearing letters. At about the fourth attempt, I managed to get about a quarter of the grid down, then looked at the clock and realised I had about two minutes left. Thought "Bollocks, all my timing is unofficial anyway" and just tried the rest of it from there. It all fell into place after that. +20 but -3 for overtime on the extra time.

*checks rules* It's 10 points penalty per late minute, not 1 point penalty? Gulp. Er, my unofficial score isn't 111, it's 84!

Of the remaining puzzles:
1) I'll probably get Battleships tomorrow and not remember what the problem was.
2) Sudoku looks nothing special.
3) Eminent D'OHmain... mmm, if I keep going with this then it might fall into place. But it might not.
4) Switch Cheese: I can probably find some more if I try. I decided trying to squeeze out extra points, even thinking the penalty was one point per minute overtime, not ten points.
5) Corral: going to struggle here, not going to try.
6) Triangular Skyscrapers: hum, can't see a way in.
7) Window Pain: I can probably do this but it's not much fun. This would probably have been a good investment of time during the test.
8) Masyu: no chance.
9) C Notes: not my cup of tea.
10) Yaijin: see Masyu. I really don't get on with loop ones, most of the time.
11) Fences: see above, though this looks clever and could be fun.
12) SuDUOku and beyond: these all look way beyond me!

[malachan]

Just sort by the rain column, then do an average of the temperature column selecting just the cells 13 and over in the rain column!