+ Reply to Thread
Results 1 to 14 of 14

Colon (:) is an OPERATOR

  1. #1
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,412

    Colon (:) is an OPERATOR

    This may have been addressed before, but there seems to be widespread lack of understanding about this.

    A2:A99 is a reference to a multiple cell range, but that reference is the result of an operation applied to two single-cell range addresses. A2:A99 is the smallest single-area range containing A2 and A99.

    More general example, C5:X7:G3:P9 is the smallest single-area range containing C9, X7, G3 and P9, which happens to be the same range as C3:X9.

    Where it gets odd is generalized references into worksheets other than the one containing the formula using such references, for example, This!X99 with the formula =SUM(INDEX(Other!A$1:A$100,foo):Other!$A$100).

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,717

    Re: Colon (:) is an OPERATOR

    Quote Originally Posted by hrlngrv View Post
    A2:A99 is the smallest single-area range containing A2 and A99.
    More rigorously, it is the rectangular range with A2 and A99 at its upper-left and lower-right corners. Since they are in the same column we have an edge case but the terminology still applies.

    More general example, C5:X7:G3:P9 is the smallest single-area range containing C9, X7, G3 and P9, which happens to be the same range as C3:X9.
    I was surprised to find that this syntax is even legal. [EDIT]However, this seems to be correct. Here is a SUM formula using that range. I have put a border around C3:X9. You will see that the numbers at the corners of that range are included in the sum.

    Name:  range1.JPG
Views: 137
Size:  40.0 KB

    If you select the formula, you will see that Excel has interpreted the expression to be two separate rectangular ranges, which are not the same as C3:X9. [EDIT] I cannot account for this, or why it contradicts how the sum is calculated.

    Name:  range2.jpg
Views: 144
Size:  46.4 KB

    Where it gets odd is generalized references into worksheets other than the one containing the formula using such references, for example, This!X99 with the formula =SUM(INDEX(Other!A$1:A$100,foo):Other!$A$100).
    I do not understand what point you are making with this last example. It doesn't seem odd to me at all.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    INDEX returns a range (assuming foo is a valid integer), and so the overall expression inside SUM is a range starting from some cell within the range Other!A$1:A$100 and ending at the cell Other!$A$100 (If the sheets in the two ranges are not the same sheet you will get a VALUE error.)
    Last edited by 6StringJazzer; 01-26-2021 at 11:28 AM.

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,412

    Re: Colon (:) is an OPERATOR

    Quote Originally Posted by 6StringJazzer View Post
    . . . I was surprised to find that this syntax is even legal. . . .
    Thus my point in posting.

    . . . I do not understand what point you are making with this last example. It doesn't seem odd to me at all.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The frequency at which such constructs solve questions here, and the apparent lack of familiarity with such uses of :.

  4. #4
    Registered User
    Join Date
    02-01-2021
    Location
    Republic of Lithuania
    MS-Off Ver
    Excel 4
    Posts
    7

    Re: Colon (:) is an OPERATOR

    Quote Originally Posted by hrlngrv View Post
    A2:A99 is a reference to a multiple cell range, but that reference is the result of an operation applied to two single-cell range addresses. A2:A99 is the smallest single-area range containing A2 and A99..
    This is interesting It many times in particular this is not always understood it right is it for many things in VBA. ( UsedRange , CurrentRegion all related to similar idea to smallest single-area range containing

    Also is interesting to note this is -
    It often said like I do this Range(TopLeft, BottomRight) ……
    Can be yes, but not must be
    Example
    Range("D4:B2", "C3:N2") will be smallest single-area range containing D4 B2 C3 N2 like B2:N4
    Is same as you did say like
    Range("D4:B2:C3:N2")

    ( This is not working. .. Range("D4:B2", "C3:C4:N2") .. me is not sure why. Must think I bout it bit more…. )

    Borat

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,717

    Re: Colon (:) is an OPERATOR

    Further evidence from VBA:

    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,412

    Re: Colon (:) is an OPERATOR

    Quote Originally Posted by 6StringJazzer View Post
    . . . If you select the formula, you will see that Excel has interpreted the expression to be two separate rectangular ranges, which are not the same as C3:X9. [EDIT] I cannot account for this, or why it contradicts how the sum is calculated.

    Name:  715367d1611674571-colon-is-an-operator-range2.jpg
Views: 121
Size:  46.4 KB

    . . .
    If you're willing to be sufficiently cynical and accept that the way Excel evaluates formulas using such constructs is correct, then the explanation is simply that MSFT fubarred how ranges are highlighted in Edit and Enter modes. IOW, it's a bug MSFT almost certain would view as exceeding low priority if anyone on the Excel developer team were aware of this.

    You don't labor under the impression MSFT has never made, currently isn't making, and never again will make mistakes, are you?

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,717

    Re: Colon (:) is an OPERATOR

    Excel is well known to have bugs. However, sometimes something that you are absolutely certain is a bug turns out to be a feature.

  8. #8
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,412

    Re: Colon (:) is an OPERATOR

    I'm just dying to learn how =MOD(2^42,3) returning #NUM! is a feature when 2^42 is 4,398,046,511,104, well under Excel's 15 decimal digit precision limit, well within IEEE 754's double precision domain for modulus, and Intel/AMD processors have no problems with calculating up to 21023 | 3 correctly. Lotus 1-2-3 and Quattro Pro could handle @MOD(2^1023,3) correctly.

    Excel and its clones won't go beyond 15 decimal digits, but even so Excel should be able to handle =MOD(2^49,3) since 2^49 is 562,949,953,421,312.
    Last edited by hrlngrv; 02-01-2021 at 09:03 PM.

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,717

    Re: Colon (:) is an OPERATOR

    Valid point but please don't hijack this thread, which is about range references (I know it's your thread but let's stick to the title topic). Feel free to open another thread about problems with number handling.

  10. #10
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,412

    Re: Colon (:) is an OPERATOR

    My point was in response to your reply that some bugs are features.

    Some of those features have been around since Excel 1 on 512K Macs in the mid-1980s.

    That Excel in Edit/Enter modes highlights A4:B3:C2:D1 as multiple areas but calculates formulas referring to it as if it were A4:D1 or A1:D4 is a bug in the highlighting functionality unless you could explain how it's a feature. I choose this new example for a reason. Enter ={"a","b","c","d";"e","f","g","h";"i","j","k","l";"m","n","o","p"} in A1:D4. The formula =CONCAT(A4:B3:C2:D1) returns abcdefghijklmnop, while =CONCAT((A4:B3,C2:D1)) returns #VALUE!. Either in Edit/Enter mode highlights A3:B4 and C1:D2 as separate areas, but Excel's calculation engine handles them quite differently. Whatever: inconsistency implies a bug most definitely does exist.

    Also, at the right of hijacking this thread again (I view it as tangential, so quasi-related digression rather than truly a separate topic), interesting that CONCAT can't handle multiple area ranges, but can handle 3D references like Sheet1:Sheet2!A7:B8. Since NPV can handle multiple area ranges as single arguments, this implies CONCAT (and TEXTJOIN) use different iterators than NPV, which seems completely asinine to me.

  11. #11
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,261

    Re: Colon (:) is an OPERATOR

    @hrlngrv
    : - is range operator
    Range operator, which produces one reference to all the cells between two references, including the two references.
    =CONCAT(A4:B3:C2:D1) - is the same as =CONCAT(A1:D4) and it work in spite of two different areas is highlighted.
    =CONCAT((A4:B3,C2:D1)) - i suppose the function expect one array but not array of arrays as arguments.
    Last edited by BMV; 02-28-2021 at 07:44 AM.

  12. #12
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,717

    Re: Colon (:) is an OPERATOR

    Quote Originally Posted by BMV View Post
    =CONCAT((A4:B3,C2:D1)) - i suppose the function expect one array but not array of arrays as arguments.
    It can use a list of ranges, not an array:

    =CONCAT(A4:B3,C2:D1)
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  13. #13
    Registered User
    Join Date
    04-05-2021
    Location
    Alger
    MS-Off Ver
    10
    Posts
    2

    Re: Colon (:) is an OPERATOR

    Hi, I find it interesting thanks for the explanation.

  14. #14
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,412

    Re: Colon (:) is an OPERATOR

    Quote Originally Posted by BMV View Post
    . . . =CONCAT((A4:B3,C2:D1)) - i suppose the function expect one array but not array of arrays as arguments.
    Semantics: (A4:B3,C2:D1) isn't an array of arrays, nor an array of ranges; it's a (single) multiple area range. It should be 2 disjoint 4-cell (2x2) blocks of cells. Apparently neither CONCAT nor TEXTJOIN can handle multiple area ranges, which means for some truly inexplicable reason the Excel developer team uses a different iterator for CONCAT and TEXTJOIN than it uses for NPV. NPV can handle multiple area ranges and 3D references, and NPV is order dependent like CONCAT and TEXTJOIN, so there'd seem to be no good reason CONCAT and TEXTJOIN couldn't use NPV's iterator other than an unspoken policy on MSFT's part to discourage use of multiple area ranges. Maybe some day in the distant future MSFT will muster the courage and decency to deprecate multiple area ranges formally.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Colon
    By HACKSAWCATS in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-17-2015, 07:15 AM
  2. Colon
    By HACKSAWCATS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2015, 05:20 AM
  3. Replies: 1
    Last Post: 08-23-2013, 05:45 PM
  4. How to add semi colon : ?
    By OceanBlue in forum Excel General
    Replies: 4
    Last Post: 04-27-2011, 08:25 PM
  5. Excel 2007 : Remapping END key to a colon
    By Spike0907 in forum Excel General
    Replies: 0
    Last Post: 03-01-2011, 06:52 AM
  6. Random colon delimitation help - Remove colon delimiter?
    By ebahr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2008, 03:33 PM
  7. inserting colon
    By xtrmhyper in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-01-2006, 02:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1