XY Chart problems – 1

Posted on 12/06/2020 by dougaj4

I have looked at problems with XY charts displaying as Line charts previously at:
When is an XY chart not an XY chart …? and
Plotting Charts With Gaps …

The charts shown below. and also in the next post, can be downloaded from:
XY chart bug.xlsb

This post summarises problems associated with section of the plot range, in response to an Eng-Tips question, regarding problems plotting an XY chart with two rows of data.

This is an example of an Excel bug that has been there for ever, and for some reason many people don’t even see it as a bug. You want to plot an XY graph (known to Excel as a scatter graph, even when displaying the data as lines), but if Excel sees any hint that the data is actually a “line graph” (i.e. the X values are not plotted to scale, but with equal spacing for each point), it will convert it to a line graph for you without asking. Also if the number of columns is equal to or greater than the number of rows it will treat the data series as being in rows rather than columns. The screenshot below illustrates these problems:

The top left graph was generated by selecting a “Line Chart” as the graph type, with range C4:D6 selected.

Top right had the same selected range, but I selected “Scatter (XY)” as the graph type. It has converted it into a Line Chart, but with the series names converted into sequence numbers (but with values displaying as decimals rather than integers!).

For lower middle I selected range C5:D6 (i.e. without the header row) and chose Scatter (XY). This has now plotted as an XY graph, but with the data in rows rather than columns.

For lower right  I selected C5:D7 and Scatter. The data is now plotted correctly, but it has decided that the text in cell D4 is the graph title.

For the lower left chart I selected C5:D6 and chose Scatter (XY), then reselected the data. If you don’t select the header rows when you generate the chart it leaves it as an XY graph with one data series, and you can re-select by drag and drop, or through the select data menu (if you prefer).

Posted in Charts, Charts, Excel | Tagged , , | 1 Comment

For Bert Jansch – Around the World in 80 Plays

Posted on 30/05/2020 by dougaj4

I have been listening to Pierre le Bras on YouTube for some time, where he plays under the name of Old Picker.  Today a link from there led me to For Bert Jansch , hosted by The Bert Jansch Foundation:

Musicians around the globe are saluting the legendary guitarist and singer song-writer Bert Jansch who would have turned 75 in November 2018. A special guitar is travelling across continents from artist to artist, enabling them to connect with the man and his music.

Each chosen artist is playing something Bert-inspired. It might be a piece they have specially created, or their version of a Bert song – or something of their own that resonates with Bert’s music or his ecological and social concerns. Each musician sends their video here, to the Bert Jansch Foundation, before choosing the next artist who will receive the travelling guitar. Bert’s light is still riding high – as evidenced by these amazing artists and their tributes.

I have selected two examples; click on Around the World in 80 Plays for many more.

Anne Briggs (about whom Richard wrote his iconic song “Beeswing”) taught Bert Blackwaterside. Richard says he first heard it played by Bert in a folk club and has since performed it throughout his career both solo and with colleagues such as Sandy Denny. He adds: “Bert could take the blues and jazz and traditional British folk music and blend those together into a style. He was also a great songwriter.”

Pierre calls himself ‘a lucky bum’ who has been able to make music his whole life. He bought his first guitar in 1961 when The Shadows were the craze – even in France! He was soon listening to American folk and blues music. Once he heard Bert and John Renbourn, he says everything else became redundant. He started transcribing straight away. He used to visit London 2 or 3 times a year in the ’60s to buy records and go to hear them at the folk clubs. Pierre spent a decade in the ’70s and ’80s in Arizona gigging in bars, coffee shops and hotels, before returning to France.

Posted in Bach | Tagged , , , , , , | 1 Comment

Building dances to an earthquake’s beat

Posted on 28/05/2020 by dougaj4

Adam Pascale from the Seismology Research Centre recently shared this animation (click on controls bottom right for High Quality and Full Screen views):

… it’s from a building in the Philippines about 130km (hypocentral distance) from a magnitude 6.1 earthquake in April 2019. There were three high resolution strong motion accelerographs (SMA-HR) installed in the building according to local government regulations for structural monitoring – one in the lowest basement, one in the middle floor, and one below roof level.

My colleague and fellow SRC seismologist Santi (Juan Santiago Velasquez) has done a great job at visualising this data in an animation where the horizontal motion of each SMA-HR has been superimposed on top of each other to see the relative movement of each monitoring point. Hopefully you can view the attached video file.

The blue dot represents the SMA-HR in the lowest basement (6 levels below ground level), the green dot the instrument on the 9th floor, and the red dot the instrument on the 18th floor. The data has been converted to displacement and the plotting scale is millimetres, so we’re not talking a lot of motion, but it’s still very clear because of the use of low noise sensors. These dancing dots are an accessible and entertaining way to use seismic data to visualise the synchronised movement of a building during an earthquake.

Posted in Animation, Computing - general, Newton | Tagged , | Leave a comment

More from Malinda …

Posted on 16/05/2020 by dougaj4

… and some of her mates, from here and there:

Posted in Bach | Tagged , , | Leave a comment

New sort functions and sorting to groups

Posted on 16/05/2020 by dougaj4

I first posted a user defined function (UDF) to sort spreadsheet or VBA data back in 2009.  Finally Microsoft have caught up, and Excel 365 subscribers now have two built in sort functions available:

=SORT(array,[sort_index],[sort_order],[by_col])
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

As an example of using the new functions I have looked at options or sorting a list into groups from Eng_Tips:

I have a long list of unique data in excel in the following form. As a first step I removed all the duplicates.
The unique headings always start with a “$_”. There are many more unique headings in the list other than GRIDS and MATERIALS.
I would like to filter the Original List, such that each data entry under a unique heading is merged under a single heading.

The examples shown below, including open source VBA code, can be downloaded from:
GroupSort.xlsb

The screenshots below show two options for performing this sort, using the Sort and Sortby functions:

The data to be sorted consists of group names, preceded by a $, followed members of the group.  To sort the data into continuous lists for each group requires a “helper column” showing the current group name for each row (see cell B4).  The data can then be sorted first by Column B, then Column A.  The sort function returns both columns, and every instance of the group name (and also every instance of group members, if these have been duplicated).

Tidier results can be returned using Sortby:

The Sortby function returns only the data to be sorted (specified in the first argument), and allows any number of “sort by columns”.  In this case Column B then Column A are specified.  The Unique function has been added to remove all duplicate elements.

For those who don’t yet have the sort functions, and to avoid the need for a helper column, I have written a GroupSort UDF, as shown below:

By default, GroupSort returns a 2D array, with one column for each group.  Setting the optional “ReturnArray” argument to False returns a single column, with duplicate group names removed.  Full open source code is included in the download file.

Another example using the Sortby function is shown below, to sort a list of names by the second name.  In this case the formula FIND(” “,A48:A71)+1,6) returns an array of the second names, which can be used as the data to sort on, without needing a helper column:

 

Posted in Excel, UDFs, VBA | Tagged , , , , , | Leave a comment