Zen and the art of Business Operations Management #1

1

Targets. Plans. Forecasts.
Is your forecast real or dream?
Do your dreams come true?

2

Consideration
should always be given to
utilisation.

3

Talent - ideas -
technology - time - testing -
revenue - profit.

Four functions I'd like to see in the next version of Excel

Originally published on my company internal blog.

Excel provides a bewildering array of built-in functions for all manner of scientific and statistical analysis. It even lets users write their own functions. But no matter how flexible a tool is, users will inevitably want immediate, specific functionality ​that meets their needs now. Here are some Excel functions I'd love to see.

ACTUALPERCENTAGE()

Copy-and-paste a percentage from a text source, and the value is stored is a number such as 50. But Excel stores actual percentages as a fraction (0.5) and merely dislays them as a number with a % sign. This means that 50, when imported from a text source, displays as 5000%. That's definitely wrong.

This useful function looks at the context of your number, including the number of decimal places that you've set, and decides whether it should be divided by a hundred or not.

CELLFORMAT()

Conditional formatting lets you set the format of the cell based on the cell's contents. For example, cells containing negative numbers can be highlighted in red.

But what if you want to change the contents of the cell based on its formatting? For example, what if all red cells should have their values weighted? That's where CELLFORMAT() comes in. It returns a description of the format of the cell against which you can do useful comparisons and adjust your business logic accordingly.

=IF(CELLFORMAT()="Red with yellow stripes", "Safe to bathe", "Do not go in the water")

Remember: It's considered best-practice to design your spreadsheets so that business logic depends upon the formatting of the cell.

ABACUS()

Excel already includes a ROMAN() function which converts numbers into Roman figures. But what if your Excel user isn't an ancient Roman, but instead is a Mesopotamian or Egyptian from 2,500BC? That's where ABACUS() comes in: it converts numerals to a visual representation of beads. An optional second argument returns the result in the Sumerian base-60 system.

CPP()

Everyone knows that VBA is a toy language used only by failed software engineers who've turned into ops managers through sheer bad luck. What Excel really needs is a way of executing a proper language, within the constraints of a normal formula (ie 32,767 characters long). CPP() executes C++ code within the context of the spreadsheet. Spreadsheet objects are available in the same way that they are to VBA.

=CPP("#include <iostream> ; #include <worksheet> ; int main() { cout << \"Hello, \" << Worksheet.Cells(\"A1\").Value(); return 0; };")

See? Simple, clear, powerful.

Six baffling Excel functions

Originally published on my company internal blog.

BAHTTEXT

What it does: The baht is the currency of Thailand. BAHTTEXT(123) returns the words "One hundred and twenty-three baht", but in Thai.

Why it's baffling: Because this is the only function in Excel that does anything like this, and it's only available for the baht. There's no equivalent for converting a number to text in any other currency or language.

CODE

What it does: CODE("HELLO") returns the code for the letter "H" in the computer's current character set

Why it's baffling: For a start, it's somewhat difficult to see why anyone might ever want to use Excel to get the ASCII / ANSI / whatever code for the first letter of a string. And then there's the contortions you'd have to go through if you wanted, say, the ASCII code for the second letter instead.

DAYS360

What it does: Returns the interval between two dates, in days, whilst assuming that all years have 360 days in them.

Why it's baffling: I can just about see why a company might have equal-length accounting periods - 12 months times 30 days - but I'm seriously struggling to see why you'd want to do date arithmetic on this basis. An optional second argument gives you a modicum of control over which five or six days of the year get ignored but still leaves open the awful question: what if my birthday no longer exists? Do I still get cake? Also, there are now automatically five (or six) fewer shopping days left until Christmas... I think. And there now exists both a 29th and a 30th of February every year.

ROMAN

What it does: Converts from numbers to a text string in Roman numerals. For example, ROMAN(5) returns "V" and ROMAN(21) returns "XXI".

Why it's baffling: Three reasons. First, unless you need to put a copyright date at the end of a TV programme, why would you ever want Excel to work in Roman numerals? Second, there's a bewildering array of optional arguments that return strings in different forms, most of which, I suspect, wouldn't mean much to a real Roman. Third, there's no function for converting a Roman numeral string back into conventional Arabic digits - so having converted a number to Roman, it can't be processed any further.

SUBTOTAL

What it does: Sums across a range of cells but excludes any cells that already contain a SUBTOTAL function. This means that you can use a SUBTOTAL function on a group of lines, and a SUBTOTAL again against a group of groups, without counting the same figures twice.

Why it's baffling: Because it doesn't actually sum, and hence doesn't actually provide a subtotal, unless you give it a very specific first argument: the number 9. Not only do you have to know that the first argument is one of 22 pre-defined function reference numbers, but you also have to remember that the most obvious default number is not 1, but 9.

DAVERAGE

What it does: There's a range of database functions whose names start with a "D". This one returns the average from a database or list of values.

Why it's baffling: OK, actually, it's quite straightforward and not baffling at all. But I like to think of this function as being called DAVE_RAGE which nicely defines my normal experience when using Excel.

TV review: Orchestra United **

An email I sent recently to Channel 4. I'd be interested to hear other people's considered responses, particularly those who are musically inclined.

I am writing with some comments on Sunday night's opening episode of "Orchestra United".

I have been involved with amateur, community-level music making for many years, and I also do voluntary work with teenagers. I feel very let down by the contents of the opening episode of this programme. It is not clear to me the extent to which this was due to editing for television rather than flaws in the orchestral initiative itself, but in any case, I feel that "Orchestra United" has done the world of classical music a great disservice, not to mention severely underestimating the talents of the young people involved.

The programme fatally misrepresented or glossed over the background of many of the participants. It was billed as an attempt to form an orchestra of young people who had no classical experience, yet the vast majority were proficient - many of whom were very talented - at their instruments. A talented violinist or harpist cannot help but already have exposure to classical music. It was mentioned in passing that at least one of the young people was already a member of the Halle Youth Orchestra - this is hardly in the spirit of opening up a new world of classical repertoire to a generation raised on popular music.

Consequently, the programme failed to address the inherent difficulty in persuading young people of the merits of classical music. Yet the programme presented itself as an attempt to sell the virtues of classical music to those with no experience of it. For example, much was made of the cornet player saved from going off-the-rails by joining his father's brass band. Surely this was the act of most interest. Why did he start playing the cornet? How was he persuaded to keep going to rehearsals? What do his friends think about it? How does he feel about the repertoire he is discovering? All this would have made for a significantly more interesting programme. Furthermore, it would have been educational for those of us who have similar problems in our community music-making.

The crux of the problem with the approach shown on screen is that all of the young people were already motivated by the idea of being in a professionally-led symphony orchestra. Because of this focus, the programme came across as deeply patronising. It also set unrealistic expectations for what young people, particularly disadvantaged ones, might achieve in similar circumstances.

At the same time, the technical process of establishing the orchestra seemed hopelessly naive. Including musicians with "potential" who could neither read sheet music nor play a traditional orchestral instrument was a worthy idea but not in the spirit of the specified intention of the project. One might cynically conclude only that this makes for good television and serves no purpose for the orchestra itself.

These young people were then placed into a full orchestral rehearsal with no preparation whatsoever, and the rehearsal was shown to be disastrous. Unless James Lowe has spent far too long working with professional musicians and has forgotten what it is like to be a student (and a teenager), this was completely inevitable and should have been easily foreseen.

The conductor waved his baton and then shouted at them that the music was "in two". Since the entire premise was that most of the musicians had never been in an orchestra before, why assume that any of them understood his instructions or even knew what a conductor is actually for? Why did Lowe not take the time to explain this? Why did he not take the entire contingent to watch an orchestral concert first, so that they knew what to expect? Why did he not begin with sectional rehearsals instead of trying to co-ordinate 75 musicians on the very first try? The conductor was extremely unrealistic and naive about what would happen.

The fault for that first rehearsal was entirely in the planning and organisation, yet the programme appeared to blame the young people themselves, perhaps in an effort to emphasise how much work is required in order to achieve concert quality. This does a further disservice to the musicians involved and to those hoping to inspire other young people.

Because "Orchestra United" was funded by a public service broadcaster and in association with the Arts Council, I had high expectations that were profoundly missed by the opening episode of this programme. I look forward to the balance being redressed in the remaining instalments. But I shall continue to be extremely disappointed if the musicians' pre-existing talent is not acknowledged, if Lowe continues to act as if he is conducting a professional orchestra, and if the series does not adequately explore what opportunities classical music can offer the disadvantaged.

Channel 4's response message:

Thank you for contacting Channel 4 Viewer Enquiries regarding ORCHESTRA UNITED.

We are sorry to hear that you feel that this programme has done classical music a disservice and underestimated the talents of the young people involved.

Please be assured your complaint has been logged and noted for the information of those responsible for our programming.

Thank you again for taking the time to contact us here at Channel 4 and for your interest in our programming.

Quarterly film roundup - April to June 2010

Son of Rambow *** - Interesting if somewhat implausible coming-of-age type drama about two boys with very different backgrounds who begin an awkward friendship over a shared desire to shoot a short film.

The Fox And The Child **** - Thoroughly beguiling dramatised nature documentary from the makers of March Of The Penguins that does exactly what it says on the tin.