Excel/Calc INDIRECT+ADDRESS+ROW+COLUMN cleaner alternative

Question

I'm not a regular Excel/Calc user, but I'm currently using LibreOffice Calc to record some money ins and outs. My two functions below works fine as intended, but still I'd like to know of a cleaner alternative; especially for the first one.

This calculates the daily income,

=INDIRECT(ADDRESS(ROW(),COLUMN()-2))-IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2))),INDIRECT(ADDRESS(ROW()-1,COLUMN()-2)),0)-INDIRECT(ADDRESS(ROW(),COLUMN()-1))

and this calculates the net income,

=SUM(INDIRECT(ADDRESS(2,COLUMN()-1)&":"&ADDRESS(ROW(),COLUMN()-1)))

Solution 1

You can achieve the same thing much more easily with standard cell references. Your first formula uses relative references only. Assuming that formula is entered into cell D3, it can be written as:

=B3-IF(ISNUMBER(B2),B2,0)-C3

Your second formula uses both absolute and relative references. This time assuming we're in cell E3, it can be written as:

=SUM(D$2:D3)

Notice the $ in D$2. This is an absolute reference, meaning as you drag the formula down, the upper bound will stay the same, while the lower bound expands downwards.

Solution 2

As Kyle indicates, people would typically just use relative addressing, and his answer describes how to do that. You can see how simple the formulas become.

Explanation of original formulas

The sample formulas you included in the question may have been done that way to highlight the relationship between the cell addresses. As long as the structure doesn't change, that style of addressing focuses on the logic of the physical relationships of the cells. Note, though, that if the structure of the spreadsheet changes, everything will break down because the references are tied to the original structure.

To understand what they're doing, ADDRESS builds a text description of a cell reference. This lets you use calculations to figure out where a reference should point. A good, brief demo here. INDIRECT converts a text description of a cell reference into an actual reference.

So if you want a cleaner alternative, the cleanest would be to just use actual cell references, like Kyle's answer. If you're in the situation where you actually want the focus on the spreadsheet structure, there are still a few ways to streamline this.

R1C1 addressing

There's a form of addressing called R1C1, that expresses addresses as absolute or relative rows and columns in a way that can be calculated and used directly, without needing the ADDRESS, ROW, and COLUMN functions.

For an absolute address, use row number and column number after the R and C labels. So A3 would be R3C1.

For relative addressing, leave off the number to refer to the current row or column, or put the relative number in square brackets. So the cell on the same row and two columns to the left would be referenced as RC[-2].

In both Excel and Calc, there's an option to change the address style to R1C1, which would apply to everything. However, you can use it in specific formulas without doing that by making it a text string and using INDIRECT (which needs the optional addressing style parameter set to zero to recognize the R1C1 style). So the first formula:

=INDIRECT(ADDRESS(ROW(),COLUMN()-2))-IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2))), 
 INDIRECT(ADDRESS(ROW()-1,COLUMN()-2)),0)-INDIRECT(ADDRESS(ROW(),COLUMN()-1))

would become:

=INDIRECT("RC[-2]",0)-IF(ISNUMBER(INDIRECT("R[-1]C[-2]",0)), 
 INDIRECT("R[-1]C[-2]",0),0)-INDIRECT("RC[-1]",0)

You can see that this is sort of a shorthand for the ADDRESS/ROW/COLUMN construction.

OFFSET

The typical way to do this, though, when you need to make the relative relationships obvious, is shorter than this. You can use the OFFSET function. The short form to refer to a single cell is OFFSET(reference,rows,columns). You specify the starting point and then the number of rows and columns away from it.

In your examples, the formula determines what the current cell is, and you could do that in OFFSET, but it generally isn't necessary. The actual cell reference is typically used. Like in Kyle's answer, that will normally get properly translated if you copy the formula to another cell. OFFSET is a way to make the location relationship explicit. So if your first formula was in cell C5, it would look like this:

=OFFSET(C5,0,-2)-IF(ISNUMBER(OFFSET(C5,-1,-2)), 
 OFFSET(C5,-1,-2),0)-OFFSET(C5,0,-1)

Comments

[Kyle]

Why are you using indirect/address like that? It looks like you could just use standard cell references.

[Admin]

@Kyle I just looked up the internet, and that's how it seemed to get the current row/column number and use it in other functions. But I do hardly know anything about these spreadsheet programs.