Peter Bartholomew
Well-Known Member
Surely not!
After all almost every spreadsheet that has a formula has relative references; they are the default. They are fundamental to the way in which spreadsheet technology is conceived. Each cell may contain a formula that references a number of other cells but it can only return a single value. From the outset this 'many to one' behaviour distorts the way business problems are analysed. Solutions are built bottom up in a piecemeal fashion.
It is, however, unusual for a cell to contain data with unique characteristics; just as sheep tend to come in flocks, so Cells tend to come in Ranges that contain lists or, less commonly, arrays. Unfortunately, 'normal' practice is to ignore the lists and arrays and concentrate on the formula to build a field within a single record of the list or a single element of an array.
Most commonly, one sees large, semantically opaque formulas being built within a cell and then the final instruction is 'and then fill down' (or across or both). There are even special notations to support this by occasionally invoking the mysteries of 'row or column anchoring'.
What other option would be possible?
I believe it is possible to build solutions perfectly well using absolute references only. Instead of
= X2 + Y2
filled down to give
= X3 + Y3
= X4 + Y4
= …
and so on ad-nauseum one could/should simply have an array relationship
= X + Y
The number of values returned should not be left to the user (i.e. how far should the formula be filled down), it is completely determined by the dimension of the arrays X and Y. All the user can bring to the party is error.
If a reference to a single element of an array is needed, the relationship could be built from a meaningful expression. A function
= RELATIVE( A, B, C )
could be an absolute reference to A but with a offsets that matches that of the formula cell to its containing range B (or if it is not in B then is may, optionally, be in C). A compact notation for a relative reference simply encourages excessive use.
When are relative references either meaningful or simply needed?
The use of @ to reference fields within the same record as the formula cell is both meaningful and important. References to other records are not desirable since sorting and filtering are legitimate list operations that can easily destroy other relative references.
The other use for relative referencing is to cope with data dimensionality in excess of two. If one of the dimensions can be eliminated by relative referencing, that enables a spreadsheet solution rather than switching to another application for model building.
If you do not like what I have written please pitch in. Even if you do not convince me, it might still help me get some understanding of the motivation behind normal spreadsheet practice. It took a video of Levi Bailey working on a spreadsheet for me to realise how fast a the conventional 'action-led' development practices could be.
After all almost every spreadsheet that has a formula has relative references; they are the default. They are fundamental to the way in which spreadsheet technology is conceived. Each cell may contain a formula that references a number of other cells but it can only return a single value. From the outset this 'many to one' behaviour distorts the way business problems are analysed. Solutions are built bottom up in a piecemeal fashion.
It is, however, unusual for a cell to contain data with unique characteristics; just as sheep tend to come in flocks, so Cells tend to come in Ranges that contain lists or, less commonly, arrays. Unfortunately, 'normal' practice is to ignore the lists and arrays and concentrate on the formula to build a field within a single record of the list or a single element of an array.
Most commonly, one sees large, semantically opaque formulas being built within a cell and then the final instruction is 'and then fill down' (or across or both). There are even special notations to support this by occasionally invoking the mysteries of 'row or column anchoring'.
What other option would be possible?
I believe it is possible to build solutions perfectly well using absolute references only. Instead of
= X2 + Y2
filled down to give
= X3 + Y3
= X4 + Y4
= …
and so on ad-nauseum one could/should simply have an array relationship
= X + Y
The number of values returned should not be left to the user (i.e. how far should the formula be filled down), it is completely determined by the dimension of the arrays X and Y. All the user can bring to the party is error.
If a reference to a single element of an array is needed, the relationship could be built from a meaningful expression. A function
= RELATIVE( A, B, C )
could be an absolute reference to A but with a offsets that matches that of the formula cell to its containing range B (or if it is not in B then is may, optionally, be in C). A compact notation for a relative reference simply encourages excessive use.
When are relative references either meaningful or simply needed?
The use of @ to reference fields within the same record as the formula cell is both meaningful and important. References to other records are not desirable since sorting and filtering are legitimate list operations that can easily destroy other relative references.
The other use for relative referencing is to cope with data dimensionality in excess of two. If one of the dimensions can be eliminated by relative referencing, that enables a spreadsheet solution rather than switching to another application for model building.
If you do not like what I have written please pitch in. Even if you do not convince me, it might still help me get some understanding of the motivation behind normal spreadsheet practice. It took a video of Levi Bailey working on a spreadsheet for me to realise how fast a the conventional 'action-led' development practices could be.