Extracting selected data with array functions.

Posted on 16/06/2012 by dougaj4

This post is in response to a query at the Eng-tips forum asking how to extract selected data from a range, so that the three best scores for each individual could be extracted from a two column Excel table listing multiple individuals, each with several different scores.

My response was based on an article by Chip Pearson at: http://www.cpearson.com/excel/ArrayFormulas.aspx

If the names are in B6:B105 the scores are in C6:C105 and a selected name is in E6 then:
(IF($B$6:$B$105=$E6,$C$6:$C$105,FALSE)
will return an array of 100 items containing either a score for the selected name or FALSE.

You can then wrap the Large() function around that:
=LARGE(IF($B$6:$B$105=$E6,$C$6:$C$105,FALSE),F$5)
where F5 contains the rank you want.  An example is shown below, listing the three best scores of Thomas, Richard and Harold:

Array Function Example

Note that because this is an array formula it must be entered by pressing “Ctrl-Shift-Enter”. If you just press “Enter” it returns 0.

The working of the formula is shown below, with the results of the formula:
(IF($B$6:$B$105=$E6,$C$6:$C$105,FALSE)

Array of scores for Thomas

Note that the array includes one entry for every row of the original table, containing either a score for Thomas, or FALSE if the row is data for another person.  This is not a problem for use with the Large() function, but if a continuous list of scores for reach individual is required then further work is required.

In many cases simply sorting the table, using the Excel filter functions, or using a pivot table would be the easiest way to go, but if an automatically updating array formula is required it is possible to wrap some additional functions around the solution presented above, such as that shown at The Get Digital Help blog (modified version shown below):

=IF($E$5=$B$5:$B$104,ROW($C$5:$C$104)-ROW($C$5)+1,FALSE)
will return an array of row numbers for rows containing scores for Thomas, with FALSE in the other rows.

=SMALL(IF($E$5=$B$5:$B$104,ROW($C$5:$C$104)-ROW($C$5)+1,FALSE),ROW(B1:B99))
Extracts the row numbers for Thomas from this array, without the FALSE values, and:

=INDEX(C5:C104,SMALL(IF($E$5=$B$5:$B$104,ROW($C$5:$C$104)-ROW($C$5)+1,FALSE),ROW(B1:B99)))
extracts the actual score from those row numbers.

In this case the first approach is not only much shorter, it is also much more convenient because before the Large() function can be used on the array returned by the second method the length of the array needs to be determined.

Once again, remember all the formulas presented here must be entered as array formulas.  See Using Array Formulas for more details.  Failure to enter as an array will sometimes result in an error message, but can return incorrect values, so beware.

Edit 19th June 12:  The comment from oli below is worth a closer look.  The basic technique is to replace the IF statement with an array multiplication:

($B$5:$B$104=E5) returns an array of TRUE and FALSE values.  When these are multiplied by the values in C5:C104 it returns an array of the values in column C or 0 (since TRUE and FALSE are treated as equal to 1 and 0 in numerical calculations), and then the Large function may be used directly on that array.  This is a very powerful technique, with many applications, the only proviso being that it is not suitable where the data may include 0 values, since it will be impossible to differentiate between this value and the values that do not match the selection criteria.

The other difference is that the Large function has also been applied as an array formula, so that rather than array entering the formula in a single cell and copying it to the two adjacent cells it must be array entered with the three adjacent cells selected.  It will then return the top three scores for the chosen person.

Posted in Arrays, Excel | Tagged , | 5 Comments

What have the Romans ever done for us?

Posted on 15/06/2012 by dougaj4

Maybe the Romans can  fairly claim the credit for spreading the aqueduct from Portugal to the Middle East, but the voussoir arch, typical of Roman aqueduct construction, did not originate with them.

The first known voussoir arches were constructed by the Etruscans who occupied the regions north of Rome (including Tuscany, western Umbria and northern Latium) from before 700 BC to 264 BC, when they were absorbed in the Roman Empire.

There are at least two surviving Etruscan arches in Perugia, the most well known being the Augustus or Etruscan Arch, dating from the 3rd century BC.

Arch of Augustas, Perugia

Arch of Augustus, Etruscan Arch

The other is the Porta Marzia, also dating from the 3rd Century BC.

Porta Marzia

Porta Marzia

Posted in Arch structures, Bach, Historic Bridges, Newton | Tagged , , , | 1 Comment

Return of Excel – Evaluating commas

Posted on 14/06/2012 by dougaj4

The Newton/Galileo posts here will continue with their Italian theme for some time yet, but since I am now back in Australia it’s time to give Excel a look in again.

I recently (well a couple of months ago) had a query about using the Eval User Defined Function (UDF) when the decimal separator was set to a comma, rather than a decimal point.  In response to that I added an option to the code for Eval and Evala that would replace all occurrences of “,” with “.” and “;” with “,”.  This allows the Excel Evaluate command to operate correctly, regardless of how the values are entered.

The revised spreadsheets using these functions may be downloaded from:

Note that the modification has as yet only been made in the Eval and EvalA functions, and not the integration functions.

After modifying the code I discovered that there is an Excel option that allows the original functions to work, without substituting commas for decimal points.  In Excel 2010, under File-Options-Advanced, deselect “Use System Separators” and enter a “,” for Decimal Separator, and a space for Thousands Separator.

System separators options

The spreadsheet will now display decimal numbers with a comma separator, and will only accept a comma as the separator for new numbers, but the Eval functions will work correctly.

However – this (strangely) only seems to work if the region language is set to English.  If the region is set to French (or presumably any other region that uses the comma separator) the original Eval functions no longer work.  I have only given this limited testing (since I normally use the decimal point), so if anyone else has any comment on how the regional separator options work in Excel, please leave a comment below.

Posted in Excel, UDFs, VBA | Tagged , , , , , | 6 Comments

Seven Florence Bridges

Posted on 08/06/2012 by dougaj4

There are seven bridges over the River Arno in the central area of Florence, of which only one (The Ponte Vecchio) is an original old structure, the remainder of the old bridges having been destroyed by the retreating German army at the end of World War 2.  Of the new bridges, 2 are replicas of the original structure and 4 are of new design, but all are of interest.

5 bridges over the Arno, looking downstream with the Ponte Vecchio in the foreground.

The first and last of the seven structures are reinforced concrete arch bridges:

The Ponte di San Niccolo is an elegant single span designed by Riccardo Morandi and constructed between 1947 and 1949.  It replaced a steel suspension bridge built between 1836 and 1837.

Ponte di San Niccolo

The structure has wide vertical cracks in the spandrel walls at both abutments, possibly due to thermal movements:

Crack as northern abutment

The Ponte Alla Vittoria is a 3 span bridge with flat concrete arches. The original bridge on this site was also a steel suspension bridge.  The new bridge has an unusual strongly shaped  deck, merging smoothly into the concrete piers:

Ponte Alla Vittoria

Ponte Alla Vittoria

The next bridge from the East is the Ponte alle Grazie, completed in 1953, replacing a 9 span arch bridge originally built in 1227 and rebuilt in 1345.  The new structure is a 5 span reinforced concrete arch structure, but its form is clearly based on that of the adjacent Ponte Vecchio, especially in the relationship of the deck and piers:

Ponte alle Grazie

The design of this bridge was subject to a competition, with some controversy about the use of reinforced concrete rather than traditional materials, described (in Italian) at: Ponte alla Grazie.

Ponte alle Grazie

The remaining reinforced concrete bridge is the Ponte Amerigo Vespucci, which is described in the Wikipedia article as an arch, but is in fact a three span prestressed concrete structure:

Ponte Amerigo Vespucci

The bridge construction was completed in 1957, replacing a temporary structure completed in 1949. Further details of the bridge design and construction are given (in Italian) at: Ponte Amerigo Vespucci

The two new bridges downstream of the Ponte Vecchio are both replicas of the original structures at their sites.  The Ponte Santa Trinita is described in the Wikipedia article as the oldest elliptic arch bridge in the world, the previous bridge having been constructed in 1567 to 1569, replacing bridges built in 1252, 1333, both of which were destroyed by floods.

Ponte Santa Trinita

The Ponte alla Carraia is a 5 span segmental arch bridge completed in 1948.

Ponte alla Carraia

Finally the sole remaining old bridge structure is the famous Ponte Vecchio.

Ponte Vecchio

Known more for the gold shops that still line the bridge than for any structural innovation, it is nonetheless an early example of a flat segmental arch construction, having been completed in 1345 with a maximum span of 30 m and rise of 4.4 m.

Ponte Vecchio

Posted in Arch structures, Historic Bridges, Newton | Tagged , , , , , , , , | 3 Comments

A tour of Venice backstreets

Posted on 02/06/2012 by dougaj4

The great majority of the tourists in Venice are concentrated within a stone’s throw of The Grand Canal and St Mark’s Square, but the real charm of the place is in the back streets and canals, where (apart from the occasional gondola jam) the tourists are relatively rare.

Here is a tour featuring (mainly) a  random selection of the many unsung bridges of Venice.

This slideshow requires JavaScript.

Posted in Bach, Historic Bridges, Newton | Tagged , , | 1 Comment