Intersection Referene Operator

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I've just been surprised to come across reference to the <space>
intersection reference operator. Surprised because I've used Excel for
years and consider myself reasonably advanced but have never come across
this before, i.e SUM(A1:A6 A5:A10).

I've been trying to think of a useful application for this functionality
though and haven't thought of anything.

Can anyone tell me of a time when they've found this of use, or any
scenario where it could be useful.

Many thanks.....Jason
 
Think of a table: column headers Jan , Feb, Mar, etc,; row headers North,
South, East, west (store branches, or whatever); numbers in the columns
being sales in each month.

I have select the table and used Insert | Name | Create. So B2:L2 are the
North stores annual sales, D2:D5 are March sales

I want Mar sales from the East store: =Mar East or =East Mar

best wishes
 
Hi Jason,

I tend to use this a lot.
One simplified example:

You have a table in which you show the development of a financial fund.
It starts with a lump sum in row 1, cell 1 (A1)
The next cell, on the same row, (B1) shows any monthly payments
The next cell, same row, (C1) shows the premium for risk insurance to be deducted
The last cell on that row (D1) shows the balance at end of month (start balance + payment - risk premium)
The first cell of the next row starts with the balance of the row above.
The formula: =D:D 1:1

I can now copy down the rows as far as I need. Each first cell in a row will copy the end balance of the row above.

I should add that I tend to use the R1C1 reference style and named columns. This combination lends itself very much to using
intersection: the formula for the first cell in a row would be

=End_Balance R[-1] which in my opinion quite clearly describes what is meant: the end balance of the row above

There are many more situations where the intersection operator is useful. There is also the concept of implicit intersection for
intrinsic Excel functions, which does not function (anymore) for User Defined Functions. In those cases on can use explicit
intersection using the intersection operator. Let me know if you really want to know......

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| I've just been surprised to come across reference to the <space>
| intersection reference operator. Surprised because I've used Excel for
| years and consider myself reasonably advanced but have never come across
| this before, i.e SUM(A1:A6 A5:A10).
|
| I've been trying to think of a useful application for this functionality
| though and haven't thought of anything.
|
| Can anyone tell me of a time when they've found this of use, or any
| scenario where it could be useful.
|
| Many thanks.....Jason
 
Fri said:
You have a table in which you show the development of a financial fund.
It starts with a lump sum in row 1, cell 1 (A1)
The next cell, on the same row, (B1) shows any monthly payments
The next cell, same row, (C1) shows the premium for risk insurance to be deducted
The last cell on that row (D1) shows the balance at end of month
(start balance + payment - risk premium)
The first cell of the next row starts with the balance of the row above.
The formula: =D:D 1:1

I can now copy down the rows as far as I need. Each first cell in a
row will copy the end balance of the row above.
Click to expand...

I must be missing something. What advantage does that offer over the
plain =D1 ?
 
None. The advantage comes if you use named areas as described later.
But the examples RagDyer gave may illustrate things better.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Fri, 7 Dec 2007 21:44:11 +0100 from Niek Otten <[email protected]>:
| > You have a table in which you show the development of a financial fund.
| > It starts with a lump sum in row 1, cell 1 (A1)
| > The next cell, on the same row, (B1) shows any monthly payments
| > The next cell, same row, (C1) shows the premium for risk insurance to be deducted
| > The last cell on that row (D1) shows the balance at end of month
| > (start balance + payment - risk premium)
| > The first cell of the next row starts with the balance of the row above.
| > The formula: =D:D 1:1
| >
| > I can now copy down the rows as far as I need. Each first cell in a
| > row will copy the end balance of the row above.
|
| I must be missing something. What advantage does that offer over the
| plain =D1 ?
|
| --
| Stan Brown, Oak Road Systems, Tompkins County, New York, USA
| http://OakRoadSystems.com/
| "If there's one thing I know, it's men. I ought to: it's
| been my life work." -- Marie Dressler, in /Dinner at Eight/
 
RagDyer said:
Here's a couple of links to threads which have scenarios where I thought the
intersection operator could be of use and perhaps easier to use then some of
the formulas suggested by the other responders:

http://tinyurl.com/27udv2

http://tinyurl.com/2cm8ds
Click to expand...

Totally agree. In the 1st scenario, the intersection operator is much
simpler than the first convoluted IF OFFSET MATCH formula.

Now I'm thinking about it I can definitely see some applications -
especially with named ranges,

Thanks for the posts everyone...definitely food for thought!

Regards....Jason
 
To add to your "food-for-thought", this describes how it's *not* even
necessary to use the *outer* columns and rows as references.

Even internal (to the entire datalist) columns and rows can be used as
references.

http://tinyurl.com/2r3y66

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

RagDyer said:
Here's a couple of links to threads which have scenarios where I thought
the
intersection operator could be of use and perhaps easier to use then some
of
the formulas suggested by the other responders:

http://tinyurl.com/27udv2

http://tinyurl.com/2cm8ds
Click to expand...

Totally agree. In the 1st scenario, the intersection operator is much
simpler than the first convoluted IF OFFSET MATCH formula.

Now I'm thinking about it I can definitely see some applications -
especially with named ranges,

Thanks for the posts everyone...definitely food for thought!

Regards....Jason
 
Back
Top