Mutatis mutandis

For this week's exercise, have a look at the little bit of a spreadsheet shown below.

	A	B	C	D
1	0	1	2	3
2	1	X
3	2
4	3

Your aim is to fill cells B2 to D4 with a formula which adds together the number of the row (in column A) and the number of the column (in row 1). The cell marked "X", for instance, should have a 2 in it.

The catch is that I'd like you to write the formula once, in cell B2, and then fill the remaining cells by copying and pasting that cell. No alteration of the result is allowed.

Last week's puzzle was: what is the largest whole number that Excel can represent?

(Note: To try the examples, you will need to change the Number formatting of the cell to "Number" rather than "General." Excel will then display all the digits it can.)

The largest whole number which Excel can represent exactly is 2⁵³ − 1. (Why? See floating point numbers on Wikipedia.)

Or, possibly, it is 1,000,000,000,000,000.

In true Microsoft style, things are more confusing than necessary. Let's start with the easy case.

If you type into a cell any number with more than 15 digits, Excel will correctly display only the first 15 – the last few digits will be zero. So, for example, 1234567891234567 is displayed as 1,234,567,891,234,560. In that sense, Excel can't cope with numbers having more than 15 digits.

However. Excel will store the number just fine – all the way up to 2⁵³. It just won't show you the number correctly. You can tell that Excel is storing the number because, if you add one to it, you get a number which is different from the first. That is ... well ... sort of.

Try this: in one cell, say A1, enter =2^53-1; and in another cell, say A2, enter =A1+1. Now try:

=(A2-A1). The result is "TRUE"
=(A2-A1)=1. The result is "TRUE"!

So Excel is claiming that 2⁵³ − 1 and 2⁵³ are the same; but that their difference is nonetheless equal to one.

Lovely.

Large, whole numbers

What is the largest whole number that Excel can represent?


Tip: To choose the format in which a number is displayed, click the "open" icon at the bottom-right corner of the "Number" group on the "Home" tab.