Forum Discussion
IF function and Structured references need syntax help
Where did the structured references go? Why not
= IF([@tier]="platinum", [@sales]*5%, 0)But then, I am the only person on this forum that refers to the A1 notation as an abomination that has no place in any computational environment!
Where did the structured references go? Why not ...
But then, I am the only person on this forum that refers to the A1 notation as an abomination that has no place in any computational environment!
Well, Peter, had there been a table in evidence, with those headings, that's how it would have come out, but you'll have to admit we were also dealing with a very basic question, and the A1 notation is still how we all begin.
"The A1 notation is still how we all begin"
It is, because that is what is discoverable by trial and error and that is what the 'tips and tricks' genre teaches. Every now and then, I contemplate how one might go about teaching Excel subject to the rule 'never use direct cell references'.
The speculation was triggered by the FAST financial modelling standard that includes 'do not use defined names' and 'do not use array formulas', both of which I objected to as statements of good practice. To migrate from 'standard practice', I wrote event handlers to identify formula cells that contained direct cell references (a RegExp routine helped) and convert any such cells to text format with red text on a yellow fill.
The initial part of such an alternative course would be, first use a Table or a Defined Name to declare a region of the worksheet as part of the solution space and to link the data to the problem domain (any data lying outside such a region cannot be referenced and, therefore, are mere annotation).
Something that the direct notations do well is create relative references such as
= R[-1]C + RC5
(though A1 notation is less effective at showing the intent).
That said, when one examines the purpose of such referencing, it is usually to replicate the functionality of an array formula element by element. One array formula will usually do the job better and is less prone to errors (such as the Reinhart-Rogof error that plunged much of the world into austerity).
A remark by Dan Bricklin during his TED talk on the birth of VisiCalc was: "it [cell referencing] could be done the programmer's way using variables, but that would be tedious!" The plus of that observation is that it probably paved the way to the incredible world-wide success of the electronic spreadsheet; the minus is that barely 10% of those spreadsheets do not contain significant errors.
'Normal' spreadsheet practice is so engrained within 'muscle memory' that I cannot see it changing anytime soon, which leaves me heading off in a different direction and makes answering forum questions somewhat tricky.