Recently a comment pointed out that if the ConBeam, or ConBeamU, user defined functions were used with a single span then the “Out” argument was ignored, so it was not possible to return support reactions. This has now been fixed and the new version can be downloaded from: ConBeamU
For more details of ConBeamU and the related functions see:
Continuous Beam Spreadsheet – with Units
yields circularity when preceded by its quotation.
Today’s post takes us on a little circular tour of the internet, starting with:
Grumpy Old Programmer (Mike Woodhouse) who returns from a long holiday from blogging to publish a nice little routine to generate a circular optical illusion:
He refers to the (very compact) code as being “golfed”, with a link to Programming Puzzles and Golf code, where I was introduced to the idea of a computer programming “quine”, that is computer code, which when run reproduces itself in full. A very brief example in Python is shown below (line 1 is the code, the second line the output).
But I can do better than that, using the “Classic” Lotus 123 macro script. If we enter in cell B1: /C~{D}~
and give it a range name, starting with a backslash, say \Q, then press CtrlQ, in cell B2 appears: /C~{D}~.
We have a 7 character long quine. Now if we add a {D} on the end, the code now copies itself down a row, then moves down to the next line, where it reads and carries out any code it finds there, resulting in a second copy, and a second move down, and so on, until the end of the universe, which in the case of this no longer brief computer code, is row 65536 of the spreadsheet:
All this talk of Quine and quining reminded me of Douglas Hofstadter, and a search on his name led me to xkcd 917:
Which completes the circular tour with a link back to this blog, wherein a work of Douglas Hofstadter is reduced to not 6 words, but a single two letter word:
Hope you enjoyed the trip.
Shortly after I wrote about extracting numbers from text strings, Winston Snyder at dataprose.org wrote a detailed article about using “regular expressions” to separate text from numbers in any string. I have adapted his routine for the same purpose as in the previous post, that is to extract a single numerical value from a text string. The regular expressions approach has two main advantages:
The only drawback is that if the text string contains more than one number the function will concatenate them if they are integers, or return zero if they both have decimals.
The new ExtractNum function has been added to GetNum.xlsb, and Text-in2.xlsb, and is shown in use in the screenshot below:
ExtractNum Function
This function is only scratching the surface of what can be done with regular expressions. For more details and links see dataprose.org.
A spreadsheet with User Defined Functions (UDFs) to dynamically sort a range of data has previously been presented here and here.
I have now modified the Python version of the UDF for improved functionality, added a second Python function, and added an example of how a dynamic sort can be accomplished without programming using the Rank() function. The revised spreadsheet, including full open-source code, can be downloaded from: Sortfunc.zip.
Sorting data in Excel can be accomplished most easily (since 2007) by inserting the data as a table. The data can then be sorted simply by clicking on the header of the sort column:
Table sorted on Column A
If you need a table that will automatically update when new data is entered, things are not so simple however. The screen shot below shows the procedure using the built-in Rank() function.:
This procedure requires 4 dummy columns to generate the required row index values, which are used in conjunction with the Index() or Offset() functions to return the data. Also note that this procedure cannot deal with two or more rows with exactly equal sort values, so the values are adjusted by subtracting different very small values from each row.
For situations where VBA is available, the VBA UDF shown below makes the whole procedure much easier and simpler: 
The only disadvantage of the VBA routine is that it only allows for one sort column. This has been fixed in the revised Python sort function shown below:
The options range, specifying sort columns and sort directions, may be any number of columns wide.
The code for this function is shown below:
from operator import itemgetter, attrgetter
@xl_func("var SortRange, var SortCol: var")
def py_Sort(SortRange,SortCol):
if SortCol is None:
return sorted(SortRange)
else:
numsortrows = 0
if type(SortCol) is list:
numsortcols = len(SortCol[0])
numsortrows = len(SortCol)
x = int(SortCol[0][0])-1
else:
numsortcols = 1
x = int(SortCol)-1
sortrev = False
for i in range(numsortcols-1,-1,-1):
if numsortcols != 1: x = int(SortCol[0][i])-1
if numsortrows > 1:
if SortCol[1][i] is None:
sortrev = False
else:
sortrev = SortCol[1][i]
SortRange = sorted(SortRange, key=itemgetter(x), reverse = sortrev)
return SortRange
A second Python function has been added, using the numpy argsort function for improved performance where there is only one sort column, and for use in other VBA and Python routines. Note that this function returns the row offset for the sorted list, which can then be used with the Excel Index() or Offset() functions:
The code for this function is:
@xl_func("numpy_array SortRange, bool RevSort: numpy_array")
def py_ArgSort(SortRange, RevSort):
if RevSort is None: RevSort = False
sortind = np.argsort(SortRange,0)
if RevSort == True:
indlen = sortind.shape[0]
revind = np.zeros((indlen,1))
indlen = indlen-1
for i in range(0, indlen+1):
revind[i] = sortind[indlen-i]
return revind
return sortind
For more details of using array functions, see the Using Array Functions and UDFs page.
For more details of installing and running Python from Excel, using the Pyxll add-in, see Installing Python, Scipy and Pyxll.
To show a bit of Southern Hemisphere solidarity with Jeff Weir (who has been getting a bit of flack for having the temerity to discuss non-Excel matters on an Excel blog), I’m bringing forward my next Bach instalment.
This post is guaranteed 100% Excel free.
Salthouse are a new Scottish group who have just released their first album, although the group members are all established musicians on the Scottish folk and jazz scenes. The first link is from their first concert, which looks like it was held in a rather small shoe box.
Update 28 Mar 2014: This song is based on a poem by Lord Byron, but incorporates a number of other works, as listed at Salthouse.bandcamp:
From Byron’s classic poem of 1814, mixed with a few words of prison poetry by Scottish / Australian Bushranger / Highwayman / Bankrobber James Alpin McPherson (1842-1895) finished off with 3 verses from Ewan’s pen.
Verse 1 & 2 – G.G. Byron
Verse 3 – James Alpin McPherson
Verse 4 – 6, chorus and music – Ewan MacPherson (MCPS & PRS)
‘Berneray’ – Lauren MacColl (MCPS & PRS)lyrics
She walks in beauty, like the night,
Of cloudless climes and starry skies,
And all that’s best of dark and bright,
Meet in her troubled eyes.Waves in every raven tress,
Softly lighten o’er her face,
Lost but lined as ever strong,
Smiles from days of goodness spent.And it’s down, down my lovely down
And it’s down, down my lovely down
And it’s down, down my lovely down
To the darkness deep and ever old.Never a stone will sound tonight,
Beneath my horse’s lonely tread.
His sire was of the purest race,
That ever yet was born and bred.Was not by silver stream we met,
Nor by rolling wave unseen.
I spoke she knew my only name,
I never gave it free or loud.Then as I neared her on the track,
Her eyes looked ever into mine,
And wild as only weather knows,
She stole my heart and I her life.So perfect my life shall never be,
And never a love can hold for me.
Whenever I look into the dark,
Her graceful form is near me still.
And here is the full text of the Byron poem:
She walks in beauty, like the nightOf cloudless climes and starry skies;And all that’s best of dark and brightMeet in her aspect and her eyes;Thus mellowed to that tender lightWhich heaven to gaudy day denies.One shade the more, one ray the less,Had half impaired the nameless graceWhich waves in every raven tress,Or softly lightens o’er her face;Where thoughts serenely sweet express,How pure, how dear their dwelling-place.And on that cheek, and o’er that brow,So soft, so calm, yet eloquent,The smiles that win, the tints that glow,But tell of days in goodness spent,A mind at peace with all below,A heart whose love is innocent!
The second piece is a “Setting Sun”, with a more spacious location:
Newton Excel Bach, not (just) an Excel Blog 