A little yak shaving mayhaps?

Like a typical geek, I tend to gravitate towards finding elegant solutions to menial tasks. Take for instance, something I had to do today: consolidate a column of hundreds of entries. I need a single column that was every 20th entry of the original column. Now, I could just hand-enter the skipping of each 20th row in my cell references (in Excel of course), or I could shave the beast of the yak and figure out how to do it in Excel functions.

Low and behold, when you must grab every 20th cell, with an initial offset of the beginning of that cell's row, and put it into your cell with an offset position:

=-INDEX('run1'!$1:$6000,(CELL("row",D4)-CELL("row",D$4))*20
+CELL("row",'run1'!$BN$2),66)

Perhaps not 'cleaned up', but it does the job. So I can drop that in the head of my new row and just drag out the box to have Excel 'propagate' the rows and fill 'em up with yummy data.

About this article

written on
posted in Programming Back to Top

About the Author

Andrew Turner is an advocate of open standards and open data. He is actively involved in many organizations developing and supporting open standards, including OpenStreetMap, Open Geospatial Consortium, Open Web Foundation, OSGeo, and the World Wide Web Consortium. He co-founded CrisisCommons, a community of volunteers that, in coordination with government agencies and disaster response groups, build technology tools to help people in need during and after a crisis such as an earthquake, tsunami, tornado, hurricane, flood, or wildfire.