Formulating Functions in a pre-made incomplete excel sheet

Calculations Order nr. 3 Item Employee Total Price Units sold Employee Gill Amount orders from Employee Units sold by Employee Bonus Database Order nr. Region Employee Item Units Unit Cost 1 Alberta Jones Pencils 95 € 1.99 2 Ontario Kivell Binders 50 € 19.99 3 Ontario Jardine Pencils 36 € 4.99 4 Alberta Gill Pen 27 € 19.99 5 Alberta Sorvino Pencils 100 € 19.99 6 Quebec Jones Binders 60 € 4.99 7 Ontario Andrews Pencils 12 € 1.99 8 Ontario Jardine Pencils 90 € 4.99 9 Alberta Thompson Pencils 32 € 1.99 10 Alberta Jones Binders 60 € 8.99 11 Quebec Howard Binders 29 € 1.00 12 Alberta Jones Pencils 35 € 4.99 13 Quebec Jones Pen Sets 16 € 1.29 14 Quebec Jones Pen 64 € 8.99 15 Ontario Kivell Pen Sets 96 € 4.99 16 Quebec Parent Pen Sets 74 € 1.29 17 Ontario Gill Binders 46 € 8.99 18 Ontario Smith Binders 87 € 15.00 19 Quebec Jones Binders 4 € 4.99 20 Alberta Sorvino Binders 7 € 19.99 21 Ontario Jardine Pen Sets 50 € 4.99 22 Ontario Andrews Pencils 66 € 1.99 23 Quebec Howard Pen 96 € 4.99 24 Ontario Gill Pencils 53 € 1.29 25 Ontario Gill Binders 80 € 8.99 26 Ontario Kivell Desk 19 € 50.00 27 Alberta Jones Pen Sets 62 € 4.99 28 Ontario Morgan Pen Sets 55 € 1.99 29 Ontario Kivell Pen Sets 42 € 5.00 30 Alberta Sorvino Desk 57 € 19.99 31 Ontario Gill Pencils 7 € 1.29 32 Alberta Sorvino Pen 76 € 1.99 33 Alberta Thompson Binders 57 € 19.99 34 Ontario Andrews Pencils 14 € 1.29 35 Ontario Jardine Binders 11 € 4.99 36 Ontario Jardine Binders 94 € 19.99 37 Ontario Andrews Binders 28 € 4.99 Train tickets Summer Camp - Vicky Outward journey 07/16/20 Return journey 07/22/20 Number of children Outward journey Rail Runner discount (

( h. )Test Functions Part1 1. Go to sheet “Calculations”. 2. Put in cell Calculations!B2 a formula/function which looks for the item name that goes with the order nr. (cell Calculations!B1) in sheet “Database”. (tip: VLOOKUP) 3. Put in cell Calculations!B3 a formula/function which looks for the name of the Employee that goes with the order nr. (cell Calculations!B1). in sheet “Database”. (tip: VLOOKUP) 4. Put in cell Calculations!B5 a formula/function which calculates the total price of the units sold. This should refer to the order number shown in cell Calculations!B1. (Tip: VLOOKUP) 5. Put in cell Calculations!B9 a formula/function which calculates the amount of orders of employee Gill. (tip: COUNTIF) 6. Put in cell Calculations!B10 a formula/function which calculates the total amount of units sold by employee Gill. (tip: SUMIF) 7. Put in cell Calculations!B11 a formula/function which will show a “yes” if the amount of orders in cell Calculations!B9 is higher or the same as 5 and a “no” if it is lower than 5. Part 2 1. Summer scout camp will run from July 16th to July 27th, 2020 Group leader Vicky will accompany a group of children by train, both going to camp (the outward journey) and coming home again afterwards (the return journey). You must calculate how many train tickets are needed. Some children are younger than 11 years old and qualify for a discounted Rail Runner ticket. There must be a leader who is at least 19 years old or a parent for each three Rail Runners. In the Train tickets worksheet, you will calculate the number of train tickets that must be bought. The Children worksheet contains a database of information about the children. In the Children worksheet, in cell F1 calculate the date before which the child must be born in order to obtain a Rail Runner ticket for the outward journey. This is the date of the outward journey minus 11 years. Take into account the leap years in this period of 11 years. 2. In cell F2, calculate the date before which children must be born in order to obtain a Railrunner ticket for the return journey. The leap years applies here as well. 3. In the Children worksheet, in the cell range E6:F120 calculate which children qualify for a Rail Runner ticket for each journey. If a child qualifies, generate the number 1, otherwise generate the number 0. 4. In the Train tickets worksheet, in cells 86 and 812 calculate the total number of children in the Train tickets worksheet who qualify for Rail Runner tickets for the outward and return journeys. 5. Look at the formulas in cells B7 and B13. 6. In cell B4, calculate the total number of children. 7. In cells 88 and 814, calculate the number of ordinary train tickets needed for each journey. Note: Ordinary tickets are the tickets for the leaders and for the children who do not qualify for Rail Runner tickets