Arrays vs. Arrayformulae

I find myself having to defend my intense dislike of Excel's array formulae methodology. Since I’m no expert in this aspect of Excel, this consideration relies upon Chip Pearson’s advice.

In reality, most familiar Excel formulae use arrays like =SUM(A2:A4). In this case the formula instructs Excel to sum all the values in the array of cells {A2, A3, A4}. In fact the formula = SUM(A2, A3, A4)  performs exactly the same task as =SUM(A2:A4).  Excel is also quite happy mixing different forms of presentation such as =SUM(A2:A4, C5:C20, D1:D2). In short Excel is pretty clever at interpreting user requirements.

Array formulae are different and the user must give Excel a specific instruction for the software to handle the instruction correctly. This takes the form of curly braces around the formulae {} generated by posting the formula using Control, Shift and Enter.

Chip offers the example of {=AVERAGE(IF(A7:A13>0,A7:A13,FALSE))}. Interpret this in plain English as for each element in the array A7:A13, if it is more than zero include it is the average otherwise leave it out (FALSE). The curly braces are absolutely crucial to this interpretation. Here is the evaluation of this formula with the curly braces in place.

See cell B7 for the result 5.25

On the other hand the identical formula, if posted using Enter it delivers the following result. This is a standard average.

See cell C7 for the result 2.142857

Even more confusing, if you list the formulae in your worksheet by evaluating each cell for its formula you get the following. Here it looks as if the identical formula cell delivers different results, because formula is never listed with its curly braces.

To me this was sufficient to put me off ever using an array formula, but in reality this is the more simple methodology. 

You can use array formulae to deliver an array of answers. The spreadsheet below has identical formulae in each of cells in columns E {=ROW(A7:A15)} and similarly in column  F {=A7:A13}. However, because they are entered using the control, shift method each cell delivers a different answer appropriate to its position in the array.

 

Arrayformuylae answers delivered according to their position in the array

If, you list the formulae however it is not possible to tell that these have been array entered – as shown below. The user or a reviewer has to deduce the array entry methodology based on the number of identical formulae which deliver different answers.

In each of these cases the identical formula appears to delivers a series of different answers

In all other languages that I have used, it has always been immediately obvious when working with arrays. It simplifies matters both for the user and the auditor. 

No matter how technically clever Excel’s array formula methodology, I will always avoid it if I can.

Walking, talking and listening

Our morning walk is intended as exercise as well as pleasure. I confess the mere act of walking is a pleasure. I don’t need much else, but this is a joint venture. Bryony occasionally reminds me as I lurch in front of her. A little conversation between us is ‘de rigeur’.

The sidewalk out of Harleston down to the Needham roundabout, however is definitely single file. (Please excuse the Americanism but it is a much better description of a path beside a road, than the more conventional footpath.) The traffic coming up from the roundabout tends to put pay to any but traffic related conversation.

By the time we got to the turn off to Starston Lane, and I had set the lap time on my newly acquired all singing, all dancing sports stop watch, Bryony was mildly irritated. She showed this by walking behind me rather slowly. This is a sure sign. Bryony’s longer legs normally have me working hard to keep up with her on this gentle slope. This is a shaded shallow gully and we have to keep our ears alert for vehicles coming behind us. The gully opens out after a couple of hundred yards.

Inevitably, we keep on listening for vehicles; the articulated chicken shit trucks are very big indeed. But once out in the open we hear a lot more. My directional hearing is pretty naff and I don’t think Bryony’s is much better. So we can’t work out whether it’s from the road behind us or over the valley until it is fairly close. I determined to look at the layout in three dimensions using the Ordnance Survey maps contour lines.

Gully at the start of Starston Lane

The gully starts at the triangular junction between High Road, Needham Road and Starston Road on the map above.

By the time we reach the squiggly red cross, the gully has opened out and the sounds of vehicles are coming over from the Harleston bypass (marked A143 east of the roundabout). It is difficult to be sure of the exact direction from which the sound is coming. You will notice though, that there is a small copse of woodland and a dwelling surrounded by trees I suspect that this protects us from vehicle noise coming the bypass to the west of the Needham roundabout.

Once round the bend, the country on either side of Starston Lane opens up even further. This year the field to the west are being used by Wharton’s as rose nurseries while to the east the fields are planted to cereals (not rape this year thank goodness). We are both sure that we can hear traffic coming from both directions.

The map below, at a slightly larger scale, shows clear lines of hearing both from the east and the west along the A143. This is the case only once we’ve walked through the long right angled bend in Starston Lane and are heading along the straight towards Cranes Watering Farm.

Towards Cranes Watering Farm. Can hear traffic from both east and west.

Contour Lines

It is difficult to see contour lines from images of OS maps. This problem is exacerbated by the fact that contours are considered less important than other information on the map. Accordingly, if there is a road or a boundary of any sort that always takes precedence. It is not always easy to match up broken contours. 

Contour lines just about visible

Also the online version of the OS map does not appear to give any heights for its contours. The image above shows a series of contours, where I have marked in the heights values, taken from the paper version of the same area.

Paper version showing contour heights

I had to follow these contours through to Starston Lane, to mark the map images above. I still can't be sure that they are correct.

Tea time music at the Pennoyer Rooms

Friends can be extraordinarily generous. For the past 25+ years Sandra Holmes has loaned me one of her violins. Here is a review of a series of chamber groups who performed for a tea time audience at the Pennoyer Rooms, Pulham St Mary. Sandra performed with two of the groups.

The Pennoyer

Pennoyer Review

The Haydn Op 20 quartets are also known as the Sun quartets, as if they were the dawn of an entirely new genre of music making. Truth be told they weren’t entirely new but they did show off, for the first time, the sophistication of the string quartet form.

This set comprises 6 quartets and No. 4 is undoubtedly the most popular. It is a happy spritely quartet and needs to be played with speed and elegance. This came over well in the outer movements, although there were times when I wanted to push the tempo along. The slow movement is a series on variations which have some brooding elements. These need absolute harmonic clarity. The occasional fudged intonation meant that some of the underlying menace was lost, but the lighter variations came over very well.

The string sextet delivers a special sound to the listener. It is quite different to that of a quartet with a softer, more plangent delivery. It also means that audiences talk over it. I was not close to the players but the little I heard of the Teleman was very pleasing.

The Mozart “Grande Sestetto Concertante” took me by surprise, even though it was written into the programme. Here the medium of the string sextet really came into its own. This very familiar piece (as a violin/viola concerto) took on an entirely new life. It worked exceptionally well in the context of the background murmur of the audience. Next time I’ll get closer.

The oboe quartets seemed to take me back to a previous life. The Mozart oboe quartet was one of the very first long playing records my father bought in the 1960s. It was a delight to be reminded of those days. Even the Stamitz and Vanhal quartets were those that I had known and played (as a violinist) over two decades ago. These performances were well worth the wait.

In the Chapel end of Pennoyers, we heard Mozart’s “Kegelstatt” trio for the unusual combination of piano, clarinet and viola. Typically Mozartian, it is quite unique in the repertoire. The opening Andante is indeed beautiful, but mainly a vehicle for the clarinet to show off. Mike Bishop accomplished this admirably. I’m used to the minuet movement being light hearted, frothy and not too difficult. Not so here. The viola’s triplets are fiendish. Sandra handled these with real dexterity, but at the cost of a tiny reduction in tempo. The final rondo brought us back to normality. The players relaxed and let their hair down (metaphorically speaking of course).

The Schumann Fantasiestücke for clarinet and piano (Op. 73) was a complete surprise. The music was entirely new to me. It certainly gave Richard Donmall the chance to demonstrate the flexibility of his electronic piano. Both he and it were very good indeed and was Mike on clarinet. This is a piece of music I will certainly be acquiring in the near future.

For those interested, the full programme is listed below

While this has nothing specifically to do with a stroke, keeping oneself active and motivated is still part of the recovery process.

Improving balance - or not

I want to cycle safely and one part of that process has been to improve by balance post stroke. There are a number of web sites offering advice on improving balance - e.g. 10 Examples of Coordination & Balance Exercise and Improve Your Balance in 3 Simple Steps.

I tried the simplest version of the one legged balance test. The results are tabulated below,

As you cab see, I'm getting worse quite dramatically. Fortunately, negative time values are not permitted.

RSPB Minsmere

A day out with the grandchildren and a chance for me to test my distinctly limited skills with the new digital camera.


Mimsmere has a wide variety of habitats. The sand cliffs here are the perfect place for sand martins and their burrows. There were plenty of the birds about but I had no chance of catching them on camera.

Sand martin burrows

Catching Molly and Nuala at the right time is just as difficult. Molly was looking at the camera - honest.










Here Molly was supposed to be in mid jump - whoops - but there's Nuala!





Photographing birds presents its own problems. These were all taken with the telephoto close to maximum magnification.

Black-tailed Godwit

Heron at mid distance

Egret

I really should have taken a snap of some of the other twitchers and their cameras, where their telephoto lens were many times the size of the cameras themselves.


Eventually, I got some reasonable photos of Molly and Nuala.

Molly

Nuala - deep concentration

Granny keeps careful watch

Boudicca Way (Stage 2)?

Last weekend our house was full to the gunnels. My brother Russell was visiting for a couple of days and I decided that we two should escape the crowd. I had always intended to walk the Boudicca Way, a medium distance walk of nearly 40 miles. It breaks up fairly neatly into 4 sections. We would trial section 2.

Photographs of the relevant OS map sections below show the walk we should have taken below. In practice our planning went awry.

Pulham to Tyrell's Wood

Tyrell's Wood to Tasburgh

Fortunately we have public transport between Harleston and Pulham Market, which was the beginning of this stage. Perhaps our first mistake was to rely on maps downloaded from the web rather standard OS maps. In fact Russell had handwritten our route onto A4 sections of an OS style Map  For an example see below.

Hand written route onto OS map printed from the WWW.

It was a warm summer day and the first part of the route went smoothly. I even had the presence of mind to take a snap of a rather interesting wooden footbridge.

Russell looks back across the interesting wooden bridge

Woods and forests are always tricky to navigate and Tyrell’s Wood is no exception. The navigation was not made any easier by my attempts to avoid the extensive boggy patches, which formed after the recent heavy downpours. We re-joined the official route about half way through the length of the wood, I confess this was more by luck than judgement.

Once through the wood we met up with one of the myriad of small roads that meander the East Anglian countryside. We should have turned right off this after about 100 yards. We missed it. In fact we were looking left rather than right. By the time we had reached one of the many back roads into Long Stratton that I recognised, I knew we were wrong. Fortunately we were close to a junction with both roads named. There’s a rarity.  I was able to locate our position. We turned towards the authorised route.

Errors - missed one right and took a left before returning to the authorized route

Russell however was still not convinced I was right. I suspect the “little brother syndrome”. We found ourselves heading towards a wooded area on both sides with signed footpath heading left (north).

We turn left off Mill Road

This footpath was clearly marked. Inevitably we reached another. My guess was that turning left would lead to Long Stratton. We agreed that the right turn was a more profitable direction.

Short cut to the Rectory at Morningthorpe

So it turned out. Although we didn't know it the time we were back on the official Boudicca Way. Perhaps more to the point we turned left at the triangular junction, which meant that we were indeed heading for Tasburgh.

Our next error was fatal. We knew we had to turn right off the route and came across a likely candidate as shown below.

Should we have taken the lane to the right

This picture above has been taken from Google Maps. When Russell and I saw it there was a chain across the path with lots pretend notices on it. Nothing actually said that this was private property but the implication of the chain together with the fake notices suggested it was. Both of us looked around for any official notice to show us that this was indeed the official Boudicca Way. We found nothing and assumed that this was not the turning we were looking for.

We went along Brick Kiln Lane. There was no sign of the authorized route and by the time we arrived at Church (yet another route to Long Stratton) I was getting tired and hungry. The need for food overrode all other considerations. 

The Queen's Head, Long Stratton

The Queen's Head was very welcome, together with Fish and Chips from the chippy 20 yards down the road. I very rarely drink cider, but the pint of very dry cider from the Aspall's Brewery (brewed in Suffolk) was exceptionally welcome. Sadly the pub was due to closed its doors permanently the very next day. This part of English life seems to be disappearing.


It's surprising what refreshment can do. We decided to work out way back to Tyrell's Wood in the hopes of finding where we made our original routing error. Eventually we returned to the point on our route where we should have turned off. Perhaps it was not entirely our fault. The growing season this year has been has been warm and wet. Everything is overgrown and the sign post for the correct route was more or less invisible.

Obvious when you know it

Through Tyrell's  Wood we took yet another route. I still have no idea which the correct route is. Once through the wood we turned right (off Boudicca Way) and headed for tea and cakes at Goodies Farm Cafe.

Measuring distance on maps

There is no doubt that we made a lot of mapping errors on this walk, But I wanted to find how much of a difference it made to the distance walked, The first map below shows the main mistake and the correct route.

The next two images employ the Google Maps measuring tool to estimate the two distances.

The authorized route

Russell and Stephen's short cut

In fact, according to these measurements, we skipped about 2/3 miles. Also we didn't even attempt the last couple of miles to Tasburg. In our defence though, we did walk a further 3.62  miles from Long Stratton to Goodies Farm shop.

Long Stratton to Goodies

My guess is that we weren't far short of the original distance that we intended to walk.

Planning for Exercise

Life intervenes on any and almost every plan.

I am attending a series of meetings organised by the NDPS. These are designed to encourage and to provide pointers for improving your chances of avoiding type 2 Diabetes and becoming reliant on insulin. Our two guides through these meetings are both young and clearly active. The group (about 10 of us) are mostly overweight and in the age range 50 – 80. I say mostly, the group also includes a mother of two small children and a couple of older persons who are quite clearly not overweight. The group characteristics provides excellent material for statistical study.

In my case the interventions from life takes the form of family visits. We have had a very full house none stop for the past 5 months. Mostly, Bryony and I encourage each other to take an early morning walk or similar exercise (The Dog that Walked, The Lurch, Fatigue and the Lurch, Measurement, Walberswick (2016) and Girls, walks and maps). Even so plans do not always work out.

Yesterday, I had one of those NDPS meetings and I had taken almost no exercise since Saturday – see Boudicca Way (Stage 2). The whole point of these meetings is to encourage group discussion about ones successes and failures. I was determined that my contribution should not balance on the failure side rather than at least some success.

Getting to the meeting at the University of East Anglia campus from Harleston is something of a pain when using public transport. The buses contrive to get me there either way too early or just too late. I opted to visit Patrick (the OU rules are changing under the Tories - this was a chance to plan for 2016/17), then walk to UEA in time for the 13:30 start.

I’ve split the route up into two sections as shown below.

Patrick to Eaton

Eaton to UEA (Medical Building)

Sadly, this only works out at a walking speed of about 3 mph. Not quite achieving that level of aerobic exercise expected in the NDPS advice.