Daily Dose of Excel

Next month is another Excel User Conference. If you’re near Cambridge, England, don’t miss it.

Conference Overview
Dates: November 28 - December 1, 2007
Where: Crowne Plaza, Downing Street
Schedule

In New Charting Utility - Box and Whisker Charts I introduced a utility for making simple box and whisker charts. In the intervening 14 months I have made a number of enhancements to this utility, including the ability to display outliers like the big expensive stats packages and a number of additional formatting options, and I’ve eliminated some bugs and enabled the utility to run without crashing in non-English versions of Excel (the utility uses English labels in all versions, but at least it doesn’t crash).

The Box and Whisker Charts utility is designed to work in Excel versions 2000, 2002, and 2003. It has not been tested in any Macintosh version or in Excel 97, and because of the older version of VBA in these products, the utility is not expected to run. The utility has only been lightly tested in Excel 2007, but it seems to work. This Box and Whisker Charts utility should be considered a beta version, and it is available at no cost in exchange for useful feedback.

The next version will most likely be incorporated into a commercial Advanced Charting utility. In addition to the features shown here, it will allow charting from computed statistics (the utility now uses the raw data as its input), and it will allow VBA programmers to call its methods from other VBA procedures.

The Box and Whisker Charts dialog is shown below:

Typical output, in the form of a vertically oriented chart and a table, is shown here:

The chart is also available horizontally oriented:

There are three styles available: Box and Whisker Quartiles, Four-Box Quartiles, and Box and Whisker with Outliers:

The utility can be downloaded from Box and Whisker Plots in the form of a zipped exe installation file. This page has instructions for installation and use. Any comments and suggestions are appreciated.

I have a few add-ins and files for download on my site, one of which I have recently been working quite a bit.

It’s my New Book Navigator addin and like the name says it helps you navigate around a workbook, as well as select, deselect and highlight cells.

The “New” part is to distinguish it from the previous version. Anyway, it’s evolved into a pretty good add-in over time (my opinion) and it does a lot of things you might find come in handy.

One of those things is what I call Quick Filter. It’s not a filter in the ordinary sense but unlike Excel’s inbuilt filters, it allows you to find and go to cells in both rows or columns, you can even use it to go to hidden cells without the need to unhide them.

Another thing is that you can see what range is selected at any time and copy that range if desired to the clipboard. The range details can also changed to show as Absolute or Relative, A1 or R1C1 references. The default setting is to update the toolbar as you select ranges automatically but you set this to Manual in the Options settings if you want to avoid the screen from flickering when running code. Details of this and all of the other features are described in both the read me files and Help files.

My download page is here. I hope you find New Book Navigator and my other stuff useful.

A couple of weeks ago I spent some time in the bible belt attending football games. Next week I’ll be in Bush country (Austin, TX) for yet another football road trip. One of the more exciting aspects of these road trips is settling up on expenses when you get back. Depending on the group, this can take between six days and six months. Here’s a spreadsheet I use to do it:

The formula in E3:I11 is

=((LEN($D3)-LEN(SUBSTITUTE($D3,E$2&";","")))/LEN($D3)*$B3)-IF($C3=E$2,$B3,0)

The first part determines how many times a certain person’s initials appear in the ‘Paid For’ section and takes a proportionate share of the money. The second part subtracts the total expense if this person is the person who paid the bill. For it to work, you have to use the same number of characters for every person, which is why initials works so well. And don’t forget to include that pesky semicolon, particularly after the last guy in the list.

The Hotel1 expense demonstrates some of the flexibility. Note that BD stayed in the room two nights, while TO and FS stayed all three nights. By repeating their initials, each pays his share proportionate to the number of nights he stayed.

I’m sure you could get rid of that $1 rounding error, but it’s not worth the effort. Of course the initials, expenses, and amounts have been changed to protect the guilty, but don’t go thinking that those initials are random.

Amy asks

I want to count every entry in a column (including duplicate entries) for all entries except the repeat (“) sign

Note that there are three double-quotes after the not-equal sign. To include a double-quote inside a double-quoted string, you use two double-quotes right next to each other. It’s similar in VBA. Here’s another way:

=COUNTIF(A1:A7,”<>” & CHAR(34))

If you’d like to comment on the change I’ve made to the Recent Comments section of the side bar, this is where you’d do that.

I got a couple of emails over the last year asking for a return to showing which posts had recent comments rather than a list of recent comments. The argument goes that if I get 20 comments on one post, no other posts are shown. I can respect that reasoning.

I changed the plug-in that I use to display recent comments when I changed the theme of the site. It doesn’t have that same option, but it does have a ‘group comments by post’ option. So I chose that option and chose to show a max of 1 comment per post. So now you get to see the post title, whomever posted the last comment, and the date posted. Should I change that to time? Or both date and time?

Also I increase the number of recent comments to show from 20 to 30. I’m not sure how this works. If the 30 most recent comments are on one post, would it only show that one? As I write this, it’s showing 18 posts. I don’t know how it comes up with that.

Personally, I think it’s hard to read. My formatting options are somewhat limited though.

David wants to know why the WMP control won't start playing right away. I'll be damned if I know. Maybe you can tell us. First, show the Control Toolbox and click on More Controls. Find the Windows Media Player control and put it on Sheet1. Next, put these subs in a standard module:

Change the URL line to point to a file on your computer. If you run the first sub, the WMP control won't start playing for five seconds. Oddly, it starts playing before you dismiss the message box. So it's not that it's waiting until the end of the sub.

The second sub, just_play, starts playing immediately. It doesn't wait for Application.Wait.

What the heck is going on here?

Hi everyone,

I am busy building my very first COM addin for Excel and I've now come to the stage that I need some beta testers.

Who would be willing to run some tests on my new "Excel Formula Reference Auditing Utility" (see screenshot below)?

If interested, send me an email:
info@jkp-ads.com

What's in it for you? a free copy of the tool once the beta is finished.

###EDIT Oct 29, 2007###
I'd like to thank everyone who has volunteered for beta testing. For now, I have sufficient people doing testing, so the subscription is closed.

Regards,

Jan Karel Pieterse
www.jkp-ads.com