Using AND within a FILTER within an ArrayFormula in a GSheet

So I’m talking Google Sheets here, one of the types of available Google Docs. Sheets is like Excel – in many ways. It’s also different – in many ways.

So I recently had a situation where I needed to filter data from one sheet (tab) into another. In English it would have read:

Bring me in the 5th column, of all the rows from the sheet called “Raw Data”, where the 11th column has a date of either today or later and the 4th column says anything other than “TEST”.

Eventually and after a lot of experimentation, I ended up with the forumla above, which on the whole, is self-explanatory.

Except for that ‘*‘!

The FILTER function has two parameters:

What to filter:

'Raw Data'!E:E,

and the criteria to use:

('Raw Data'!K:K>=TODAY())*('Raw Data'!D:D<>"TEST")

And that ‘*‘ in the middle? Well it’s simply GSheet’s way of saying ‘AND’. (A ‘+‘ is used to mean ‘OR’).

In Excel you’d say something like:

AND('Raw Data'!K:K>=TODAY(),'Raw Data'!D:D<>"TEST")

And so we end up with:

=ArrayFormula(FILTER('Raw Data'!E:E,('Raw Data'!K:K>=TODAY())*('Raw Data'!D:D<>"TEST")))

 

Posted in Technical | Tagged , , , , | Leave a comment

The Yuletide Cacophony

Christmas MusicWe approach the festive period, sometimes with excitement, often with dread but always with the familiar realisation that some traditions never change. Even the things that some love to hate, would be missed if they made no appearance at all. Neither mince pies, sprouts or turkey are everyone’s favourites, but somehow their absence would just make Christmas all the poorer.

And so as the build-up to Christmas began, we were reminded again of another annual festive tradition that I look forward to, sometimes with dread, sometimes with apprehension but always with excitement at the prospect of being surprised; one way or another. Yes it was time for the Annual School Christmas Celebration Concert Service Festival thing.

This year we were treated to a fabulous mixture of traditional Christmas carols, traditional Christmas readings, that old festive favourite “The Phantom Of the Opera” (I kid you not!) an extremely (shall we say) “enthusiastic” brass band and the head girl using the phrase “shameless hussies” from the pulpit. To add to the theatre of the event, we had stage crew and lighting but more about these later.

The evening started with the very traditional “Once In Royal David’s City” with a beautifully voiced Year 7 treble singing the obligatory solo first verse from the West Door. Everybody of course knows the first verse, but we had to keep quite whilst the treble sang for his supper. And then it was our turn. With song sheets in hand, we filled our lungs, opened our hearts, some opened their mouths and we began to sing.

Midway through the four verses that were ours, the lighting person, added tremendously to the atmosphere, helping to create that Christmasy feel, by plunging us all into darkness, mid-verse. This luminary stroke of genius enabled that time-honoured traditional practice, portrayed on so many traditional Victorian Christmas cards, that of carol singers, huddled together and using their mobile phones as torches. What other creative strokes of genius could the lighting person pull out of the bag? The night was young, I was afraid. For surely he had more surprises for us, his lucky victims.

Now, you know when you sit in the theatre just a couple of rows in front of the cough sweet rustler, that person who is totally unaware that their infernal sweetie wrapper rustling is causing any annoyance, and who, despite your initial, most sympathetic of tolerances towards, you now end up simply thinking “JUST CHOKE AND DIE so we can all get back to enjoying the show”, well we were subjected to a similar scenario. For behind us was a balcony atop which a lady was clicking away with her camera. Actually double-clicking. It was quite dark at times, the church was quite long and she was trying to photograph. Needless to say the shutter was staying open (one click) for ages before closing again (one more click). After taking many many pictures, she decided she wasn’t near enough, then chose the quietest moments to descend the stairs in her very clicky heels (more double clicks).

As I mentioned, the evening’s musical pieces were interspersed with readings, some Biblical, other secular and here, I was introduced to a new phenomenon. At the end of the first reading (taken from Isaiah), the congregation or audience depending on your point of view, applauded. Yes, the school’s headmaster, was applauded for doing no more than reading out loud, from the pulpit, words that someone else wrote. This trend continued with every vocal interlude from the pulpit being applauded. (Later I was scowled at for attempting to applaud the vicar when he gave a blessing – where is the consistency?)

Anyway, as I say, there were many musical offerings from various sections of the school, some of these were gifts to our ears and some of them, well they all tried very hard. The string ensemble’s rendition of “Walking In The Air” was, shall we say, not rushed. I had not before perceived this piece as a potential funeral dirge, but their innovative use of dragging the bow as slowly as possible across the strings definitely gave this piece new meaning for me, which I was able to contemplate at length. At tremendously long length.

A little moment of joy for me, was possible almost unnoticed by many. As I have told you the assembled masses had decided to applaud each of the readings, a point which did not go unnoticed by one child, who simple ended her rendition with “Thank You” as the applause began. Smart kid! She is assured of a future in show business.

Now if dear reader you think that my perception of the evening was negative then you are entirely wrong. Some elements of the evening were a sublime delight – the school’s unique Acapella choir are a joy to hear.

One of my favourite pieces of the evening was a performance by the Junior choir of “When Christmas Comes To Town” from the film The Polar Express. Firstly they resisted the all-too-often performed underhand tactic of bringing in ringers – Senior choir members who are short enough to be taken for Junior members. On this occasion we were treated to a small number of delightful Junior voices, each perfectly crisp and audible from the back of the Church, bringing to life this lovely song which easily became one of the highlights of the evening. And for all the right reasons. Well done to them all.

This had been preceded by a lovely piece which I never get tired of hearing – the wonderfully irreverent and witty John Julius Norwich’s “Twelve Days Of Christmas” parody letter, read by the head girl and boy. There’s something wonderfully naughty about hearing someone say “hussies” from the pulpit.

I was a little disappointed with the speed of the event. The long delays between each piece were sometimes inordinately long. Cake would have been nice. The ‘crew’ would wait for one choir or whatever to clear off, then wait some more for the next choir or whatever to get into place and then begin to rearrange microphones etc. All of this took forever and could, with a few simple steps have all been a lot quicker. One of my pet hates in any show, is scenes changes that take too long and indeed I do sometimes train stage in how to do these things a lot quicker. Even a simple “Enter from stage right and leave by stage left” would have helped. Or use the time during readings to quietly and efficiently get the next piece set up. Or give the audience cake to eat whilst they’re waiting.

Altogether there were five “audience participation” carols, each one accompanied by a new lighting design. I use the word “design” loosely. These enthusiastic and instant lighting states were the results of giving someone unfamiliar with lighting design, two things: new LED lighting to play with and way too much coffee. Subtlety was not in his luminary arsenal, but primary colours certainly were and what’s more, he wasn’t afraid to use them. His favoured weapon of choice was to wait until a carol or musical piece had started. To lull us into thinking he’d gone to the pub and then, with neither a hint of shame, or sadly timing, he’d suddenly push all the LEDs to bright green or blue. Thus momentarily distracting us from the assault upon our ears, by assaulting our eyes.

But despite what you think I may have felt about the evening, it was still wonderful, still Christmassy and still a pleasure to ….. Oh Cake!! They brought out lots of cake at the end. Mince pies (obviously); Panettone (getting better); Stollen (things were really beginning to pick up) and CHRISTMAS CAKE!!!

The evening was a delight. And I have a whole twelve months to recover and forget about it before we repeat the experience again.

And I wouldn’t miss it for the world.

Merry Crimble.

Posted in Personal | Tagged , , , , | Leave a comment

I never claimed to be Byron

When the world conspires to spoil your day,
When the world wants to chide every word you say;
Raise a smile, raise your game, give a shrug and shout “Hey,
Nowt’s gonna bring me down on National Poetry Day”.

National Poetry Day, 6th October 2016

© James McCann (like anyone would want to steal it)!

Posted in Personal | Tagged , | Leave a comment

My default state is ‘shall_i_cook=no’. But then I had a beer!

I have never enjoyed cooking, I just don’t get it. But then I’m not really a creative person. I don’t paint, I don’t write great prose and I don’t really draw (this is about all I can manage):

So really it’s no great surprise that I don’t cook either. Sure I’ll grill bacon, make scrambled eggs and put all manner of things on toast, but that’s where I generally draw the line. I certainly don’t collect ingredients together and cook things from scratch. When left to my own devices, porridge has generally seen me through (and I have been known to eat cornflakes and rice krispies on occasion but that’s another story).

And to be fair I’m spoilt rotten at home, living as I do with a great cook. Which is handy since I’m a great washer-upper.

So last night I was left home alone and forced to rummage and fend for myself. I began to look around to see what there was. Randomly the first thing I came across was a bottle of beer in the fridge. I was hungry, beer is food. So I drank it.

My hunger was still not sated although my brain was now a little fuzzy (oh I don’t really drink either, so one beer and I’m …)

“I know, I’ll make a stew.”

“I beg your pardon?”

“I’ll make a stew.”

“Do you even know how?”

“How hard can it be?”

“But you’re drunk!”

“But that just means I’ll worry less about the outcome. Besides, what’s the worst that can happen? Let’s do this.”

“You’re nuts.”

“Hey, I’m not the one talking to myself!”

“Erm ……”

Vegetable StewSo I made a vegetable stew. It had potatoes, carrots, mushrooms, butter beans, red kidney beans, chopped tomatoes and (because I’d drunk some ale) a packet of Schwartz Slow Cookers Beef and Ale Stew mix (from when I worked there and they kept giving us loads of freebies) and an hour and a half later it looked like this:

Now, I tend to veer more towards the sciences than the arts and therefore my measurements were quite precise. There was:

  • ‘A very large handful’ of potatoes.
  • Of carrots there was ‘As many as I could hold in one hand’.
  • The beans, beans and tomatoes were easy, ‘One whole tin each’.
  • And hey a packet is a packet, I assume you’re just meant to put the whole lot in anyway.

At some point I noticed that the tin of kidney beans were in a chilli sauce, but hey, they were in now, it’ll all add flavour right?

The end result was a whole crock pot of stew which I’ll still be eating on Christmas day. But it does actually taste rather good.

“Told you.”

“Oh you’re back.”

“Of course. What’s for breakfast?”

“What do you think?”

Posted in Personal | Tagged , , | 1 Comment

When the stage manager sends in an understudy

Already Calm I'm The Stage ManagerSo I recently injured my back in a way which is not too serious (I hope) but it means I can’t really take any chances with it for a while either. Which means not really lifting anything and not camping on anything which isn’t as smooth as Anne Hathaway’s tummy.

Which meant I had to turn down a long-standing booking to stage manage some artistes including the Mediæval Bæbes at a summer jousting event lasting four days in all. It would have meant camping (on a rough forest floor) and lifting stuff on and off stage repeatedly. It’s an annual gig that I’ve worked before and I’m always delighted to be asked back.

But this year the sensible thing to do was to pass, rather than turn up and be useless, or worse, not be useless right up until the point that I was injured and then be a real nuisance.

I figured it was best that I give the organisers a brief run-down on what I actually do there so that they could draft someone in and give then a fighting chance.

It also occurred to me that Catherine was going to this event. She is normally next to the stage selling merchandise for the artistes, but when she’s not at this annual jousting event (or doing her actual proper job) she does know her way around a stage and is familiar with most of what I do when I’m SMing. Plus she has crewed on stage so is no stranger to the environment. So maybe, just maybe I should just send her in my place (I figured they could easily get someone else to sell DVDs and T-shirts).

She was convinced it was a difficult job. Until I gave her the briefing. This is the gist of it, see what you think:

What does the Woodland Stage stage manager do:

  1. The most important task is keeping everything running to time. This usually means rounding up the artistes in plenty of time, making sure the stage is set according to their needs (and swept) and getting them on on the dot of their allotted start time.
  2. It also means ensuring that they understand the importance of finishing their slot on time and being prepared to ask them to stop if they try and overrun.
  3. Timing is essential since large numbers of audience will want to move from the Woodland Stage area to the Jousting area and the timings allow for this, only if they are stringently adhered to.
  4. Preparing the stage can mean anything from just clearing everything off it and sweeping it, to a full set up for the Bæbes. With the Bæbes it is important to know what the differing requirements are for their two different sets (if doing 2). For instance different instruments in different positions. They will advise and help.
  5. Ensuring that the public and all those not associated with stage performances do not come back-stage. There is a dressing room in which artistes need to feel comfortable getting changed.
  6. The dressing room also need a certain amount of cleaning. It is particularly important to remove any leftover food or plates, drinks etc. to reduce the impact posed by wasps.
  7. Ensure that the dressing room is well stocked with water bottles which can be requested from control.
  8. Ensure the safety of performers whilst on or back stage. Pointing out trip hazards, making or requesting repairs when necessary.
  9. Be calm and soothe anxious performers. Help them resolve any problems as efficiently and as drama-free as possible.
  10. Ensure that all performers are thanked on leaving the stage and congratulated, praised etc. Performers egos can be delicate and your reassurance will be appreciated.

What does the stage manager need to have:

  1. If nothing else, they need a large easily visible accurate clock. I have one the prefect stage-manager’s clock which I sent with Catherine (or they can be picked from Argus for £20).
  2. A broom which can be supplied from the site crew. (Once acquired, never relinquish. The broom is power!)
  3. An up to date version of the running order, preferably written or typed out in large print and displayed near to the above-mentioned clock backstage.
  4. Notepad and pens. Things change. Messages will be left. Signs will need to be created on the fly. So have a portable stationery kit with lots of A4 paper and Sharpies.
  5. But most importantly the stage manager has to have an air of calm.

As soon as the event is over, I’ll add a note of how she did, but the first day has gone well, so I might well be out of a job!

Posted in Technical | Tagged , , | 2 Comments

Sleeping on a welcome bomb

Oxygen Under The Bed!So it’s been pointed out to me that I’m now basically sleeping on a bomb. This doesn’t worry me, in fact it’s rather comforting. Now before those lovely chaps in black jump out of helicopters and land on the roof I should perhaps explain.

Or perhaps not if it’ll give the neighbours in my road some excitement.

Oh all right, I’ve teased you enough with a tasty crumb and so you deserve a hearty mouthful.

You see as one or two of you know, I get some naughty little headaches now and again. They’re actually called “Cluster headaches” and they’re evil little buggers. Think of it as a migraine on acid, or if that metaphor doesn’t help, then how about a heavyweight boxing champion, trapped inside your head and using the back of your eyeball as a punchbag. That one generally works for me.

However a chance encounter many years ago with a tiny little baby doctor (she looked about 12 but I was sure she’d grow into her stethoscope one day) made me realise that oxygen was like a magic potion, giving almost instant results. Five minutes of breathing pure O2 was enough to relieve a pain that had gone on for days and was causing me to think seriously about head removal (which is of course pointless, since technically, one doesn’t chop of one’s head, one chops off one’s body. The head is still you. You’d go through the whole decapitation process and still have a headache). You see, I’d thought this through.

So anyway, all I needed to do was to get my GP to prescribe O2 and all would be hunky dory. Well apparently not. Some GPs think that they cannot prescribe O2. Some wonder if you’ve tried maybe taking an aspirin instead. Some like to ask if it’s tension. Some, unreassuringly reach for Google.

Eventually a few weeks ago I sat and chatted with a neurologist. I tried to convince him that they weren’t actually clusters because mine don’t really fit the standard pattern. He fired millions of questions at me and told me that they really were. And why wasn’t I using O2?

And then from out of the blue, just the other week, I was told it would be delivered the next day. No fuss, just wait in and a van will deliver two whopping great cylinders of the stuff. And it did. And once the first one is empty, I just phone or email and they’ll deliver more if I leave the empty outside.

Occasionally we have visitors come to stay and they use my bed. I think legally I’m now supposed to give them a Health and Safety briefing before bedtime. I foresee a PowerPoint presentation on the horizon.

Toodle pip.

Posted in Personal | Tagged , | 2 Comments

Time for just one more …

Tim Brooke-TaylorI was fortunate enough recently to be working at “An Evening With” type event, where a guest was interviewed in front of a mostly, alive, theatre audience. I was the venue’s sound and AV engineer for the event, not a taxing job by far; just two radio mics and two back-ups and a sequence of video clips to play on cue.

The interviewer was computer history nerd and That’s Lifer Chris Serle and the interviewee was the wonderfully comic entertainer, all round Goodie and regular I’m Sorry I Haven’t A Clue panalist, Tim Brooke-Taylor.

T’was a fascinating evening with chat and banter interspersed with wonderful video clips, some dating back to before I was born.

And of course the interval drinks were followed by a selection of questions from the audience. Now, being a public-spirited sort of chap, I didn’t want to run the risk of him running out of questions. It can be embarrassing when nobody actually asks anything. So, to be sure of there being at least something to answer, I jotted a couple down. Well three actually. I wasn’t particularly expecting them to be answered, it was more so that there was something to answer if they were in short supply.

My three questions were:


Tim,
From a lifelong “James” who has never been a “Jim”, have you ever been tempted to adopt the far posher moniker of “Timothy”?
Or indeed, “Monica”?


Dear Bill,
During any of these fascinating ornithological evenings, have you ever been tempted to pose a question, as if it had come from Mrs Trellis of North Wales?

And that question comes from a Mrs Trellis of North Wales.


Tim,
Do the all-too-frequent hikes in Samantha’s appearance fee, in any way cause an effect on the quality of the panellists afforded?


I needn’t have feared, for there was a plentiful supply of questions.

And they even picked out one of mine, the first one.

It later transpired as we were chatting afterwards, that he and I, not only grew up very near to each other, but also experienced a very similar educational start in life, by effectively being the wrong sex in an almost, single-sex school!

 

Posted in Technical | Tagged , , , , , , | Leave a comment