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 (<11 years) Number of groups of 3 Rail Runners 0.00 Companions (<19 years) 0 Train tickets Return journey Rail Runner discount (<11 years) Number of groups of 3 Rail Runners 0.00 Companions (<19 years) 0 Train tickets Children Date of birth to qualify for Road Runner for outward journey Date of birth to qualify for Road Runner for return journey First name Last name Date of birth Vegetarian Outward journey Return journey Ilona Adam 23/May/10 Caroline MacDermont 9/Jan/10 Judith Black 5/May/11 yes Fanny Anderson 22/Aug/07 Natasha Bossewell 9/Dec/99 Emma Brault 28/Mar/99 yes Sarah Bromley 6/May/10 yes Karin Bromley 7/Aug/09 Irene O'Connor 5/Feb/07 Mary Elvin 13/Dec/08 Anne Dower 19/Jul/09 Hannah Steward 14/Jul/09 Martha Flegg 2/Jan/04 Francis Humphrey 7/Nov/11 Lucy Smith 6/Mar/10 Francine Legh 19/Mar/11 yes Eleanor Goods 4/Apr/07 Ada Thomas 3/Nov/08 Susan Gabriel 4/Oct/05 Mary Fox 1/Oct/08 Mary Jane Friar 14/Jul/09 Jane Maclagan 3/Mar/08 Agnes Parker 9/Aug/05 Maria Papworth 21/Jul/07 Danielle Pye 4/Sep/05 Catherine Roger 4/Mar/09 Lenie Sorrell 5/Sep/11 Ida Stone 17/Aug/09 Sanne Beckmann 21/Dec/05 Kim Decker 21/Oct/08 Eve Willis 15/Jul/06 Annie Simpson 21/Sep/07 yes Bertie Wagner 19/Jan/07 Clars Withycombe 30/Nov/08 yes Eliza Woodcock 16/Apr/09 Angelina Young 16/Apr/08 Maud Abson 24/May/09 Annie Backhouse 14/Mar/94 yes Daisy Bateson 6/Aug/07 Edna Carlton 31/Dec/03 Alice Capstick 18/Oct/05 Francine Carlisle 26/May/92 Ingrid Eaves 10/Dec/08 Charlotte Eastwood 9/Feb/94 Clara Parnaby 3/Apr/93 Ruth Parker] 26/Oct/03 Eleanor Vernon 25/Jul/07 Jane Vaughan 11/Jul/05 Dorothee Vitty 2/Sep/04 Marianne Lambert 27/Mar/07 Cyrille Adam 2/Feb/08 Amelia Ladley 18/Oct/09 Asha Cohen 19/May/06 Clara Willis 7/Mar/07 Edith Abbott 27/Oct/09 Estelle Atkinson 12/Jul/07 Esther Barstow 3/Dec/08 Anouk Austin 6/Jun/07 Cynthia Carey 4/Jun/08 Ester Parnell 5/Dec/07 Mendy Barnaby 3/Jun/09 Karin Cardwell 5/Sep/07 Inka McIntry 5/Mar/08 Ellen Abelson 3/Sep/08 Susan Allen 4/Mar/09 Trudy Brown 3/Sep/08 Alice Stonewell 13/Dec/07 Christine Blackstone 23/Mar/07 Chista Patterson 6/Jul/09 Marleen Leftfield 20/Apr/07 Anne Allgate 31/Dec/08 yes Ester Humphrey 18/May/07 Sita Delmee 1/Jun/07 Petra Colbeck 15/Jun/07 Edna Pitts 29/Jun/07 Angelina Place 11/Jun/08 Iris Abbott 18/Feb/09 Daisy Addams 4/Feb/08 Marlon Dunger 24/Aug/07 Harrier Otter 7/Sep/07 Emmanuel Goldfinger 28/Jul/10 Linda Weston 6/Mar/10 Matilda South 18/Oct/08 Ellen Auckland 4/May/07 Sasha Austin 19/Dec/09 Ellen Pitts 30/Nov/07 Iris Glapthorn 12/Jul/09 Wilma James 15/Nov/08 Sasha Edward 9/Aug/09 Hanelore Roosevelt 22/May/09 Lisette Churchill 2/Aug/09 Evelien Allcaster 13/Oct/09 Simone Feather 24/Dec/09 Sasha Aaron 5/Oct/07 Meriam Backhouse 17/May/10 Sasha Adams 2/Nov/07 Danielle Robin 8/Oct/10 Marie Great 19/Dec/10 Muriel Brown 27/Jul/07 Mariette Stonewell 6/Apr/07 Irene Blackstone 20/Oct/06 Ilse Patterson 17/May/10 Priscilla Leftfield 2/Mar/09 Barbara Allgate 16/Dec/09 Mary Jane Humphrey 4/Jul/09 Andy Delmee 28/Oct/10 Wilma Colbeck 1/Nov/09 yes Isabella Remmits 31/Dec/09 Helen Austerfield 2/Sep/09 Dorien Pitchford 1/May/10 Ethel Ladley 30/Jun/10 Ida Flegg 20/Sep/09 Henrietta Feet 2/Mar/10 Penelope Rye 27/Dec/10 Leonora Greenfield 25/Feb/11 Vegetarians

( 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