Tangential Ramblings

Last week, I described how I'd used a Google API to allow a Google Spreadsheet to drive items appearing on a Google Map. I was proud of the work. Rob was less impressed, instead focusing on the fact that the latitude and longitude had to be looked up and input manually based on the postcode or town being mapped. He's never impressed, that boy.

In a comment on the original post, Mercedes Car Finder (a person, it seems) suggested a Google formula, as follows:

=ImportData("http://maps.google.co.uk/maps/geo?output=csv&q="&A1)

where A1 contains the item to be looked up—the postcode or placename. Let's assume the formula is entered into B1.

The CSV file resulting from the URL contains four fields. For "York", the first two numbers are 200 and 4. (I have no idea what these are. Anyone?) The last two (53.957702 and -1.082286) are the latitude and longitude respectively.

So the ImportData function brings back all four values, but only stores the first of these (200) in the cell containing the formula, in this case B1. The subsequent three values are accessed through the formulae

• =CONTINUE(B2,1,2)
• =CONTINUE(B2,1,3)
• =CONTINUE(B2,1,4)

Given that the CSV file returned has a single row of data, these formulae bring back the mysterious 4, 53.957702 and -1.082286 respectively.

Unfortunately, Google doesn't allow you to create latitude in one step through the following syntax:

• =CONTINUE(ImportData("http://maps.google.co.uk/maps/geo?output=csv&q="&A2),1,3)

But nonetheless, if I have a column of postcodes or placenames, all I need is three columns of formulae (the ImportData formula containing the 200, the CONTINUE (3) formula containing the latitude and the CONTINUE (4) formula containing the longitude) to feed the map.

Fabulous. Thanks, Mercedes-selling-person.

If at the start of the season you were to predict the finishing positions of the 20 Premier League teams and then compare your predictions with their actual finishing positions, if you had no previous knowledge of teams' performance, what would be your expected margin of error? The measure here is the sum of the absolute differences between teams' predicted and actual positions. So if every team was out by one (either over- or under-predicted), then it would be 20.

I'm not sure whether it's easy to create a formula for n teams, but a random Excel trial of 26,126 such prediction sets yielded a minimum difference of 56 (an average discrepancy of 2.8 positions per team), a maximum of 192 (9.6 positions per team) and an average of 131 (6.6).

My brother's performance in his work competition based on the teams' current positions is 80, 0.4% of my random trials bettering this. Is 80 good? And where will he be in May?

I tidied up the calendar spreadsheet last night while awaiting for our Ocado delivery. Plenty of time available, given they arrived over an hour later than their billed time. A tiny bit of the logic was tidied up, and I added a year-view. Overall, it's now the dog's bollocks, if I may say so.

Below is the logic I used, in English rather than Excel.

The first thing to do was to confirm the weekday of the first day of the year. So I took the value from concatenated the year on to "01/01/", and calculated its weekday from there.

Next, I created a table showing each of the month-lengths, eleven of them hardcoded, February's using the slightly cumbersome formula for working out whether a year leaps: divisible by 400 is; divisible by 100 is not; divisible by 4 is; otherwise, not. The table also contains the weekday of the first of each month, calculated as the mod base seven of the previous month's start date plus that month's length less one.

On the calendar sheet itself, the first row of January's dates was created by comparing the number of the day of the week each cell represented with the day the month started on to establish whether to show a blank (the month hasn't yet started) or a number, either a 1 for the first day, or the previous cell plus 1 for subsequent days.

The rest of the rows' entries simply compare the previous entry with the number of days in that month. If the two are equal, then that and subsequent entries display blanks; if not, then it's time to increment the previous entry by one.

Each month has six rows of entries to accommodate the rare months that start late in the week and drip a day or two into week six.

And the cells' formats are dependent on their contents and the weekday. Blank cells have no borders; those representing weekends are shaded with borders; those representing weekdays are clear with borders.

Combining so many beautiful elements of Excel into a spreadsheet so useful was rewarding to say the least. Formula-driven conditional formatting, lookups, ifs, weekdays, moduli. Happy days.

If I open two instances of Excel 2007 and copy a range from one to another using paste special, the destination doesn't recognise the copied range as an Excel object. Instead of offering paste values, paste formulas (it's formulae, goddamit), transpose and the like, it offers me Microsoft Office 2003 Worksheet Object, Picture (Enhanced Metafile), Bitmap etc. Copying between two workbooks within the same instance behaves correctly. Bizarre, and rather annoying.

Today I created one of the most beautiful spreadsheets I've ever created. It should have universal appeal, is simple in concept but more cumbersome than you might imagine to implement.

It's a calendar.

It shows the days of any year you choose (from 1900 to 9999) in the traditional diary-style monthly view, with days of the weeks as columns, each week appearing below the next. Each month prints out on a different page and it takes into account leap-years, including the less well-known 100-year and 400-year rules.

Here are the 2008 version (53Kb) and the 1997–2003 version (oddly, 212Kb). On the Annual sheet, enter your year of choice. And your more detailed calendar will appear on the Calendar sheet.

Oh and by the way, 31 December 9999 will be a Friday. And what a Friday night it will be.

I've recently been upgraded to Vista at work. But the default setting is to save Office files in 1997–2003 mode to ensure that unupgraded colleagues can access them.

Given the richer functionality offered by Office 2007, the usual warning I'm presented with on saving a spreadsheet in the format of its predecessor is "Minor less of fidelity." Such a glorified message to convey the fact that your spreadsheet might look a bit shit if opened in an earlier version of Excel.

Thinking about it, the Ribbons in Excel 2007 are not that revolutionary. They're horizontal, visual representations of what were the dropdowns that appeared from the menu bars; re-organised allegedly to be more logically grouped. For some reason, I felt that they were marketed as something more than that in the run-up to the launch of Vista and Office 2007.

It's the re-organisation that troubles me, possibly because the grouping is still not fully logical, possibly because it's different from the File | Edit | View | Insert | Format | Tools | Data | Window grouping of its predecessor. I read a very short article on Daily Dose of Excel recently that said nothing more than if you can't find what you're looking for in Excel 2007, you'll find it on the Insert menu.

Apologies: I'm rambling, but I'm allowed given the title of the blog. (I've never really considered after naming the blog whether it lives up to its name. Another segue.) Basically, I still don't quite know where to look for stuff in Excel 2007. My preconceptions are obviously still there, formed by 20 years in Lotus 1–2–3 and pre-2007 versions of Excel. But when I can't immediately find something now, I try to ask myself "where would it logically be" and often fail to come up trumps. The Home menu doesn't seem right to me, combining lots of formatting stuff with content movement (Copy/Cut, Paste and its various offspring, insertion and deletion of columns and rows), and sorting and filtering.

To me, it would make more sense to have a Format menu item and an Operate item, the latter to cover the likes of insertion and deletion, clearing, filling, finding, selecting. (Shit! I've just realised that almost nothing in the Edit sub-menu within Home has anything to do with editing. Sort, Filter, Find, Select, Sum, Fill have nothing to do with editing!) All of your filtering and sorting should be firmly in the Data menu item.

And why the fuck PivotTable/PivotChart button is under the Insert menu and not the Data menu beggars belief. (If everything inserted goes in the Insert menu, then why don't Insert Function and Insert Row/Column join the party?)

I like the concept. However I believe there was a set of workshops held by Microsoft (some of the most important workshops in Excel's history) in which Post-Its containing all of Excel's functions were arranged into areas on a whiteboard. But the wrong people turned up. (I didn't get an invite.) So the result is OK, but it's not quite right.

As an aside, I'm wondering whether things should ever appear in more than one location. Or should everything have one and only one home?

BTW, I've held off on writing such a post until a month after starting to use Office 2007, to allow my opinion to mature.

In February, 21 people happened upon my site by searching for the string SUMPRODUCTIF. They would have got to this post, which is surfaced as the top result in Google. Hopefully I'm helping some people, which satisfies me hugely.

There are two big problems with most risks and issues registers:

• They're difficult to read
• They're difficult to maintain.

Let's start with the readability. Usually, they're a lifeless grid in a tiny font with lots of columns and, depending on your project/programme, lots of rows. There is nothing to distinguish between the rows other than the contents of the eight-point type in each of the cells. Risk or issue titles are unnecessarily verbose, and it's often not clear what an item represents by reading it.

To solve this problem, include a Headline column in 14-point bold. Maximum of ten words to give a high-level description of what the row is all about. People will read it and say "Ah, yes. I know that one". And make sure all of the columns are absolutely necessary. Nugatory columns are a pain to fill in, and deflect from the important columns when viewed.

Use 90° text alignment for the short fields like who raised it and assignee to save width for the columns containing important textual information.

Now some pointers to make your registers more manageable.

Don't hive off closed items. Just close them in the status field. It's important that you keep them for posterity and completeness, but use Excel's Custom Views to make sure you don't see them unless you have to.

Have a single register to house both risks and issues. This is a biggie that I can't stress enough (or indeed too much). Although some of the columns for each register are subtly different, there are quite a few that are consistent between the two. Keep two registers and your reference numbers get cumbersome, and cutting and pasting from one to another when risks come to fruition is a right pain.

If a column for risks is sufficiently similar in nature to an issues column, then combine the two. Have three heading rows, one for risks, one for issues and one for a combined view. Again, use Custom Views to hide the two title rows that are redundant for a particular view. If you're showing risks, hide the issues and combined titles, for example.

If columns are being used to determine the Custom View via filtering (e.g. Status: Open; Type: Risk), then hide these two columns in that view—they're redundant. Instead, include a cell above the title row saying "Open risks" that can be displayed for that view. For each view, have a dedicated row above the title rows purely for this function, summarising the view. This can be printed on every sheet through repeated titles. And if you want, you can succeed it with the number of such records.

A few other points worth making.

• Unless you're using some shared web interface, use Excel. Don't consider anything else.
• For the risk or issue titles, word your items as statements of fact. Risks should be phrased as if they've happened. Issues similarly, because they have. "There is no environment available for performance testing," for example
• Keep update summaries brief, and archive old updates into a separate, hidden column in four-point. This is for audit purposes and is rarely referenced. Its font can be increased as and when you need to read it
• By combining your risks and issues, you can have generic reference numbers that support both
• Build Custom Views for: open risks; open issues; open items; all items. You can add more complex views if necessary, such as: imminent risks; high-impact issues; escalated items
• Use conditional formatting to work out how to colour items, but only colour the rating cell and make it narrow

Boring post, but necessary. Comments welcome.

I realised on Thursday that I look up when people mention Excel, as if answering to my name being called.

Slightly worrying.

I have a colleague. For the sake of argument, let's call him Neil. (His real name is Neil.) He sent me an email today asking why the following Excel formula wasn't working.

=IF(F648=1, 10, IF(F648=2, 8, IF(F648=3, 7, IF(F648=4, 6, IF(F648=5, 5, IF(F648=6, 4, IF(F648=7, 3, IF(F648=8, 2, IF(F648=9, 1, 0)))))))))

[Note: I've added spaces after each comma to allow the for inevitable word-wrapping issues.]

My immediate response was that Excel can only cope with eight nested ifs. (The last argument in his email was red, and this was indeed the straw that was breaking the proverbial camel's back.)

I then asked what business problem he was trying to solve. He had a column of data containing values between 0 and 10. And he wanted to invert them, so that 0 became 10, 1 became 9, 5 stayed 5, 8 became 2 etc.

I suggested he instead used the following formula:

=10-F648.

There was a short pause on the other end of the line. Bless.

I've recently discovered the huge power offered by combining the offset function with named ranges in Excel. Allow me to share. No, please.

Offset is quite a new one to me, but it's phenomenally powerful. It allows you to refer to a range of a chosen height and width that is a chosen number of rows and columns away from another range. Its syntax (or one of two possible syntaxes) is:

=offset(range, rows, columns, height, width)

Range is the original range, which only actually needs to be a single-celled range (an amoebic range?) Rows and columns are the respective offsets, while height and width define the height and width of the new range.

So offset(A1,2,3,4,5) will give you a range 4 rows high, 5 rows across that starts 2 rows below and 3 columns to the right of A1 (i.e. D3:H6).

Named ranges are also great. You can reference them in formulae, and if they change in dimensions, all you need to do is change the reference behind the named range, and all of your formulae update accordingly. Fantastic!

But what if you know in advance that your referenced range is going to change? And what if the height and width of the range can be determined based on the contents of a column and row?

So let's combine the two. Let's assume you have a range of data that starts in A2, with column titles in row 1. And let's assume that your columns and rows are contiguous (i.e. no blank rows or columns), and a column title signifies that it should be part of the range, while an entry in column A qualifies that row as being part of the range.

Create a named range (Insert | Name | Define) called my_data. And name the range as follows:

=offset(A2,0,0,counta(A:A)-1,counta(1:1))

(Note, the zeros mean that we're not actually using the offset bit of the function, but that's by the by.)

The range referenced will start in A2, will (if the earlier assumptions hold) end at the bottom-most row which has a value in column A and go as far right as there are columns with a title. The "-1" adjustor is to account for the title in column A, which you don't want to count.

By having a dynamic range, you can add rows and columns to a range that is being looked up in without fear of rows or columns being missed out of the look-up. So a database of contacts can be added to and the new names will be included in any referenced range; and new columns of data can be added with similar confidence.

Combine this with the suggestion in an earlier post to parameterise your lookup offsets, and you're in vlookup heaven.

vlookup is a hugely useful formula in Excel. I'd say that apart from the basic mathematical operators and the if statement, there's nothing that touches it in terms of how often I delve for it in trying to solve a problem.

But it's a very unstable formula. Here's its syntax.

vlookup(needle,haystack,column number to return,exact match?)

The issue comes when the range that you're looking up in is liable to change in structure and size.

The needle is unlikely to ever change, so that's not a problem. Meanwhile, the haystack is likely to be a fixed range, and if it's not, it can be dealt with by using a names range. The column number to return is the weak link.

In the past, I've always hard-coded this, not thinking to do things any differently. But recently, I found a way of making it dynamic.

At the head of the column in which you're looking up, likely in a hidden row, store a value that represents the column number that the column represents in the range. So if your data runs from row 3 to row 100, with column titles in row 2, and the column in which you're looking up is column A, put the following formula in, say, D1:

=column(D3)-column($A$3)+1

In this example, it will return the value 3.

Copy this formula across the relevant section of row 1, and the row can then be hidden.

And in the lookup itself, if you want to bring back column D, use the following formula:

=vlookup(XXX,$A$3:$Z$100,D1,0)

for an exact match.

By doing this, if a column is inserted somewhere between columns A and D, the value of D1 will automatically increase to 4, and your vlookup won't break.

Hope it helps.

I was set a challenge recently, in which the business requirements kept getting more complex. I rose to the challenge, but the final solution was made more complex by the fact that the requirements were iterative as opposed to being defined upfront.

The requirements evolved such:

• Can you find the difference, in hours, between two times in Excel
• Oh, can you exclude weekends
• Oh, and on the weekdays, can you exclude the times that are out of hours (i.e. before 9am, after 5pm doesn't count)

Here's the solution. It works, but because of the way it evolved, I no longer know how it works. So if you have any problems with it, don't come running to me!

Someone at work the other day described Excel as "God's tool". While not being a believer (in God, not Excel. I believe in Excel, a belief called excellentialism), I have to agree.

It's surprising, as this guy was, and indeed is, a techie. Usually, techies and Excel don't mix.

A word of warning. Well, nine actually: never show that you're good at something at work. If you do, you'll get a reputation, and every similar task thereafter will be thrown your way.

I showed an aptitude for relentless and organised chasing of stuff recently. The next time there was a need for similar relentless chasing, it was thrown my way. On this occasion, I threw it straight back whence it came.

Yesterday's Excel pivot-table and graphing task was thrown straight in my direction. I had nowhere to throw it, but luckily it was 15 minutes' work. It was also fun.

I did a pivot table today. Ever since my days with a particularly annoying client in New York, I can't help but say Pivot aloud while clicking the PivotTable from Excel's data menu. It's a hangover from a particular Friends episode, a fabulous outtake of which can be at 8m25s in this video.

Thanks for making me laugh today, annoying client from New York.

A few nice custom Excel formats, some of which I've known for a while, some which are new to me.

• <;;;> Stores a value, but doesn't display it
• <00000> Always stores five digits. So 453 becomes 00453. Useful for US zip codes
• <[Black] General> To hide error messages (e.g. #DIV/0!). If you make your standard font colour the same as the background (usually white), then this will kick in to give non-error cells a black font
• <0;-0;;@> Not sure why this works, but it hides zero values
• [#.???] This will line up your decimal places in a column, and display three decimal places, but only if they're significant. "3.2" will display as "3.2", not "3.200". The only slight issue is that "3" will display as "3."
• < @> Put a bunch of spaces before the @, and these will appear at the beginning of the cell, resulting in a padding
• <#,###"km"> Type in 100, and it will appear as 100km, but you can still use the value to do calculations
• <@*.> This will pad your cell out with dots to the width of the cell. So "Excel" will appear as "Excel............". Might be useful for tables of contents
• <#,###,, "M"> Again, not sure how this works, but it divides the value by a million and displays a trailing "M"
• <[<=2]"Low"* 0;[>=4]"High"* 0;"Average"* 0> This will display the word at the left of the cell, and the value at the right

Go to Format | Cells and click Custom. Then type the bit between the < and the > above to get the desired result.

The beauty about the above is that only change the display format. They don't affect the value stored.

I've had a bit of a traumatic weekend on the iPod sync. front. Before we headed to the US in 2004, I copied all of our music on to my old laptop and then on to our two iPods. Since then, we have gone through four life events:

• Two failed hard-drives (one each)
• Two replacement iPods (one each)

In the meantime, I've downloaded a bunch of music (completely above board through iTunes, I hasten to add), and my wife has done the same. Different tunes of course, but tunes nonetheless.

All of this has meant that our three music collections (what should be the 'master' on my laptop, my iPod and that of my wife) have got out of sync.

Even more frustrating is that in the intervening two and a half years, Gracenote's database entries for old stuff have changed slightly. Typos have been corrected, ands have changed to ampersands, regular parentheses (Disc 1) have become square [Disc 1] and so on. And not as rarely as you might think.

The net outcome of this is that I'm finding it very difficult to reconcile any two music sources. I've got spreadsheets into which I've copied library listings (one tab called "Old iPod", one called "New iPod" etc.) and I've created artificial unique names for each entry, stringing together song title with artist and album name. VLOOKUP shows me the songs that don't match, but it doesn't tell me whether this is because the track's genuinely missing on one source, or whether Cigarettes and Alcohol is now Cigarettes & Alcohol.

Now I'm down to a list of 672 mis-matching tunes (were on old iPod, haven't found their way to new iPod) from 119 CDs, 522 of which come from 50 albums. So I think I'll target these and call it a day.

There are two big issues with iTunes.

Firstly, the DRM stuff is really frustrating. I own the tunes (either through download from iTunes or buying CDs), yet I am not allowed to copy them from one hard disk (the iPod) to another (the 'puter).

And secondly, even if song details aren't necessarily the same throughout their life due to the issues highlighted above, please give them unique reference numbers that stay constant. That way, I can easily and confidently establish the mis-match between two music sources, thus saving a whole day's effort. Amazon uses (invented?) the ASIN (American Standard Identification Number) which sits at the product level. String a three-digit number on to the end of this, and you're sorted.

Thanks to Francis for pointing me to the free online demo. It's certainly worth a look if you're interested in the imminent Office release.

The login process is a bit frustrating. First of all (and in retrospect I should have predicted it), the demo wasn't compatible with Firefox, so I had to switch to IE. Thereafter, it was an unintuitive log on process, but I finally got there.

The demo itself is a Citrix-based browser application, which works really well—no downloads necessary apart from Citrix itself. A remote connection launches a server-side application: Excel, Word, Outlook, whatever you're interested in.

Excel itself has some new functionality, but the majority of the stuff under the bonnet does the same as its predecessors. However there are two significant changes:

• User experience. The ribbon menus work really well, although they'll take some getting used to. They essentially give a more logical and intuitive structure to the things that you can do, but drill down into any one, and you'll find you're back to the same tab-based pop-up window
• Polished features. Quite a few of the features (e.g. table formatting, conditional formatting, cell referencing) have been enhanced and taken to the next level.

The intuition is a funny one. The grouping of features and tasks seems more logical, but given that it's different from what we're used to, it doesn't immediately come across as being intuitive. In Excel of today, the fact that the Find feature is on the Edit menu is bizarre, until you realise that it shares a pop-up with the Replace function. I can't immediately find its equivalent in Excel 12.

If you click Alt, the pop-up shortcuts are a bit freaky, I have to say, but potentially useful. Oh, and I'm happy about one bug that they've fixed. In the Alignment tab of the Formatting pop-up, the Vertical dropdown used to have an element (Top) missing from the top of the list, and you needed to scroll up to get to it. Scroll no longer.

In summary, it's nice, but I don't see this as a step-change from a functionality perspective. Which is probably a good thing. They've taken this opportunity to invest significantly in the user experience, and to polish some of the features that previously had limitations.

I won't be rushing to upgrade, but I'm confident that when I do, it won't be a whole new world.

As for the other applications, Word and PowerPoint look like they've had similar make-overs to Excel, while Outlook merely seems to have an extra entry on the main menu (Business Contact Manager). Oddly, when you launch an email, it gets a ribbon treatment, maybe because of its tie-in to Word.

The demo's certainly worth checking out.

I am.

Microsoft has been doing some user testing as part of its development of Office 2007 (or Office 12); hundreds of thounsands of hours of it by all accounts.

While the new ribbon-based interface is apparently more natural and intuitive, "the people who take longer [to adapt to the new interface] are the die hard users; the expert Excel user." This according to Chris Capossela, Microsoft's Corporate VP on the business side of things.

I'll get my coat. Does anyone know how I can get a test version to try out?

Some time ago, I created a spreadsheet migration tool for a client of mine at the time. I was heavily criticised by my then employers for venturing outside of our agreed scope.

The tool essentially accepts data input into one sheet (each field occupying a column; each record occupying a row), and spits it out in a pre-defined, fixed-width format (padding each field with the necessary number of spaces), ready for a downstream application to feed off its goodness.

It wasn't the most strategic tool ever created, but it worked beautifully. It was heartening to find out yesterday that the same tool is being used to this day to migrate data into the application. I'm sure some of the official deliverables are now shelfware.

I can't remember why, but I set myself a personal challenge to create a spreadsheet that could convert numbers to text.

Here it is. Type in a number between 1 and 999,999 in the red cell and it will write it out in words in the blue. English style, not American, so the ands are included.

As an aside, the lowest positive integer for which the letter a is required is 101 (one hundred and one). In American, it's 1,001 (one thousand one).

I conditionally format cell A10 based on the contents of cell A1, using the formula bit of the conditional formatting feature and leaving A1 undollared. I then copy A10 in its entirety to A9. Because Excel can't base its formatting on A0 (as such a cell doesn't exist), it instead bases it on A65536.

Quite cute really.

I created a lovely Excel doneness matrix a while ago, and Vivek has kindly rooted it out for me. (I'd lost it between laptops.) Here it is.

It basically tracks the doneness of various deliverables through various stages. It warns of impending deadlines and tells you how early or late things were done. Quite beautiful and very useful.

Thanks for digging it out, Vivek.

As mentioned a few weeks ago, I recently found out how to do the equivalent of a SUMPRODUCTIF. Here's the detail on how.

First of all, some context.

SUM does exactly what it says on the tin. It sums a range of cells.

SUMPRODUCT is a lesser-known function, and sums a set of products. (a1*b1*c1) +(a2*b2*c2) + [...] + (an*bn*cn)

SUMIF sums a range of cells if a certain condition is true. For example, sum people's salaries in a column if they are based in London.

I wanted a way of putting a condition on the SUMPRODUCT function. That is, I wanted to do a sum of the products only where a condition for those rows held true.

To make the formulae more manageable, let's assume I've named a few ranges: conditions is the column of data that I want to validate the condition against; range1 and range2 are the two ranges that I want to do the conditional SUMPRODUCT on. And let's assume I only want to do this if the values in the conditions range are "London".

The formula would read:

=SUMPRODUCT(--(conditions="London"),range1,range2)

Basically, the first term acts as a range in its own right, taking the value of 1 (for London) and 0 (for anything else). This means that it's not actually doing a conditional SUMPRODUCT, but instead it's multiplying the entries for which the condition fails by zero, which has the same effect. I think the double minus at the beginning is to ensure that the first argument is read as a formula.

Quite beautiful.

Today, my hair has ended the day in a much better state than it did yesterday. It was the most productive day I've had in a long while, not because I've been a work-shy fop of late; rather because I covered huge amounts, and my focus was relentless for ten hours. That sounds boastful, but rarely can I maintain such intensity throughout a day.

The day culminated in a three and a half hour hardcore, solo Excel session, which pushed my own boundaries to produce an absolute beauty. One particular moment of joy came at my discovery of an artistic, non-VB way of performing what could be described as a SUM PRODUCT IF, a function that doesn't naturally exist in Excel. More on that another time.

The Tate should set up a gallery dedicated to Excel, as spreadsheets often share the beauty of works of art. I'd like to think that today's effort would be short-listed.

Anyway, now I'm shattered, so am on my way home to my wife for a beer and a take-away curry. (Mr. Jones got run down.)

I often watch My Name Is Earl on a Thursday night. It's a solid comedy. Not great, but solid. Also, it segues into The Office (US version), which is always a bonus, unless you fall asleep on the sofa as The Office is about to start.

Last night, Earl got an office job for which he wasn't qualified. On being instructed to do something in Excel, his thought was as follows:

Since I had no idea what an Excel was, I decided to go stand in the elevator.

Genius. Such people exist?

I always get frustrated when people use Excel as a drawing tool, putting draggable drawing objects on top of the grid structure. If you want to draw something, use Visio or PowerPoint. Don't patronise Excel.

In my line of work, the most common way in which people do this is in project and programme plans. They create boxes that represent tracks of work or tasks, and position them relative to one another to show the tasks over time.

The trouble with this approach is that changes to the underyling assumptions result in a lot of fiddly manual effort to move everything around.

Here's a spreadsheet I created that takes an alternative approach.

Each track has a start and end date. The matrix compares these dates with the date that a column represents to determine whether or not to shade the associated cells. Cells are given values of "S" if they’re at the start of the task, "M" for middle and "E" for end. Conditional formatting uses these values to format the cells accordingly.

Applying a custom format of ";;;" to the cells means that the contents of the cells (the S, M and E) are hidden. Nice little tip.

Change the start and end dates, and the bars move accordingly. In this particular version, tasks are rounded to the month level, but the same concept can be used for week- or day-level plans.
The summary bars for each project are merely mins and maxes of the underlying start and end dates.

The nice part about this is that you can import the task data from MS Project to get a more visually appealing view, or you can use Excel formulae for the dates to make tasks dependent on one another.

First of all, news of a tornado being caught on camera by a helicopter in Oklahoma is greeted by possibly the most apathetic reporting ever caught on microphone, courtesy of BBC News' Fiona Graham.

And secondly (courtesy of Francis), a rather sycophantic yet impressive demo of some of the functionality that will be included in Excel 12. It's quite neat, especially when compared to the three conditional formats included in Excel 11. Zip along to 6m15s if you want to see the cool stuff.

I've just found out that Excel 12 will have 1,048,576 rows and 16,384 columns. The last column will be column XFD.

So, for every cell in a sheet in the Excel 2003, there will be 1,024 cells in Excel 12. A total of 17,179,869,184 cells per page, three for every person in the world.

Simon asked me today how to return the lowest positive value from a horizontal range in Excel. Given that there's no MINIF function, at first I struggled. (Create an interim series and work off that - not very elegant.)

Then I re-phrased the question: how do you return the Nth lowest value in a range? Ah, we can do that with the little-known SMALL function. And can we find N? Sure we can, using the COUNTIF function.

So, the following formula gets you what you want:

=SMALL([range],COUNTIF([range],"<0")+1)

From the range, count the number of non-positive values, add 1 (the result being N), and return the Nth lowest value.

Beautiful, even if I do say so myself!

It seems that as was the case when Marilyn vos Savant published the correct answer in the New York Times back in 1990, my entry is causing some (seemingly) intelligent people to blunder. (I have to be somewhat polite here, as one of the blunderers is my hosting provider.)

The rationale in the post below is pretty straightforward, as highlighted by the friend who originally sought my help:

"Perfect ... even I understand that!"

Nonetheless, it seems some people need some further evidence. What better way to help than via the powers of Excel. Here's a spreadsheet that takes you through the logic behind both the no switching option and the switching option. Each row in each of the sheets represents a unique experiment, with each element that is up to chance being driven by an independent random number.

In the no switching option, the only things that are random are the prize-winning door and the door originally chosen by the contestant. In the switching option, both of these elements are still random, as is the door that Monty opens, although this is sometimes forced.

The spreadsheet contains 50 rows of experiments, although you can copy a row down as far as your computer's memory will allow to see where your odds settle. Using all 65,536 rows (65,532 experiments of each type), I recorded a 33.15% win rate for games in which I stuck, and a 66.72% rate for games in which I switched.

Alternate row shading is quite useful if you've got particularly wide data (lots of columns) and when readers need to scan across individual lines. Bug listings, contact lists etc. I usually use a light yellow shading to help with this, alternating with the default white.

To generate the alternate row shading, I used to insert a new column at A, fill it alternately with 1s and 2s, filter the 1s, shade the filtered rows, then delete column A. The problem with this method is that if you insert a new row some way down, it screws up the shading, leaving two consecutive shaded or unshaded rows.

The better way to do this is to use conditional formatting, and base the conditional format on the row number, or more specifically, whether the row number is odd or even. If the cells you want to highlight start in row 2, highlight rows 2 through n (n being the bottom of your list). Select Conditional Formatting from the Format menu. In the first condition, select Formula Is from the dropdown and type the following into the condition area:

=mod(row(A2),2)=0

Then choose your desired shading through the Format button.

Because the first cell of the range highlighted is A2, it will apply this conditional format to A2, but change the reference accordingly for all of the other cells in the range. The formula itself takes the row number (for row 2, this is 2), divides it by 2 and calculates the remainder (in this case 0). If the remainder is 0 (i.e. the row is even), it will shade it; otherwise, it won't.

Maybe not the most concise title, but it's a great feature. Have you ever needed to fill a column or row with dates, but skip weekends? Pain isn't it?

Well no. There's a hidden menu that enables this. Put your first date in your first cell. Now hover over the bottom right corner of that cell until your cursor becomes a "+". Right-click (yes, right-click) and drag, either to the right to fill the row or down to fill the column. When you've gone as far as you think you need to, release the mouse button and you'll be presented with a nice little menu. This allows you to fill the range with all days (standard behaviour), weekdays only, months (giving the same day of each month) or years (the same day each year).

Clicking the Series option even allows you to only include every nth value, combined with the above.

It's a lovely feature, but I question its placement. Hiding things like this only causes frustration for those users who've done it the hard way for the last umpteen years.

Today, I added a new category to my blog: Excel tips. Those of you who know me will be aware of my penchant for MS Excel (Excel 5.0 being the most revolutionary piece of software ever written), so I thought I'd dedicate a corner of my beloved blog to it.

I'm not sure how useful this one is, nor where its business requirement came from, but it's quite cool. Excel allows you to create a dynamic picture of a range of cells, and for it to hover over the main body of the spreadsheet, as would any other picture. The beauty of this, however, is that the image changes as the cells' values change. Also, it's a hidden feature that many won't be aware of.

First of all, copy the cells that you want to make a picture of in the standard way. Then navigate to the place in the spreadsheet (either on the same sheet or a different sheet) where you want to plonk the picture. Now, instead of doing your standard paste, hold down Shift and click the Edit item on the main menu. As if by magic, the menu has changed slightly. Click Paste Picture Link, and there you have it. You can even rotate this picture to a cheeky angle to baffle your colleagues and to spice up your reports.