BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 25 November 2019
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
The winner and solution for our October 2019 competition
The winner and solution for last month's competition are shown below, along with various other interesting snippets of information!
The problem
Last month we asked you to find the missing question:
The task was to find out what the missing question was.
I inadvertently used the CONCAT function, which I think only came in to Excel in version 2016 - apologies that this made life harder for a few people.
Fortunately, we left instructions:
There were only 2,293 steps to follow ...
The answer
We received a record response of 24 (all correct) answers. The missing question was:
I think most people used text-to-import or Power Query to extract the numbers from each question, then used filters, subtotals or pivot tables to get the grand totals for each of the 25 boxes (I used VBA instead).
The winner
Congratulations to Victoria Anthony of Sucden Financial, whose name was picked out of the Wise Owl electronic sorting hat, and who receives this month's £50 Amazon voucher.
I used Excel to type the formula =RAND() against each name, pressed F9 to recalculate the worksheet 10 times, copied/pasted values and then sorted. Fairly random ...
The films chosen
Here are the 24 films chosen:
Shawshank Redemption is the only film which was chosen more than once, justifying its place at the top of IMDB's Best Movies Ever list.
I toyed with the idea of awarding the prize to the person who chose the best film (in which case The Sound of Music contributor would surely have won).
Another solution
For those who don't want to have to open another file, here's Andy (Gould)'s suggested code to solve the problem:
Option Explicit
Sub FollowInstructions()
Dim Instructions As Range
Dim Instruction As Range
Dim NumberRange As Range
Dim NumberArray
Dim NumberToChange As Integer
Dim NumberToAdd As Integer
Set NumberRange = Worksheets("Phrase").Range("B4:AC4")
NumberRange.Value = 0
NumberArray = NumberRange
Set Instructions = Worksheets("Instructions").Range("A2", Worksheets("Instructions").Range("A1").End(xlDown))
For Each Instruction In Instructions
NumberToChange = CellNumber(Instruction.Text)
NumberToAdd = ValueToAdd(Instruction.Text)
NumberArray(1, NumberToChange) = NumberArray(1, NumberToChange) + NumberToAdd
Next Instruction
NumberRange = NumberArray
End Sub
Function CellNumber(InstructionText As String) As Integer
CellNumber = Split(InstructionText, " ")(5)
End Function
Function ValueToAdd(InstructionText As String) As Integer
ValueToAdd = Split(InstructionText, " ")(1)
End Function
This is a better solution than mine, but be aware that you'll also need to know more VBA to make sense of it!