1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Relative referencing is wrong

Discussion in 'The Lounge' started by Peter Bartholomew, Sep 28, 2018.

  1. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    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.
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    My take. Excel is visual oriented tool not code/programming oriented tool.

    For it to gain acceptance among larger audience. It's beneficial to have numbered relative reference, instead of contextual reference.

    In DAX it does use [Column] as reference (i.e. array/list), and does away with relative reference.
  3. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    I agree with each of your observations.

    I probably also took the fact that both M and DAX work well without requiring formulas that apply to single values within the list as providing some vindication of the ideas that were gradually taking shape within my mind.

    The problem I as I see it being "Is it reasonable to expect a visual, action-led and intuitive process for manipulating numerical data to provide a robust basis for building models that (purport to) form the basis for important decisions?"

    … or should spreadsheets be seen as fit only for quick ad-hoc calculations of minor significance? I suspect that is how most IT professionals see it.
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,668
    There are some new Dynamic Formula functions about to be released.
    They are available on the Excel Fast release version already

    They will add a lot of functionality to spreadsheets and nearly remove the Ctrl+Shift+Enter functionality
    They also expand as the ranges require

    Stay tuned
    GraH - Guido likes this.
  5. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    I probably have posted this discussion sooner, since I have long held the view that both relative referencing and the direct referencing notation are unsuitable for serious solution development. Now it may well be that the distinction between array and non-array formula now simply fades away. Unless, of course, users simply ignore the new functionality when it is introduced :(.

    I was first alerted to the possibility of the new developments at the ModelOff GTC London in 2016 and have been eagerly awaiting change since that time. As someone who often builds models entirely from array formulae, I am keen to put dynamic arrays through their paces, though I might need to get the case to buy a further 365 license for that purpose past senior management (my wife) :eek:.
  6. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,668
  7. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    Hui
    Thanks so much for the reference. I have printed it off and read pages 1-34 in detail! The new functionality seems to be far more pervasive than I had expected. Soon, I will have to unlearn my behaviour of committing all formulas with CSE :)!

    Over the last year or so I have been publishing a series of Excel articles 'Excel for Engineers and other STEM Professionals' in an engineering magazine for analysts. The subtitle could have been 'If you're so smart and use matrix algebra every working day, why do your spreadsheets look as cr*p as everyone else's?'. I can see a rework being called for in due course!
    Hui likes this.
  8. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    I had held off posting this thread because there is a limit to how many people one is willing to risk upsetting and how often. Now, though, I have been well and truly overtaken by events. Check out this 20min conference clip.

    https://myignite.techcommunity.microsoft.com/sessions/64705

    Do you believe array functionality will now be the starting point for all Excel calculation?

    Will 'and copy down' disappear forever?

    OR

    Will disinterest and reluctance to change mean that things simply stay the same? Backward compatibility would allow that after all.
  9. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    I learnt some new things this week.

    The first is that CSE is not needed to make any Excel function or operator evaluate an array formula. The Excel calculation engine works with 2D arrays and always has. This applies to F9 in the formula bar, Named formulas, conditional formatting formulae …

    This can be seen from the addition of arrays shown below. The first row might suggest that CSE is needed. The second row shows that the '+' operator is perfectly aware of the arrays and has, in fact, calculated each term in the 3x3 array without CSE.

    upload_2018-10-7_11-40-42.png

    The problem is that a formula in the grid does not convert a range into the corresponding array whenever a value is expected by the formula. It will instead try to pick a value out of the array by implicit intersection. Most of the errors you see when you forget CSE arise during the implicit intersection process [#N/A - no intersection, #VALUE! - multiple intersections].
    CSE is simply the instruction to omit the implicit intersection step and load the array as requested. SUMPRODUCT is different, not because it magically does something extra; it is different because it omits the implicit intersection step. ROWS does the same, otherwise it would always return 1.

    For those amongst you for whom this is a statement of the obvious, please accept my apologies.
    Last edited: Oct 7, 2018
  10. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    Having spent some of the last month or so looking at the new dynamic ranges (including some great video material by Mike Girvin) I return to the topic of this discussion.

    Is there any calculation that really still needs the concept of relative referencing or could it be left to gracefully slide into oblivion along with Excel's XLM macro language? Of course, human psychology might chart a different path.
  11. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    I'd argue that there's still place for it.

    Particularly in string manipulation. While PQ and new dynamic ranges do handle things. It's often beneficial to just use single cell references to construct SQL string on Excel sheet, making minor adjustments on the fly. Quick copy paste into SQL MS, to insert many records quickly in one shot.

    I often use this approach, when I need to insert into multiple dependent tables, and where I need to reference ID generated in the first step in the second.

    But in general, for analysis, I've mostly done away with traditional formula and primarily use data model and DAX.
  12. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    Chihiro
    I am not fully sure I understand the setup but then, I rarely have datasets to analyse with Excel. The snippet above, taken from your description, reminds me of the labels used to tag and reference statements in M.

    Something I wonder, is the relative referencing you describe an essential feature of the solution or merely a device that is convenient and quick?

    The other possibilities I have in mind are:
    1) an absolute reference
    2) an array with referencing by index
    3) a table with foreign/primary key pairings
    4) a linked list
    Would any of these work for the use-case you describe?

    There are places where I do use relative references but the contexts are very restricted. Ones that come to mind are
    1) the '@' operator in structured referencing to reference properties of the current record
    2) where the number of array dimensions exceeds two, looking up an index can be achieved by explicit intersection of the row through the formula cell and an index column used as steering data.
  13. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    This. It's just one time insert into db. So, I'm not concerned with rigid integrity (that's handled on db side). Nor do I want to spend time studying pattern for potential exceptions. I just handle exceptions as I find them. It's fast way of updating/inserting 100~1k rows into existing table in db (using text functions to construct SQL insert statement etc).

    I've always believed Excel's strength is in it's flexibility and adhoc reporting/transformation. Why I believe there is place for relative referencing along with more structured referencing types.
    Peter Bartholomew likes this.
  14. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    OK. I accept that relative referencing can give rapid results in support of ad-hoc calculation, and can be especially useful in cases where the workbook as not going to be saved for re-use.

    When one considers that 96% of workbooks do not contain formulas, that does set the bar pretty low for the next tranche of workbooks that do perform minor or ad-hoc calculation.

    At the other extreme, I have seen large, highly complex models build on single-cell relative referencing. The proponents of such workbooks, built using only the most basic (primitive?) techniques, claim that their workbooks are 'simple' and 'can be easily audited'. I agree except for the last two statements!

    Simplicity in excessive quantity can almost be considered a definition of complexity.
    ThrottleWorks and Hui like this.

Share This Page