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:
    446
    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,052
    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:
    446
    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,632
    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:
    446
    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,632
  7. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    446
    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:
    446
    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:
    446
    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

Share This Page