Five or so years ago I exclusively used my custom built Windows PC. It was great. It was also yellow. Then I used a Macbook. Things changed.
Since then, whilst I do regularly use Windows PCs for boring day to day stuff (Internet browsing, playing games etc), I spend the majority of my working hours on my Macbook. Office (and things like that) I tend to do in the cloud.
My mother recently asked me to take a look at a spreadsheet she was making. A colleague had told her that she needed to 'codify' the data that she had diligently been inputting from a survey she had gone to great effort to distribute.
I thought this was a little cruel. Making things sound uneccesarily complex to someone who clearly isn't an expert in the subject matter.
I offered to help, and 4 hours later I had produced a beautiful, functional, and extensible excel workbook.
As mentioned, whilst I spend a lot of time online and work in software, I never use Excel. I essentially wanted to procrastinate and this seemed like a good way to do so.
Programming is certainly a transferrable skill in that once you know one language you basically (in my opinion) know them all. I wanted to test my hypothesis that 'computeriness' is also totally transferrable.
The spreadsheet
Each sheet was a different question. Questions did not neccesarily have quantitative answers. Some were qualitative, and the answers had been categorised. The data had been inputted in a human understandable way, but not in a way that was useful for data analytics and graphing.
The first problem was converting the data into an appropriate format without retyping it all. Meet my friend Google.
I made the assumption that Excel could do most things. If I could conceptualise something that I could do with (for example) a SQL database, I could surely achieve similar with Excel.
I did consider just creating some sort of web app that my mother could use to process data, but decided that whilst probably easier for me, it would unlikely be easier for her.
Transpose paste was useful. As was the ability to freeze panes. It did however seem that everything was fairly clunky. Fixing things at the top and bottom of a dataset seemed more arduous than it should have been.
The next thing that I wanted to do was to create an 'Index' sheet where the raw data for each question was summarised and that data graphed.
As the survey data was categorised by the submitters location I wanted to allow my mother to change the location (from a predefined list) and have the summary data and graphs update automatically.
Creating the list was easy. Summarising the data was less so. Each sheet needed to be aware of the value in the 'Index' sheet, and the dataset needed to be updated as/when it was changed.
Excel allows filtering by a columns values, but to get it to update when a value in a different sheet was changed was less simple. It required macros. CODE ! woo !
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Q1").AutoFilter.ApplyFilter
Sheets("Q2").AutoFilter.ApplyFilter
End Sub
The other puzzle piece was the summation of data. Whilst you can filter data by the value of a column, the normal aggreation functions like SUM act upon all rows in the range, not just the filtered ones. Fortunately there are functions like SUBTOTAL which allow you to act on just the filtered results.
Things do however get more complex when for example you have ratings data. One question asked the individual to rate a factor on a scale from 1 - 10.
I wanted to create a bar chart of how many people specified each rating. The summary data simply requires a count of how many rows had each value (from 1 - 10). Automating this counting was surprisingly complex given the additional complexity of the location filtering.
Thoughts
It was a fairly good reminder as to why I do not use excel :)
It does everything, but makes you jump through hoops to do it. It is overly complicated, and makes slightly complicated things very complicated. Given that it is an office product for the masses this seems somewhat ridiculous.
=SUMPRODUCT(('Q7'!J$5:'Q7'!J$250=A124)*(SUBTOTAL(103,OFFSET('Q7'!J$5,ROW('Q7'!J$5:'Q7'!J$250)-MIN(ROW('Q7'!J$5:'Q7'!J$250)),0))))
Whilst I went further than neccesary and produced something that simply required data input and then automatically processed it, it seems apparent that it would have taken my mother an enormous amount of time to achieve the result that she desired on her own.
Someone needs to build an data processing tool that is actually simple.
Also. Nowadays you really can Google just about anything.
