Task 3: Using advanced formulas

Wallmores determine their Day Rates of payment to workers based on their completed Stacks per hour. Each working day is 8 hours long. Wallmores uses a lookup table with a Day rate of payment that changes according to the number of Stacks per hour that a worker completes Up to 4 stacks per hour, the more stacks completed, the higher the Day rate of pay. You will use the range B11 to M11 (Day Rate) for the formulas to calculate each workers Day Rate of payment. Starting from row 16, there is a table on the spreadsheet showing the Day Rates that apply to workers.

We are now going to set up a lookup table with a named range. Select the data section of the spreadsheet which has the headings Stacks/hr and Day Rate. Do not select the headings. When you have selected the correct range, name the range Day_Rates.

In cell B11, use either the Lookup function or VLookup function to find the day rate of payment that applies to Jerrys Stacks per hour for the day. Use relative referencing to copy the formula from this cell all the way to cell M11

You will now create an IF formula to determine if the Wallmores bricklaying team has laid enough bricks to be considered acceptable. In cell O17, enter an IF formula that returns the answer Yes! if the Total Brick Stacks Laid in cell N9 is greater than 350. If not, the formula should return the answer No.