[ Home ] [ FAQ ] [ Web Pages ] [ Other Sites ]

Lesson 3

The Budget2 problem

Page 102 instructs you to create a more complex lookup table. This is one of the trickiest problems in the course, so don't be discouraged if it takes some time to solve. 

Your data exists in a large table. To pick out a piece of information, VLOOKUP needs to know which row and column to look in. The previous exercises you did with VLOOKUP referenced a cell for lookup_value. This lets VLOOKUP determine which row of the table to use. Then, you put the column (col_index_number) in by hand. For this assignment, both the lookup_value and col_index_number need to come from cell references.

This two-level lookup is a bit different from the lookups you have done before. Here is an example using the Lookup spreadsheet shown on page 96 of the learning guide. Rather than showing both the title and the price, we will change it to let the user select which column will be shown.

Change B1 so it reads 2.
Change C1 so it reads 3.
Change A11 to read Column:
Change A12 to read Value:
Change B12 to read = VLOOKUP(B10, A2:C8, B11, False)

You can now enter a video number in cell B10 and either 2 or 3 in cell B11, and the correct value will show up in B12. If you like, you can add as many columns of information as you want, and the only change you will need to make to the lookup function is to change the C8 to D8 (or whatever cell ends your table).

Note that I put 2 and 3 at the head of the columns, but you could just as easily call them February and March, or anything. The VLOOKUP uses the value in B11, and counts that number of columns. It doesn't look at any of the information in row 1.

Try doing the Budget2 project in two steps. First write a VLOOKUP function that will allow you to type in a category name, and give you the value of that category for January. This is very similar to the function we originally wrote for the Lookup file. Next, replace your col_index_number with a cell reference to allow for changing the month as well as the category. When finished, you should have something that looks roughly like this:

The user will type in values for the category and month, and there will be a VLOOKUP formula to automatically calculate the amount. Notice that I used a 5 instead of "May". We do not expect your function to use the month names, only the numbers. Using the names would add another level of complexity to a problem that is already somewhat tricky.

Extra hint: Since January (month 1) is in column 2 of the table, all of the month numbers will be off by one. You need to put a +1 somewhere to fix this problem.

The Pseudocode problem

Pseudocode is "halfway" between english and a programming language. It is a description of a process in detail, though not necessarily in full sentences. The key is to provide enough information so that anyone could follow the instructions (a child, an alien, or even a computer, if the instructions were translated into a language it understands). Recipes are one type of pseudocode, instructions for assembling things are another. Most instructions are similar to "Put tab A in slot B", "boil until the egg floats", or perhaps in the assignment, "If change_needed is greater than 25 cents, Put a quarter in pile A."

When you are asked to write pseudocode, you should create one solution for each of the problems. The solution for a problem should use selection, sequence, and repetition as necessary. Do not create three separate solutions, with one for each programming construct.

Here is an example of pseudocode for washing dishes:

close the sink drain hole
squirt some dish liquid in the sink
turn on hot water
WHILE sink is not full
    wait 2 seconds
    splash water to create bubbles
turn off water
(sink is now full of hot, soapy water)
put a dishrag in the sink
WHILE there are dirty dishes
    grab a dish
    put dish in the sink
    scrub dish with dishrag
    IF dish is still dirty
        THEN scrub dish with scouring pad
    rinse dish
    put dish in drainer
open the sink drain hole
put dishrag in laundry basket

Drying the dishes is left as an exercise to the reader :) Even though this description is fairly complex, it still has some pieces missing. If there are nonstick dishes, we don't want to scratch them with the scouring pad. We don't want to wash greasy dishes first, since they will make the water dirty. There are no instructions for times when the dish drainer is full... The pseudocode you write should be detailed enough that it can be understood (by a child, alien, or computer) without being so detailed that it becomes difficult to follow.

For the first problem in the assignment (making change), your solution should work for any item that costs less than a dollar. I should be able to make change for a $0.37 item, a $0.25 item, a $0.99 item, all using the same pseudocode. When approaching  the problem, think about how you would make the change if you had to do it yourself. Think about "easy" amounts (like $0.25) as well as more difficult amounts (like $0.37). What process do you go through to select the proper coins?

When you are finished making change, don't forget the second problem!