Monday 31 December 2012

Shalee lhaih 07

Wooish! Lesh fakin jerrey ny bleeaney roshtyn, ta mee er jionney er jiu er son cosney beg jerrinagh.

Jea, ta mee er lhaih The Great Hedge of India as Oresama Teacher (y.l. 1). Chammah's shen, ta mee er scryssey daa lioar elley jeh'n rolley er y fa dy vel mee er nyn lhaih tammylt er dy henney (barriaght!) as er geau magh lioar ny ghaa t'er ve ayms rish bleeantyn gyn goll er lhiah.

Myr shen, rish baase ny bleeaney, ta'n rolley soit ec 112 lioar ry-lhaih as 27 lhiait aym rish mysh 6 shiaghteeyn. Cha nel shoh moal, er lhiah, as 3 jeu ayns çhengaghyn joarree, as wheesh dy 'eailley goll er 'sy traa shen.

As shalee 2013? Wahll, shegin dou troggal er y bun shoh, nagh nhegin? Myr shen, t'eh foym y rolley shen y yiarrey sheese dys y lieh: 56 lioaryn ny ny sloo rish jerrey 2013. Vaikmayd!

Sunday 30 December 2012

Shalee lhaih 06

Gyn leshtallyn. Daa hiaghtin, tree lioaryn. Cha cosoylagh eh dou yn çhalee shoh y chooilleeney, agh shen myr t'eh. Mannagh row mee er ngeddyn wheesh dy lioaryn noa nagh row er y rolley ry-lhaih hannah, veign er gosney, foddee. Inshym shen dou hene, aghterbee... Ta 115 lioaryn er y rolley foast. Ghow mee toshiaght lesh 149 er y rolley, as ta mee er lhaih 25 neayr's shen, as er scryssey kuse jeh'n rolley er caghlaaghyn oyr (myr sampleyr, lioaryn clashtynagh as lioaryn cur sheese).

Lhaih mee Suzunari (yl. 1), Igam Ogam, as Genkaku Picasso (y.l. 2) 'syn daa hiaghtin shoh chaie. Nee'm my chooid share ben elley y lhaih mairagh, er son moyrn.

Wednesday 26 December 2012

Er faarkaghyn nagh heill ee rieau nyn lheid

Hannee y Captan rish radling ny puirt, gee ooyl dy moal, as clashtyn rish yllagh ny follian. Leayst y lhong bwoailt dy smooinaghtagh: Arrane ny Geayee. V'ad ass Loghlynn jea, dinsh eh dy gennal, as son yn Eearvooir noght. Agh da'n inneen veg ruy roie n'oi, as hug leshtal, as hebb oarnag villish, loayr eh er faarkaghyn elley foast, as er nane-yeig er y chlag.

By fastyr geuree ee tra daag Arrane ny Geayee cour Nerin as y sheear. She arrane kiune v'ayn y fastyr shen, as by litçheragh ad ny tonnyn. Snaue yn Arrane nyn drooid, gollrish thunnag vraew oayllaashagh.

Hannee y Captan ny hassoo tost, as blakey magh trooid uinniagyn daahit ec bleeantyn dy gheay hailjey as ushtey feie, er y vooir as ish conghorraghey.
"Jannoo traa mie, Chaptan."
"Braew, braew."
Cha cheau eh shilley erbee er y phabyr buigh roish, lane linnaghys as shennaghys.

Beggan er veggan, hyndaa glass glonney dys doo scaanagh, as seiy y lhong trooid oie ayns cummey ushtey. Va'n speyr roue bentyn rish y cheayn as ren ad co-lheie, do nagh row ny rollageyn hene ry-scarrey voish y ghlistral er yn ushtey. Haink trimmid cadlee er y skimmee, as fer as fer, ghooin ny sooillyn oc. Huitt ad dy meein ayns ny soieagyn oc; as rish yn aavainshtyr leaystey er e chassyn, skeayll magh y Captan laue mooar fondagh, as stiurey eh da'n laare dy sauçhey.

Ny lomarcan, lhig eh da'n wheeyl stiurey ee hene, bunnys. Ren eh filley e laueyn mygeayrt cappan stainnagh, as tannaghtyn ny hassoo, soo stiagh çhiassid taitnyssagh y jough as jeeaghyn magh er oie as ushtey mestit. Jirree skeabey meein ny tonnyn da, myr sonsheraght ainjyssagh vayrey rish y lhiabbee. Voogh eh ny londeyryn, son nagh beagh gaue da baatey erbee nish, as mongey lesh ennaghtyn y shenn charrey shiaulley cho kiune. Lheie ad 'syn oie gyn heean, as voish y chiouyl huitt neose bineyn sailjey, nyn jeirnyn scarree, dys y cheayn foddey foue.

As yeeagh inneen ruy lieh ny cadley fo ny curtanyn, as fakin floag dorraghey roish yn eayst ard 'ailtagh.

Friday 21 December 2012

Excel: library heatmaps

A quick discussion of a recent project involving COUNTIF, SUMIF, INDEX, named ranges and production of a heatmap using a surface chart.

I was recently asked to do what turned into a fairly fiddly statistics project. A university library has moved to new premises, and the new building has a mixture of furniture types: armchairs, small café-style tables, large desks and so on. They want to monitor usage of the different furniture, to find out whether they've got the right mix for their students, or whether anything should be changed. This means tracking the number of students in each category of furniture at each counting time.

There are a few layers of complication.

  • There are several counting times each day, so there's nested/duplicate data. This basically introduces a three-axis analysis: tracking the number of students in each type of seating at each time on each day. It also means that analyses over time will need to collate the information for each day, as will analyses by time of day.
  • Counts can't always be taken, depending on staff availability, so sampling will be erratic.
  • Furniture has been broken down heavily as a starting point, but may need regrouping later.
  • A graduate study room is to be tracked as well, but separately from the main library.

I set up the usual table structure: ever-increasing Time and Date rows as the rows, and the furniture types as fixed columns, with a Total column at the end to sum the students in the library at any time. There's also a secondary column to track the Graduate room. I named this Readercount, and defined it as follows:

=OFFSET( 'Raw data'!$A$2, 0, 0, COUNTA('Raw data'!$A:$A), COUNTA('Raw data'!$2:$2) )

This creates a dynamic named range, which expands as new rows are added to the bottom. What it's doing is:

Select this stuff(Location of first cell I want, move down 0 rows, move across 0 cols, select a number of rows equal to (the number of rows in column A containing data), select a number of cols equal to (the number of cols in row 2 containing data )

In other words: get the first cell in the block, then select all the rows and all the columns that are in use.

It starts at Row 2, because row 1 contains values for the number of seats in each section, as I'll explain in a moment.

Using names rather than just blocks of cells has two advantages. One is that I can use dynamic ranges, which is really useful for calculations like sum and mean values. The other is that you don't have to worry about formulae breaking in unexpected way if you move, delete or add cells - as long as the name still selects the right cells, all formulae based on it should still work. It also helps pinpoint mistakes in formulae, because names are more transparent and simpler than cell references. You can avoid repeatedly defining complex cell ranges and risking mistakes.

Calculating usage

A heatmap seemed like a sensible and intuitive way to map the average usage of each seating type, and show how it changes over time. For the 'heat', I decided to use the percentage of the seating type that was in use. I added in a row giving the number of seats of each type, and named it Seats - this is row 1.

On a new sheet, I did some test analyses to make sure things were working. For any complex work, I tend to go through step-by-step progression, which is a habit from coding, but also a useful way to check I actually know what I'm doing. This makes it much easier to pick up on typos, data entry errors and other confounding factors, before getting into complex formulae. Since I didn't really know what I was trying to do, I faffed about with several ideas and several new functions before getting something really useful; I won't bother discussing the mistakes and dead ends. I ended up with three main blocks of calculation.

The first is a simple block of potential usage. This calculates how many seats were available in each seating type, in each timeslot - adjusted to account for the number of counts taken. It shows the maximum possible students who could have been recorded.

This is a simple COUNTIF. I gave a timeslot in column B, and each successive column had a version of the following code:

=COUNTIF( Readercount, $Bthis row) * INDEX(Seats,0,column)

The row always matches the current row; this selects the timeslot value in column B as the timeslot you want to filter for with COUNTIF. Using $B allows easy drag-and-fill of the other columns. The column value picks the column from range Seats that matches the seating type.

In other words:

=COUNTIF( Readercount, this timeslot) * INDEX(Seats,0,this seating type )

Count up instances in the Readercount block of this timeslot, and multiply them by the number of seats listed for this seating type in the Seats block.

Note that with INDEX, if you enter 0 for the row or column, it will pass through every row or column in the chosen range. This is crucial to the COUNT* and SUM* formulae. If you enter a specific row and a specific column, it'll only look up one specific cell.

Next I calculated the actual seat usage: the total number of people who used each type of seating during each timeslot. I gave a timeslot in column B, and each successive column (representing a seating type) had a version of the following code:

=SUMIF( INDEX(Readercount,0,2), $Brow, INDEX(Readercount,0,column) )

i.e.

=SUMIF( INDEX(Readercount,0,timeslot), this timeslot, INDEX(Readercount,0,this seating type) )

Go through all rows in Readercount. Whenever the value in the Time column matches our timeslot value, find the column in that row that corresponds to the current seating type, and add its value to a running total.

In practical terms: total number of readers counted in that slot

Thirdly, I calculated the mean seat usage, by taking the total usage and dividing by the number of counts for the timeslot in question. Luckily, there was no variation in counts between the seating types, only between timeslots, so that's one less thing to worry about. As always, I gave a timeslot in column B, and each successive column (representing a seating type) had a version of the following code:

=SUMIF( INDEX(Readercount,0,2), $Brow, INDEX(Readercount,0,column) ) / COUNTIF(Readercount, $Brow)

i.e.

=SUMIF( INDEX(Readercount,0,timeslot), this timeslot, INDEX(Readercount,0,this seating type) ) / COUNTIF(Readercount, timeslot)

Here's a timeslot and a seating type. For every timeslot value in Readercount that matches our chosen timeslot value, get that row's entry for our chosen seating type, and add it to a running total. Finally, divide that total by the number of counts made for this timeslot.

In practical terms: total number of readers counted in that slot / number of counts in that slot

Finally, I worked up to the percentage usage. This was simply the mean seat usage multiplied by 100.

=SUMIF( INDEX(Readercount,0,2), $Brow, INDEX(Readercount,0,column) ) / ( COUNTIF(Readercount, $Brow) * INDEX(Seats,0,column)) * 100

i.e.

=SUMIF( INDEX(Readercount,0,timeslot), this timeslot, INDEX(Readercount,0,this seating type) ) / ( COUNTIF(Readercount, timeslot) * number of seats available ) * 100

For every timeslot value in Readercount that matches our chosen timeslot value, get that row's entry for our chosen seating type, and add it to a running total. Divide that total by the number of counts made for this timeslot multiplied by the number of seats in the room. Finally, multiply by 100 to give a percentage figure.

In practical terms: total number of students counted in that slot / hypothetical maximum number that could have been counted * 100

For the actual heatmaps, I selected relevant cells from the percentage usage block, and created a surface chart. There were several changes necessary.

  • The default interval for the 'heat' (percentage usage) scale was 20. I had to tweak this by going into the Depth Axis, and setting the Major Unit to 10.
  • The default colours are revolting and useless for a heatmap. Astonishingly, there doesn't seem to be any simple way to specify a gradient for the colours used, or any preset colour ranges. You can, however, add a gradient fill to the legend, and you can add a gradient as the fill for a single value: I can't think of a single reason why you'd ever want to do either of these things. Baffling. I had to manually go through each 'band' on the legend and set its fill colours.
  • The vertical time axis automatically places the earlier values at the bottom of the scale. I found this unintuitive, and flipped it (Format Axis > Options > Series in Reverse Order).
  • A graduate study room is to be tracked as well, but separately from the main library.

The heatmap indicates that the “South Desk” and “West Desk” are by far the most popular, that the “Comfy Chairs” and “Oval Table” are the least popular, and shows up the generally higher occupation rates between 11.30-5.30pm, which correspond to more students present.

On request, I also created a line graph of the seating usage, with each timeslot as a separate line. Compared to the heatmap, this emphasised the pattern of use for each seating type. It demonstrated that this was more or less constant throughout the day, except for a slightly increased tendency to use armchairs at the end of the day, based on a small sample of counts at this time.

Analysing the graduate data proved considerably fiddlier, even though it's a single column of data. I'll discuss that in another post. EDIT: Other post is now up.

If anyone is interested, you can get a (slightly cleaned-up, but hopefully otherwise unchanged) copy of the spreadsheet here.

Sunday 16 December 2012

Shalee lhaih 05

She shiaghtin harroogh v'ayn: cuirrey kiaull, jinnair obbreeyn Nollick a.r.e. As mish skee dy liooar, ta mee er lhaih lioaryn aashagh son y chooid smoo.

Yn çhiaghtin shoh chaie, lhaih mee Haganai: I Don't Have Many Friends (y.l. 1), The disappearance of Nagato Yuki-Chan (y.l. 1), Point Blanc, The Moon of Gomrath as Genkaku Picasso (y.l. 1).

Sunday 9 December 2012

Shalee lhaih 04

...cha nel mee feer vie ec screeu ny postyn shoh. Ny lhaih dy tappee noadyr.

Yn daa hiaghtin shoh chaie, lhaih mee Language in Society, Zoo Quest to Madagascar, Achtung! Vranek sieht ganz harmlos aus, The Diary of a Nobody as How to Write Science Fiction and Fantasy.

Ren mee lhaih mysh y chied jeihoo jeh Triple Zero as faagail magh er y fa nagh vel mee dy firrinagh son goaill toshiaght lhaih y carnane lioaryn shen ta jannoo magh y Star Wars Expanded Universe, as myr shen er lhiam nagh row eh feeu ceau ooilley'n traa shen lhaih lioar nagh ghoym y share assjee.

Skeeal recortyssit: Yn Lioar

Er bun nagh cooin lhiam dy leayr nish, ren mee briwnys dy phrowal skeealyn clashtynagh traa ennagh, as myr shen tra mee er nyannoo recortys jeh "Yn Lioar" liorish Lovecraft, çhyndaait aym pene. Ta'n recortys ry-gheddyn ayns my choontey Dropbox (kiangley jeeragh da'n choadan).