Information systems theory Tutorial 6

Tutorial 6 (teaching week 7)

Objectives and organisation of tutorials:

.    Students will complete excel activities (including watching LinkedIn Learning videos) and case study readings prior to the tutorial

.    Students will work together to confirm their understanding of excel topics

.    Tutors will lead discussions on theoretical topics using the case study readings

.    Students will participate in quiz sessions to help reinforce learning. These quizzes are not assessed but the highest-ranked students will receive a prize at the end of the semester.

.    Students are strongly encouraged to attend all tutorials and take comprehensive notes. In   cases where a student cannot attend a particular tutorial, the student can complete the pre- tutorial activities on their own, watch the recordings, or follow up with the tutor during

consultation sessions.

Information systems theory (ISD)

1. Read the short article/case: “How project management turned into a Scrum” (2015) by Pollack, L, in the Financial Times (Click here toaccess it through the library).

1a. Based on the case what do you think are the benefits of agile/Scrum over waterfall?

1b. Based on the case why do you think that Scrum IS projects have a higher success rate than waterfall?

1c. Based on the case why do you think the Scrum approach makes IS development faster?

1d. The case provides a positive perspective on Scrum, what are some of the possible downsides and arguments for Waterfall?

Files for this tutorial:

.     10-IF_VLOOKUP COUNTIF.xlsx

.    VLOOKUP-Introduction.xlsx

In the LinkedIn.com video, the VLOOKUP (working with a vertically organized data lookup table)

and HLOOKUP (working with a horizontally organized data lookup table) functions are introduced.

We shall concentrate on VLOOKUP, and this function features heavily in the assignment Part C.

It is assumed that students have already worked through the LinkedIn video, and its associated file 10- IF VLOOKUP COUNTIF.xlsx. We want to thoroughly understand VLOOKUP.

We want to use the VLOOKUP function to see how it works and also contrast its use with nested IF functions that we should have developed and used previously. Let’s look firstly at the VLOOKUP    syntax.

VLOOKUP SYNTAX

VLOOKUP (lookup_value,table_array,col_index_num,[range_lookup])

The VLOOKUP function syntax has the following arguments:

. lookup_value This always must be supplied. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A error value.

. table_array This always must be supplied. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

. col_index_num This always must be supplied. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

If the col_index_num argument is:

o Less than 1, VLOOKUP returns the #VALUE! error value.

o Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value

range_lookup This parameter/argument is optional (THIS IS INDICATED BY THE SQUARE

BRACKETS). A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. We can use a logical value explicitly (FALSE/TRUE) or a numeric indicator to these (i.e. 0 / 1).

NOTE WELL THE FOLLOWING:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct   value.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted. If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the

lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Using VLOOKUP to calculate grade (worksheet: VLOOKUP_Introduction.xlsx)

1.   Within the file VLOOKUP_Introduction.xlsx, open the worksheet VLOOKUP-Introduction-1 and view the nested IF in cell F13. It looks complicated – and it is! There is a better way with

VLOOKUP.

2.   We want a VLOOKUP in cell G13 to calculate the resulting grade for each student. Remember,

we want an approximate match (why) therefore this means we use a fourth parameter/argument of TRUE (or 1).

3.   We also want to use absolute addressing for our table references – why? Each student should

ensure that they fully understand this formula, the meaning of each parameter/argument, and how VLOOKUP finds the answer to the query. For example, what changes would we need to make to  our grade table if we were to code “FALSE” as the fourth parameter?

Using VLOOKUP to describe type of day (worksheet: VLOOKUP_Introduction.xlsx)

1.   Within the tutorial file VLOOKUP_Introduction.xlsx, you will find the following data in the

worksheet VLOOKUP-Introduction-2. The first column (A) represents a range of ‘random’

temperatures. Column B represents a description of the ‘type of day’ and we must develop a

formula to complete this column. The columns D, E, F represent the appropriate “rule” to describe a particular day, for example, if the temperature is 28 or above – but less than 33 degrees – then it  is a “hot” day.

2.   Our goal is to develop a formula in cells B8:B13 that automatically describes the type of day    given the actual temperature reading in the range A8:A13. Now we can develop our formula in cell B8 firstly – then copy it down through the range B8:B13. Remember to use ABSOLUTE    addressing for the table reference argument in the VLOOKUP function call.

3.   Now copy this formula down through the range B8:B13 and notice that the absolute addressing prevents the table_array references from being altered by Excel – this is exactly what we

want. What would happen if we left the table_array in relative address form? You should answer this question by saying exactly what Excel would do during the copy operation, and what this would do for our formula (try it out if you do not know – and then explain it).

4.   There is one more point to consider. What if we have a really cold day – say a temperature

reading of - 10 degrees (i.e., minus 10 degrees). Try this out and enter -10 into one of the cells.  The VLOOKUP cannot find an appropriate value and therefore returns us the message “#N/A”, which means an answer is not. If we want rigorous Excel formulas, we must allow for this. We  can use an IF function and use it to “wrap” our VLOOKUP to deal with “extreme values”. The  logic would be (for example) as follows:

5.   IF (temp < 0, “below zero”, VLOOKUP(……))

6.   Try to develop this formula and get it running properly. This is a very important principle –

mixing IF and VLOOKUP functions so that the resulting formula works for all possible values.

7.   Now in the two other tables in VLOOKUP-Introduction-2 (the tables with the YELLOW

headers), we need a formula in B18 (and the cells directly below) that ‘looks up’ the ‘type of day’ in the TYPE OF DAY table. In this VLOOKUP we need to look for an exact match, therefore our 4th argument to VLOOKUP needs to be coded accordingly.

Excel work – Named Ranges

1.   So far, we have entered the range for our table-array argument with absolute referencing. The

better way to enter ranges such as for table_array is to create named ranges. In this exercise, we  want to create the following named ranges (Temperature_Range_Table and Type_of_Day_Table) for the data tables shown in the worksheet VLOOKUP-Introduction-2.

2.   We must access the Formulas tab, and then click on Name Manager. The Name Manager lets us  create named ranges (New), change existing named ranges (Edit), and also delete existing named ranges (Delete). We should take note that Named Ranges must contain legal Excel naming

conventions (e.g. no spaces).

3.   So now, go ahead and create the named ranges. In this case we shall create the named ranges to    cover only the data cells in the tables (i.e. do not include any coloured cells in the named ranges). When finished, click on the Name Box (top left of the spreadsheet) and see the drop-down list of named ranges after you create them and verify the range identifies the correct cell/cells.

4.   Now go back and change the absolute address references to Named Range references – this is very much the more professional way to ensure our Excel formulas are more user-friendly.

Excel work – Absolute, relative and mixed referencing (worksheet: Referencing.xlsx)

We have mostly used absolute and relative reference in our formulas. But in some instances, it is more efficient to use mixed referencing.

By default, a cell reference is a relative reference, which means that the reference is relative to the

location of the cell. If, for example, you refer to cell A2 from cell C2, you are actually referring to a  cell that is two columns to the left (C minus A)—in the same row (2). When you copy a formula that contains a relative cell reference, that reference in the formula will change.