SKI LENGTH CALCULATOR

You have a Saturday job at the SuperSkis Rental Shop. At the moment they look up the ski length for a person’s weight on a table printed on paper. You are going to use your spreadsheet skills to develop a simple computerised system for them. You will practise the following skills:

  • Entering text & values
  • Naming ranges
  • Moving & renaming sheets
  • Designing the user screen
  • Adding validation to cells
  • Testing your validation
  • Using the VLookup function
  • Formatting the sheets

Entering text & values

  • Open a new Microsoft Excel workbook.
  • Save it in your ICT folder as SkiCalculator.
  • Enter the details exactly as they appear in the screenshot below:
  • You will need to wrap the text in row 4 to get it to appear as above. Highlight the entries in row 4, select Format → Cells, then Alignment, then choose Wrap Text, & adjust the width of the cells.

Naming ranges

  • By naming a cell or cell range you will make referring to it much easier, and you will automatically be using absolute cell references.
  • Highlight cells A5 to C11
  • Select from the main menu Insert → Name → Define.
  • Name the selected range SkiLength, click Add, then Close

Moving & renaming sheets

  • You need a main screen into which the customers weight will be entered.
  • Drag the sheet tab for Sheet2 to the left of Sheet1.
  • Right-click the Sheet2 tab, rename it Main Screen.
  • Rename Sheet1 as Ski Length Chart.
  • Delete Sheet3.

Designing the user screen

  • This is the screen where the ski shop staff will enter data (the customer’s weight) into, and the system will display the correct ski length.
  • On the Main Screen sheet, enter the data exactly as it appears in the screenshot:

Adding validation to cells

  • Validation is the checking of data by the software as it is input to make sure it is sensible or reasonable. It reduces the chance of errors.
  • In this spreadsheet, validation will be used to make sure that when the customer’s weight is entered it is within the allowed range.
  • On the Main Screen sheet, click in cell C3.
  • Select from the main menu Data → Validation.
  • Select Whole number for the first drop-down list.
  • You are then asked for a data range. Enter 48 as the minimum weight, and 85 as the maximum weight.

  • Do not click OK, instead select the Input message tab on the same dialogue box, enter this text in the message box: Enter a whole number between 48 & 85.
  • Do not yet click OK, now select the Error Alert tab, the message you enter here will be displayed if someone enters an invalid value.
  • Choose a Style from the drop-down list on the left of the dialogue box, enter weight as the title, then type in the error message below:

Testing your validation

  • It is important now to test that your validation works.
  • When you click in cell C3 you will see your Input message.
  • Enter 45, you should get an error message.
  • Enter 59, it should accept this value.

Using the VLookup function

  • Still on the Main Screen sheet, select cell C7.
  • Click the Paste Function button
  • In Select a category, find Lookup & Reference, then in the box below find VLOOKUP, click OK.

  • The Lookup_Value is the cell you want to find in a table, enter C3 here.
  • Enter SkiLength in the Table_array box. This is the named range that contains all of the lengths in the Ski Length Chart sheet.
  • VLookup will look for the value you entered in cell C3 in the left-most column of the range that you entered(this is the Minimum Weight column. If it can’t find the exact value (if you enter 65 for example), it will default to the nearest value less than 65, and find the ski length for 64. For this reason VLookup will only work if the Table_array is sorted in Ascending order.
  • Now you need to find the ski length from the third column in the table, so enter 3 in the Col_Index_num box.

  • Enter a weight of 50 in C3, and see the ski calculator working.

Formatting the sheets

  • Format the worksheets to make them easy to use, and attractive.

Page 1 of 4