Colon (:) is an OPERATOR

[hrlngrv]

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).

[6StringJazzer]

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.



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.

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.

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.)

[hrlngrv]

. . . 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.

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 :.

[_Borat]

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

[6StringJazzer]

Further evidence from VBA:

Please Login or Register  to view this content.

[hrlngrv]

. . . 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.



. . .

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?

[6StringJazzer]

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

[hrlngrv]

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 2¹⁰²³ | 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.

[6StringJazzer]

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.

[hrlngrv]

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.

[BMV]

@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.

[6StringJazzer]

=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)

[wofeye]

Hi, I find it interesting thanks for the explanation.

[hrlngrv]

. . . =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.