There’s history and history!

The other day I saw an article on the web by a writer who seemed to think that VLOOKUP was new to Excel 2013. Strange, I thought: I was using it in Lotus 123 in 1990.
Now that is only the earliest documented usage by me (I made a note in the Lotus manual). VLOOKUP has been around longer than that. I cannot find when it started but Visicalc (one of the earliest spreadsheets) had a LOOKUP function in 1979.

Well, 1979 is ancient history in computer spreadsheet, so I was rather amused on my recent holiday to Turkey (that’s why there haven’t been any blogs) to visit some sites which had carved tombs going back over 2500 years!

The photograph is of Myra in Antalya, with the carved tombs on the left, and the relatively new Roman theatre (2nd Century AD) on the right.

Even this is new, in a country where there are sites such as Göbekli Tepe (which I didn’t visit), which have remains of man-made structures dated between 9500 and 7500BC (ie 11500 to 9500 years old)!

Carved tombs in Myra

Carved tombs in Myra (Turkey)

Posted in Uncategorized | Tagged , , | Leave a comment

Relative and Absolute References

One of the great attractions of spreadsheets when they started was their ability to quickly replicate complex calculations, once those calculations had been developed for one category. An example might be calculations worked out for one month , which could then be replicated quickly across the whole year.

And most of the time that is how we want it, so we don’t even notice.

However there are times when we want the reference to remain fixed, eg we have today’s date in one cell, and we want lots of cells to refer to that. In that example, we could name the cell DateToday, and use DateToday in any formula, with the added advantage of improved legibility.

Another example is in analysis. Supposing we have a table of prices:

Absolute Reference example

Depending on whether the item in column A is an Apple, Banana or Currant, we want the price in the appropriate column.

We can do this with the formula (for the top row) of =IF($A4=”Apples”,$B4,””). This can be copied down successfully, as normal. The dollar sign indicates that the Column is fixed, but the row can change as normal. This is known as an absolute reference, or anchoring.

If we copy this across, because of the anchoring, it will remain pointing to the first column, and the result will come from the second column. We will have to change Apples to Bananas, but that is all (and we can combine it with an even more powerful use of absolute references to avoid doing that, but that’s for another day!)

In an absolute reference, the column can be fixed (eg $B5), the row can be fixed (eg B$5) or both can be fixed (eg$B$5). The dollar signs can be handtyped,  or you can work your way through the different combinations by pressing the function key .

Posted in Uncategorized | Tagged | Leave a comment

Conditional formatting – Hierarchy

In 2003, the hierarchy is very important in Conditional Formatting: if you have that wrong some conditions may never be tested. Supposing you want the cell to go red if less than 50% in an exam, amber between 51 and 75%, and green for above 75%.
Conditional formatting is a very simple test, which stops testing after a true condition.
In the exam example, if you tested for a score over 50 before over 75, then the second test would never be performed, because the first was true.

From 2007, the default is to test all conditions.

If you don’t want the tests to continue after a condition has been satisfied (ie it’s TRUE), then you should tick the “Stop if True” box in Home, Conditional Formatting, Manage Rules. This has to be applied individually to each Rule.

Even so, you need to check that rules don’t conflict, or you might not get the results you are expecting.

Posted in Uncategorized | Leave a comment

IF statements – multiple levels

IF statements really bring power to your spreadsheets. And multiple IFs, even more so.

Before 2007, the limit was 7 nested IFs in one statement. My advice was to avoid anything more than 3, because it would make your head spin.

Multiple levels are difficult to follow, meaning it is easy to make mistakes, and a real problem trying to debug them.

It is also very easy to put a comma in the wrong place and obtain a completely different result from that which was intended.

This combined with the issue that logic doesn’t always behave as expected (particularly where negatives are involved) increases the potential for error.

And, even if you get it right, it can make future changes and development tricky for the same reasons.

And from 2007 on, the limit has increased to 64!

As a simple rule of thumb, if you find yourself going beyond 3 levels, then there’s probably a better way of doing it (eg Vlookup, Index).

Posted in Uncategorized | Tagged , , | Leave a comment

Clarity trumps prettiness

You’re looking to produce an impressive report or set of charts from Excel, but don’t forget that a good-looking chart or report is meaningless if you haven’t used the right data or calculations.

Before that final step, where looks are important, the most important thing is to lay the data out well, and ensure that the logic is clear and repeatable.

Have you included all the rows? (missing out rows is a simple mistake to make, but can produce wildly different results).
Has the information been formatted consistently (for example, some percentages might appear as 12% (in which case Excel stores it internally as 0.12), while others might appear as 12 in a percentage column (and Excel will store it 12, 100 times bigger than you mean it to be).

Can you trace your working easily through the spreadsheet? (someone verifying your results needs to be able to do this. That someone may even be you after several months.
This is also important if you wish to develop the spreadsheet further or debug it).

Do those stripy tables add anything to the understanding of the data? (if you’ve made good use of Conditional Formatting then stripes will just be confusing.

Don’t merge cells except in the final output, where you know no calculations will be performed on the cells.

It’s best to leave the prettifying until the final stages, other you can waste a lot of time to find that something needs changing again.

Posted in Uncategorized | Leave a comment

Conditional Formatting: highlight items automatically

Conditional Formatting changes the font, border or colour of a cell depending on the criteria you have chosen. For example you might set a cell to go red if it went over 32 or green if a certain date is only three weeks away. This is very useful for highlighting records, eg, a temperature threshold exceeded or forthcoming subscription renewal.

Conditional formatting is a very useful tool in 2003, which has become even more powerful in 2007, because it’s not restricted to three criteria per cell.
However, the extra flexibility of 2007 also means it is easier to set up conflicting rules.
Worse than that, Excel also sets up duplicate rules in the background because they refer to a different range. This is a particular problem when one is developing rules, a process which is best done step by step (conditional formatting doesn’t always behave in the way one expects). Unfortunately having developed it for one cell, copying it forward over a range of cells, retains the original test, so there are now two rules.

I think two main rules apply with conditional formatting:
1/ Don’t have too many – it just gets confusing
2/ Define your own rules and don’t use Excel presets, because then you know what is going on

Posted in Uncategorized | Tagged , | Leave a comment

Automatic Date and Time in Excel

If you want to put today’s date into your spreadsheet, then use =today().
But remember this is dynamic, which means it will change. Thus it’s not much use for dating a document, because tomorrow’s another day!
To get the current date and time, use =now().
This like =today() is dynamic, but even more so. Because it includes the time it will update almost every time a value in the spreadsheet is changed.
If you wish to insert today’s date as text, then type Ctrl+; and for the time type Ctrl+:
These can be combined in a single cell.
However, if you want Excel to automatically insert the date and/or time as text, then you’ll have to use VBA.
Posted in Uncategorized | Tagged , , | Leave a comment