Daily Dose of Excel

Given the problem I have with images and blog posts, I’m trying something new. I uploaded the content of the post to my website and am using an iframe to post it here. Yes, the UI experience is somewhat different than scrolling the entire browser window but hopefully it is something most people will be OK with. And, yes, I can adjust both the height and the width of the iframe.

This approach has another benefit in that one can read the entire post on the home page itself.

We were trying to figure out the odds on keno the other day. There’s something about statistics that doesn’t create appropriately strong synapses in my brain. I got an A in stats in college, so I know I can retain the information long enough to be tested on it. But put me in a bar and make me figure out odds on craps or blackjack or keno and I’m lost. Thank goodness for the internet.

The formula for determining pick/catch odds at keno is

=COMBIN(20,Caught)*COMBIN(80-20,Picked-Caught)/COMBIN(80,Picked)

Keno is a game where to 20 numbers are selected out of 80. You can pick numbers that you think will show up in the 20. The number that actually do are “caught”. What’s the best play? I start with a matrix of picks and catches and the odds of each.

Then I create a matrix of payouts for all the cominations

Finally I multiply the top table by the bottom table. This is my expected payout for $1 bet.

I sum up all the expected payouts for a pick and

Let’s set aside the fact that the best play is not to play. If you don’t play, $1 yields $1. The next best option is picking one number. If you pick-1 long enough, you’ll only lose $.25 for every $1 bet. Pick-20 is so bad, in part, because our local keno jobber has a maximum payout of $50,000. If you pick 20 and catch 20 you get $50,0000. If you pick 15 and catch 15, it’s a $50,000 payout. With the cap, longer odds don’t pay. If you pick-8 long enough, you’ll lose $.44 for every $1 bet.

I put this to the test last Friday night by playing five $1 pick-1 games. I won three of them for a net profit of $4. I’m sure you’ll agree that five games isn’t statistically significant. And to be honest, I don’t need to “prove” the math; I think it stands on its own. But in the interest of science, I’m going to the local pizza and beer merchant to play 80 $.25 games of pick-1. I should lose $5.

You can download kenoodds.zip.

Getting data from an Excel sheet into an array is usually best accomplished with a statement that looks something like this:

I thought it would be keen to fill an array from a filtered list, so I coded

on this list

The SpecialCells returns a range of only those cells that are visible, i.e. unfiltered in this case. The problem, it turns out, is that this method doesn’t work with noncontiguous ranges and that’s just what SpecialCells returns. I put the Stop in there so I could check the Locals Window.

It filled from the first Area of the range, then stopped. I confirmed that it was the lack of continutity of the range with this code

which returned similar results. So I’m stuck iterating through the range, I guess. But then my array is backward; column, row instead row, column because I can’t change the first element of an array with Redim Preserve.

Is there a better way?

Mike Alexander tells me that I have to sell, so sell I shall.

First, the pretty:

Microsoft MVPs Jon Peltier (Peltier Technical Services) and Mike Alexander (DataPig Technologies) are joining together again to bring you our acclaimed Excel Dashboard and Visualization Bootcamp! This 3-day boot camp is designed for Excel users who need to more effectively synthesize data into meaningful dashboards, charts, and visualizations. The topics presented during this boot camp will introduce you to advanced techniques that will help you build and manage better reporting mechanisms. Going beyond simple tables and charts, you will learn to: * Synthesize data in meaningful views with advanced charting techniques * Create reports and dashboards that communicate and get noticed * Create interactive reporting mechanisms * Implement macro-charged reporting * Automate the creation of PowerPoint slides directly from Excel * Integrate external data into your reports

And for those of us who love data and don’t need pretty pictures, the plain:

Microsoft MVPs Dick Kusleika (Daily Dose of Excel) and Mike Alexander (DataPig Technologies) bring you our first ever Excel and Access Power User Workshop! This 3-day workshop is designed for power users who what to expand their skill-set and get more out of Excel and Access. During this workshop, you’ll be introduced to a wide array of tips and techniques that will muscle up your skills in Data Reporting, Automation, and Application Development. The topics presented during this 3-day workshop will help you go beyond basic spreadsheets and databases, to to robust professional-grade solutions. Learn how to: * Move Data between Excel and Access using MSQuery and SQL * Use ADO Scripting to build robust data entry models in Excel * Automate Excel from Access * Run Access Processes from Excel * Automate Outlook Interactions * Building client-side solutions that use SQL server as the back end

And now the closer:

Sign up before April 1st and save $200 on your registration. On April 1st, prices return to $800.

OK, I feel sufficiently dirty now. But these are going to great workshops, so go sign up now!

Someone asked if anyone automates Office applications any more. That struck me as kind of funny. If I had to guess, I would guess that there are more lines of VBA code (poorly written or not) that automate Office applications than all the COBOL, VB6, PHP, and any other language you can think of doing anything. I don’t have any evidence of that. It’s in the Outlook-is-the-largest-data-store vein. Because there are so many Excel, Word, Access, etc documents out there, I think all the code in them would add up to a bunch. What do you think?

Part 1: http://www.dailydoseofexcel.com/archives/2010/02/26/powerpivot-part-1-of-4/

Part 2: http://www.dailydoseofexcel.com/archives/2010/02/27/powerpivot-part-2-of-4-prepping-the-census-data/

As mentioned in the Part 1, I had developed this technique to conditionally color shapes some time ago. Based on customer feedback and my own experience I made changes to simplify the various connections required to make it all work.

Also, documenting the technique was easier to do with a webpage for two reasons: 1) the length of the post, and 2) I find the UI for a blog post somewhat cumbersome for managing images.

The updated solution is at http://www.tushar-mehta.com/publish_train/xl_vba_cases/0301-dashboard-conditional_shape_colors.htm

In Part 4, the last part of this multi-part post, I will look at using PowerPivot from within Excel to analyze the 18 million row data set generated in Part 2 and on integrating the result with the Conditional Shape Color solution of this post.

I’ve recently learned some things about Web Queries that I’d like to share with you. But first a little background. You create a Web Query in Excel 2007 by clicking From Web on the Data tab. This launches a browser within Excel through which you navigate to a web page and select a “table”. Tables on web pages can be a few different things. I think the common “tables” are html tables and html div tags.

Lesson 1: The web browser Excel uses is some variant of Internet Explorer. If you don’t use IE, and I don’t, then you haven’t bothered to set the home page. In that case, the home page will be msn.com and you might see errors like this:

Awesome. Why did I ever stop using IE? If I open IE8, set my home page to google.com (or anything else), then the errors go away. Bonus April Fools day tip: Find a co-worker who uses Firefox and set his IE home page to hawtness.com. It’s marginally NSFW, so it will be funny when he does a web query, but he won’t get fired (probably).

Lesson 2: If you try to refresh a Web Query for a web page that requires you to log in, it won’t work (sometimes). You’ll get

This Web query returned no data. To modify the query, click OK, click the name of the external data range in the name box on the formula bar, and then click Edit Query on the External Data toolbar.

I have a workbook with a couple dozen web queries all pointing to the same site. If I ‘edit’ one of the queries, Excel’s instance of IE remembers that I have logged in (cookies maybe? that aren’t shared with normal IE?) and I can refresh all the remaining queries without error. So I created a sheet near the beginning called ‘Credentials’ where I have a Web Query that brings in a small table near the top of the home page. I ‘edit’ that query to log in, but I don’t have to navigate around much to bring it in. Then I run my code which updates all the URLs and refreshes all the queries. The poor man’s IE automation, I guess.

I have a need to verify some user input is hexadecimal (0-9 and A-F).

With help from this Mr. Excel thread. So where did &H come from? Are there more like it?