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

Originally Posted by 6StringJazzer

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

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

Originally Posted by hrlngrv

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

Further evidence from VBA:

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

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

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

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.

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.

Hi, I find it interesting thanks for the explanation.

Originally Posted by BMV

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