Harry Potter potions room

I once tried to chat up a guy in a bar using my favourite Excel formula. He shockingly didn’t take the bait. I wasn’t too cut up about it, I figured if he were the guy for me his eyes would have lit up the moment I said VLOOKUP. He would be in my bed right now asking me to show him my SUMIF.

Sexy talk aside, if you can master the below formulas people will think you are a wizard. I am 99% sure that Voldemort could have been taken down with a good PIVOT table and if you learn the formulas below you too might be able to take down a fascist overlord one day. And if it turns out that magic doesn’t exist then they might actually help you with your job. Boring. Bring on the Harry Potter references.

With all this Excel knowledge I genuinely have no idea why I’m still single.

VLOOKUP

Ok so imagine that you have a list of Death Eaters and how many muggles each of them killed. You are in the middle of a court case at the Ministry of Magic where 4 of the Death Eaters are being tried and you need to quickly know the muggle count for each. What do you do?

Death Eaters

Use the magic of the VLOOKUP of course! Here is our list of Death Eaters and kills on the left (imagine that there are thousands on the list because I can’t be arsed googling more than ten). Bellatrix is first up in the court and what I am going to do is write the following formula into cell F2 of my court list.

=VLOOKUP(1,2,3,4)

1: Click the cell that has the thing you want to look for written in it, in this case I have clicked E2 because that is where Bellatrix’s name is.

2: VLOOKUP’s think in terms of look for the information in a table, so we need to tell excel where this table is. For this example my table is columns A and B, because that is my list of Death Eaters. By writing A:B I have selected the whole damn thing. If your list also included where they lived, their favourite Unforgivable spell and their wand type you would just select more columns.

3: This is where you tell the formula what information you want it to bring back, if it finds Bellatrix I want it to bring back how many kills she had. In the table we selected above it has two columns (A is 1, B is 2), I am going to put 2 because I want the muggle number to come back.

4: Always write FALSE here. Don’t try to understand why, if you don’t the formula will fuck up and Bellatrix will literally get away with murder.

Top VLOOKUP tip: Always make sure the thing you are looking for is in the left hand column of the table.

&” “&

Snape has sent you a spreadsheet of the Gryffindor Quidditch team. But because he is a lovesick resentful dickhead he has given it to you in a really unhelpful format. The names are in column A, and the positions are in column B; what you want is the name and position in the same cell (god knows why, just humour me).

Quiddich team

If I used the formula =A2&B2 it would give me the name and position like this Oliver WoodKeeper with no space between the words; that is why I added the “ “ between them. That tells excel to put the name, then a space, then the position. Fuck you Snape, your tricks don’t work around here.

Mid

You copied your Transfiguration homework from Hermione and it wasn’t until just before class that you realised she passive aggressively wrote an extra bit at the start of each spell so that you would be marked wrong. Never fear, Excel is here. The MID formula lets you take the middle part out of a string of text.

Spells

=MID(1,2,3)

1: the click the cell that the text is currently in

2: Write the number of the letter you want it to start with, in this example I want all the spells to start on the 2nd letter, so I am going to write the number 2

3: What letter do you want to end with? Because all my spells are different lengths I have just put 100 so that it captures all of the rest of the spell. BOOM cheating achieved.

SUMIF

Well it is the end of the school year and the entirely random allocation of house points has been done (Longbottom getting ten for standing up to his friends just reeks of a rigged system created by a crazed despot). But because wizards clearly have no understanding of structure it is time to add up the meaningless points.

house points

=SUMIF(1,2,3)

1: This is the column where Excel is going to look for your citeria, in this case it is column A because that is where all the houses were listed

2: This is where I tell excel what I want it to look for, in this case I have clicked on the cell that has the House name in it in my final points table, I could also have typed “Ravenclaw”.

3: Select the column that has all the fucking pointless points in it (aka, column B)

NB: To select an entire column you can click on the damn letter on the top of the column you want, like click on the actual fucking letter at the top.

 

There you have it. I’m drunk and I think I may have just insulted both Harry Potter fans and Excel experts. But fuck it, if I see Voldemort I am going just stand there screaming I WANT TO PIVOT YOU until he gets concerned and offers me a cup of tea.

Is there a support group I can join?

Yes, I am a Ravenclaw how did you guess?

Rookery Hotel Farringdon

I am afraid of flying and anxious about travel in general, a winning combination. After a month of long weeks at work I really wanted a holiday, but without the miasma of stress that came with it. What is a girl to do? Apparently the answer is go to Farringdon.

It’s a decidedly odd feeling to get all packed up only to hop on the Hammersmith and City line for twenty minutes. Honestly it was the most stress free start to a holiday ever. I was so overwhelmed by how easy it was to take the tube to my holiday destination of choice that I actually smiled at a stranger. I, Runawaykiwi, smiled at a stranger on the tube. Either I was seriously sick, or this was the start of a beautiful weekend.

I know you are sitting there thinking this is a really weird idea, Farringdon doesn’t seem like anyone’s first choice..for anything. Well when Travel Supermarket asked me to choose between a night in a windmill in Norfolk or a lighthouse in Cornwall, I asked if there was anything a little closer to home. They said Farringdon and I said FUCK YES BECAUSE THE HOTEL LOOKS LIKE A SET FROM HARRY POTTER.

Farringdon…Hogwarts…same same.

Rookery Hotel Farringdon

The Rookery hotel is exactly what I thought a London hotel would be before I moved to London and realised they were all clones of the Premier Inn. Thought to be built in 1764 the Rookery comes with ACTUAL KEYS, like on a key ring. A key ring that would be defined as a weapon in at least 12 of London’s boroughs.

Walking into my room was like I had been awarded the Prefects badge for Ravenclaw and got upgraded to the best freakin dorm room in Hogwarts. Four poster bed, iron bathtub and a bottle of port waiting to be cracked open. Ok I’m not sure that prefects should be drinking port, but Dumbledore lets us have butter beer so he must be ok with us drinking right?Rookery Hotel Farringdon key

My only issue (one shared I’m sure by many Ravenclaw) is the paintings on the wall. There was an alarmingly disapproving portrait of Joseph Parcroft (the 1700’s tailor my room was named for) on the wall of my room, his eyes perfectly positioned to glare at me as I lay in bed. Way to kill the mood Joe.

The other benefit of a Farringdon holiday, after the stress free travel sitch, is the close proximity to brunch. Five minutes after leaving the hotel I was at Workshop coffee, ready to mainline flat whites to my heart’s content. But here is the truly magic part, after brunching I headed back to the hotel to blog… because unlike most hotels built in 1764 the Rookery had amazing wifi. This is literally the only time you will hear me say this; but I think a hotel in Farringdon might actually be better than Hogwarts? I think Harry Potter would have had a far easier time beating Voldemort if he had wifi and access to Snapchat.

Workshop coffee company brunch

Oh and in case I haven’t sold the Rookery enough here are two very important facts for you:

  1. It has a cat
  2. There is free cake every afternoon

No even kidding, free cake and a roaming cat…totally makes up for the creepy portrait watching you sleep.

Farringdon

Hogwarts Castle covered in Snow at Christmas

I am an out and proud Ravenclaw; total nerd, sassy as fuck and rather partial to a diamond encrusted diadem. I am also a Christmas lover, that annoying person who listens to Cowboys Christmas Ball ..on repeat.. in July. This combination meant that visiting the Harry Potter Studio Tour today when it has JUST been decked out in all its snow covered Christmas glory is essentially a wet dream…except Voldemort was there. Awkward.

This morning I was 100% panicking because in typical London style I was running late. I had built a 30 minute buffer into my journey but the bus, two tubes and a train just laughed and destroyed me like a horcrux. The reason I was freaking out was because entry to the Harry Potter Studio Tour is timed, and I turned up for my 12 o’clock tour at quarter past. I mean I’ve seen the punishments Umbridge metes out and I just don’t want ‘I will not be late’ permanently etched on my hand…although that may solve my perpetual lateness issue.

Gryffindor common room at Christmas studio tour

So I turned up in a full tizz, accidentally pushing over a small child in Slytherin robes (lol, no one accidentally pushes over a Slytherin) and told a muggle staff member my entire train drama starting from the moment I woke up. Before I even got to the funny part with the angry guy on the tube she just patted me on the shoulder and said don’t worry just go in I SWEAR I DIDN’T USE A SPELL ON HER.

The Harry Potter Studio Tour takes you through some of the ridiculously intricate sets that the eight movies were filmed on. It’s a bizarre experience to see these entire worlds that you have watched on screen, and turn up in real life to discover the fourth wall is actually a bunch of gawping tourists.

Even if you haven’t seen the movies in a few years there is something special the first time the Great Hall doors open and you step inside. Decked out for Christmas the hall is lined with Christmas trees, each topped with a flying witch because thats just how Hogwarts rolls. The tables are topped with a Christmas feast including turkeys with all the trimmings, house branded Christmas crackers and Christmas puddings that spontaneously burst into flames.

Harry Potter Christmas Wreath

The other sets are also getting into the holiday spirit with snow falling behind each window, tinsel adorning the beds in the boy’s dormitory and the remnants of a hard Christmas lunch all over the Burrows kitchen table. I was expecting a little more snow on the outside sets (Privet Drive and the night bus) but I contented myself with a second butterbeer to make up for it (seriously Butterbeer is my ideal combination of sugar and cream).

Then came the moment we [I] had all been waiting for [desperate to Instagram]; the set model of Hogwarts covered in a fresh dusting of Christmas snow. It got emotional. Christmas and wizard emotional.

Harry Potter Studio Tour Great Hall at Christmas

I don’t care how cynical you are, get yourself down to the Harry Potter Studio Tour and get your fill of Christmas magic. Unless you are a Slytherin in which case take your bitter heart of coal and go and rain on someone else’s Christmas parade.

And no I didn’t buy a wand. Although I really REALLY wanted to.

20130817-150842.jpg

The last in the series of ‘seeing famous people in real life’ plays was seeing Daniel Radcliffe in The Cripple of Inishmaan.

It was challenging from the offset – the entire play is set on a remote Irish island and has the thick Irish accents to match. I’m normally quite good with accents, but this took me till the third scene before I didn’t have to concentrate on the words.

While it is meant to be a hysterically funny satire, I found the humor not black enough to be funny but too heavy to be funny either – the writing has to be very clever to make entertainment out of tuberculosis, domestic violence and unreliable egg delivery men.

But there was no shortage of laughs, I think there was a healthy scattering if Daniel Radcliffe fans in the audience who would have laughed at anything he said.

The outstanding performer of the night had to be Sarah Greene who played Helen McCormick, the violently tough village slut. She was sparky, funny and really stole the show. I also really liked the set – essentially three sections on a stage sized lazy susan.

Although it wasn’t really my sort of play I think the reason I felt so let down by it was because the previous two in the series were so mind blowingly amazing.

Then again, for £11 it was still a good night out.