tag:blogger.com,1999:blog-3794737577784583819.post6484331970768803129..comments2022-11-18T00:44:27.613-08:00Comments on Excel Enthusiasts: The Intersect Operator! Bob DeLaMartrehttp://www.blogger.com/profile/03761089085420930697noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-3794737577784583819.post-34119323810379322702015-09-13T20:46:39.398-07:002015-09-13T20:46:39.398-07:00I too have just found this underused operator. I u...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.<br /><br />Occasionally you need to use the value in a previous row or the next row.<br /><br />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)<br /><br />This. = R:R<br />Next. = R[+1]:R[+1)<br />Prev. = R[-1]:R[-1)<br /><br />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 <br /><br />=sqrt((This. Lat-Prev. Lat)^2+LL*(This. Long-Prev. Long)^2)<br /><br />where LL is a named Lat/Long scale factor<br /><br />This concept has revolutionised how I think about my Excel coding.<br /><br />Only thing I am not sure of - is this efficient in terms of the computation?<br /><br />Bob JordanBBobJordanBhttps://www.blogger.com/profile/16672866154584077126noreply@blogger.com