5. Prepare an Excel sheet with the data as shown in Figure 2-41.

a. Write MATCH and VLOOKUP functions in cells K2 and K3. The MATCH function returns the column number of the field Actual. The VLOOKUP function then returns the actual cost of Item6 (BackfiJIAround Foundation).

Figure 2-40. Excel Sheet for Exercise 4.

Foundatron BaCk1U 1 ~:JOO 400[1 Possible Tas~d T;,SKL laSk~. '---.j.:~: ~~~~ill~~~==E~~=Z:3~iC}

- I ask I 1< Lquunnenl r;::-....,,-;-:-;-,-. - I Po«ible v.,ueS:!l Labo{

EqlJipme:'11 I Material

~O'U?i·FJu~~IIJn·lilHo.O) I(d)

(c)

~~~==~===+~~~~~~~~~~~~~~=+ ~a)

~~~~--~~7+~~7+~~~~~~+==-~--~b)

Figure 2-41. Excel Sheet for Exercise 5.

Figure 2-42. Excel List for Exercise 6.

Figure 2-43. Excel Sheets for

::xercise 7.

I>-lFORlvlATION MANAGEMENT TOOLS I 61

b. Use the INDEX function in cell K4 to find the Planned Cost of Item11.

c. Use the DMIN function in cell K15 to find the minimum Total Cost for all items that apply to the criteria range in cells A15:I16 (WBS is Formwork and Quantity is more than 100 m2).

d. Use the DSUM function in cell K18 to find the summation of the Actual Cost of all items that apply to the criteria range in cells A18:I19 eWBSis Formwork and Unit Cost is more than 10).

e. Use the DSTDEV function in cell K20 to find the standard deviation of the Planned Cost for all items that apply to the criteria range in cells A20: 121.

f. Use the OFFSET function in the shaded range (G23:K25) to return the same values shown in the shaded range (D8:HIO).