News‎ > ‎

IPT: Help wanted?

posted Oct 13, 2009, 2:50 AM by Eddie Woo
I don't usually get school-related emails in the holidays. But this two-week break is different from all the others I've had so far. The HSC looms, and I teach two HSC classes!

So for those who asked me questions, thanks for your patience. Here are some answers - I hope they're worth your while.

Refining an internet search
The first answer is related to Q21 (b) in the 2005 HSC. If you haven't had a close look at it, download it from the Board of Studies website

The exact question I've been asked is: "I came across the following question and had no idea how to answer it. Am I missing something really obvious? I would go with using different search terms, but that wouldn't give me the 3 marks its worth."

This is one of the cases where the devil is in the details. If we pay attention to all the minor details given to us by the question, we can use them as clues to form the required strategies. Here are the clues:
  • A single document (PDF) is being sought
  • The student uses 'IPT' as their search term
  • Unwanted matches include ‘Insurance Protection Tax’ and a foreign website
Based on those, here are the strategies I would write about:

1. Actual words, not just an acronym. One of the unwanted matches mentioned in the question is 'Insurance Protection Tax', which is a match for IPT (obviously) but not what we are after. Therefore, it makes sense to search for the actual words and not just rely on an ambiguous acronym. Information Processes and Technology would be a better search term than IPT.

2. The whole phrase, not just individual words. We can go one better, though. Entering this phrase in inverted commas would further narrow the search: “Information Processes and Technology” which would force the search engine to return only the documents containing the complete phrase, rather than all combinations of information, processes and technology. This would also prevent the search engine from interpreting and as a logical operator.

3. Related phrases, not just the key phrase. To reduce the number of results still further, you could add extra keywords such as Board of Studies or syllabus document. The key is to realise that we are gradually increasing the specificity of the search, which is a vital step when searching through unstructured data.

4. Specific categories, not just any content. I can think of at least three categories that we can use to restrict the search engine even further. We can specify a search according to (a) language, (b) locality and (c) file type. By going into the advanced search setup and searching for English documents hosted on Australian servers that are PDFs only, we are going to sift out a lot of unwanted matches. 

One of the coolest things about this course is that I can prove the efficacy of the strategies I've just outlined. If you input all of the above into Google, the resulting search returns only 5 matches - and the top one is the one we want. Not bad! Okay, one down - next question!    

The second question speaks for itself, so I'll just quote it:

We have an important question to ask. Looking through the following paper we couldn't do question 26 (b) i. When browsing the answers the formula had strange dollar signs. We'd like to know the importance of these if possible.

Are they important? Well, yes (that's why they're included in the answers). Let me try and explain what they do with a sample example.

Suppose you have a spreadsheet with some boring numerical data in it, and you want to carry out a few simple calculations on it. Here's what your spreadsheet might look like:

All this formula does is multiply cell B2 by 100. Note that when we typed in B2, Excel recognised that we were talking about another cell in the spreadsheet. When your formula refers to another cell in the sheet, that's called a reference - remember that for later. Unsurprisingly, the result turns out like so:

A natural thing to do would be to copy the formula in cell D2 into the cells below. But something very interesting - and intelligent - takes place as soon as you press 'paste':

Nothing might seem unusual to you at first. That's because the designers of Excel have done an excellent job at anticipating what you wanted to do when we copied and pasted that formula. Let's have a closer look at the formula that has just been pasted:

Do you see what just happened? The formula we copied was =B2*100, but the formula that was pasted by Excel is =B3*100. Why did it change? The answer is that references are relative, not absolute. What does this mean?

Here's a simple way to think about it. When we typed B2 into cell D2, Excel remembered this as "the cell two to the left". So when we copied B2 into cell D3, it became B3 instead - that is, "the cell two to the left of D3". The pattern continues all the way down as we continued to copy the formula:

This is really quite neat. It means that we can copy and paste formulas very quickly, and don't need to retype them for each cell. The usefulness of this increases in proportion to how much numerical data we have to work with. If there were 500 rows instead of 5, the time-saving would be truly significant!

But here comes the question. What if we don't want our references to be relative? Consider the following example:

The situation is similar to before, but instead of always using the number 100, I want to be able to change the "Magic Number" to be some other value - say, 25. If we were using the previous formula, then we would have to update every single cell if we wanted to change the Magic Number. Now - in theory - all I need to do is change one cell (in this case, D2). So, we input the formula =B4*D2. What happens next?

Okay. So far, so good - right? Well, let's do what we did before - copy and paste the formula we used in cell D4, and put it straight into cell D5. This is what you get:

Oh no. What's happened?! 2 multiplied by 25 is not 0. So, where's the problem? If you've been following the argument so far, then this should come as no surprise. For those who don't get it and need the spoiler, let's reveal the formula in cell D5.

Disaster! Can you see what the problem is? Since references are relative, Excel interpreted the formula in cell D4 as "two to the left (B4), multiplied by two above (D2)". When this formula was pasted into cell D5, it resulted in "two to the left (B5), multiplied by two above (D3)". Cell B5 is the correct, but cell D3 is not - in fact, that cell is empty! That is why the result is 0 (2 multiplied by nothing is equal to 0). 

How can we fix this? Finally, we have arrived at the point of this whole exercise! Dollar signs make references absolute instead of relative. Let's start again and re-write the formula in cell D4.

When the dollar signs are put in front of the D and the 2, they mean: "Don't think of this reference in relative terms. When this formula is copied and pasted, it should absolutely always refer to cell D2." When we enter this formula in, the result is the same as before:

But when we copy and paste this formula into the cells below, watch what happens:

Success! Let's have a look under the hood to see what Excel has done to the formula:

As you can see, the reference to B4 has become a reference to B5. However, the reference to $D$2 remains a reference to $D$2, which is why we get the expected answer (50). Now we can copy the same formula into all the rest of the cells:

Incidentally, since we have made the reference absolute, we can use it for other things too:

By copying the same formula two cells to the right, you can see how the middle column of numbers is now being multiplied by 25 again. We can also take advantage of the fact that changing the Magic Number is as easy as adjusting one cell:

Alright, I think you get the point now! I hope that makes sense of the 'strange dollar signs' for you. Follow-up questions are welcome. Good luck to the rest of you all in your study sessions - one week to go!