The syntax is simply: = RangeName1 RangeName2 (Note: No brackets or commas…) Now, you can do this by using the generic names of the ranges (i.e. =B2:B34 B10:H10), but that is a bit clumsy (and who needs Clumsy…).
A much better way is create Named Ranges. This can be easily done by Highlighting your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3. This will bring up a dialogue box as shown below (Click OK). Very Cool!
Once you have your ranges Named, you can then (as with the example below) find a value with the elementary (my dear Watson) function: =East Quarter3
But That’s Not All! You can combine the useful Intersect Operator with statistical functions to find even more information! For instance, if you wanted to find the overall average quarterly sales for the “East” region, you could use: =AVERAGE((East Quarter1):(East Quarter4))
Yes, there are other ways of doing this in Excel, but any Excel Geeks worth their pocket protectors should have the Intersect Operator in their bag of tricks.
All the best!
1 comment:
I too have just found this underused operator. I use it to work in non table aras where each row is an entity and different columns contain data or calculated values across that row.
Occasionally you need to use the value in a previous row or the next row.
So define names to be (I use RC notation but you can do it in A1 format by noting whecih row is selected when the name is defined)
This. = R:R
Next. = R[+1]:R[+1)
Prev. = R[-1]:R[-1)
Now if you have a column of latitudes called Lat (=$C:$C for example) and a column of longitudes called Long (say $D:$D) you can calculate the separation of adjacent points using
=sqrt((This. Lat-Prev. Lat)^2+LL*(This. Long-Prev. Long)^2)
where LL is a named Lat/Long scale factor
This concept has revolutionised how I think about my Excel coding.
Only thing I am not sure of - is this efficient in terms of the computation?
Bob JordanB
Post a Comment