Author Archives: Dan

Working with dates

A lot of people get confused when working with dates. But they're really rather simple when you get to know them. All dates and times are stored as numbers, the unit being days. Specifically, they're stored as the number of … Continue reading →

MAX

Summary MAX brings back the maximum value from a range of cells. As with other similar functions, it ignores any cells containing text or logical values As with SUM, it only applies to cells that are formatted as numeric values … Continue reading →

COUNT

Summary COUNT is not as straightforward as it might look. Many people think it counts the number of cells in a range, or the number of populated cells. But it doesn't. Instead, it counts the number of cells in a … Continue reading →

AVERAGE

Summary AVERAGE takes some legwork out of formula creation. It adds up some numeric cells and then divides this by the number of cells it added up. In mathematical jargon, it provides the arithmetic mean of a set of cells. … Continue reading →

SUM

Summary SUM is probably the most regularly used formula. It does exactly what it says on the tin. It adds things up. It only applies to cells that are formatted as numeric values (including dates, percentages etc. as well as … Continue reading →

How to construct formulae in Excel

Formulae are created by combining up to six types of object: operator condition value or reference range informational logic helper Each will be described in detail here. The subsequent formula descriptions will refer back to these, so it’s important that … Continue reading →

Using dollars in cell references

Dollars are inserted into cell references to influence what happens when that cell reference is copied from one cell to another. It only affects behaviour when the cell reference is copied and pasted elsewhere. It does not have any effect … Continue reading →

Formula fundamentals

If you're new to formulae, or are keen to get your toes wet, here are a few tips to get you started. Every formula should start with an = sign. =A1+B2 =MAX($A:$A) Beyond basic arithmetic, every formula has a standard … Continue reading →

Olympic ticket allocation spreadsheet: explained

Yesterday, a friend and a Twitter follower (two separate people) asked me to put together a spreadsheet to figure out which Olympics tickets they might have been allocated based on the amount of money that would, they hoped, shortly be … Continue reading →

Toggling between values and formulae

For most people, the only way to see the formula in a cell is to click on the cell. You can then see the formula in the bar immediately below the ribbon. Hit F2 and you can start editing that … Continue reading →