Do My Homework
/
Homework Help Answers
/
Business Homework Help
/ Formulating Functions in a pre-made incomplete excel sheet
Formulating Functions in a pre-made incomplete excel sheet
Need help with this question or any other Business assignment help task?
Hi there,
This is my first time submitting a question here. As I am extremely busy with multiple deadlines from several courses. I overlooked this one particular assignment have don't have enough time to complete it. The assignment consists of basic Excel functions such as VLOOKUP, COUNTIF, SUMIF.
If you have the time to complete this within my desired completion date. I would greatly appreciate it.
P.S. The Microsoft Word doc is a guideline on what to do. The Excel sheet is where you must complete the work.
Additional Instructions:
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
There are no answers to this question.
Login to buy an answer or post yours. You can also vote on other
others
Get Help With a similar task to - Formulating Functions in a pre-made incomplete excel sheet
Related Questions
Similar orders to
Formulating Functions in a pre-made incomplete excel sheet
386
Views
2
Answers
393
Views
2
Answers
Tutlance Experts offer help in a wide range of topics. Here are some
of our top services:
- Online writing help
- Online homework help
- Personal statement help
- Essay writing help
- Research paper help
- Term paper help
- Do my homework
- Online assignment help
- Online class help
- Dissertation help
- Thesis help
- Proofreading and editing help
- Lab report writing help
- Case study writing help
- White paper writing help
- Letter writing help
- Resume writing help
Post your project now for free and watch professional experts outbid each other in just a few minutes.