Week 2: Excel Introduction.
In the next following weeks, we will look at using Excel to develop a graphic solution.
Even though you may be highly skilled already or have a lot of knowledge in excel - you still need to complete these tasks and hand in. If you finish the required tasks early, we'll look at uping your skills and giving you harder work. Sometimes a refresher on how to use something can also be worthwhile and this will give me an idea of what you know!
We'll be using Excel 2010/2013.
The skills you will need to demonstrate and learn:
To begin with open and practice with the following file to go over the how to's of Excel. This is a very basic introduction and you will cover more later.
We'll be using Excel 2010/2013.
The skills you will need to demonstrate and learn:
- Cell Referencing
- Mathematical Functions (Sum, AutoSum, =, Average, Min, Max, <,>,=<,=>, Count, Product)
- Formatting cells (Integer, Floating Point, Currency, Date, Percentage)
- Conditional Formulas (IF, AND, OR, NOT)
- VLOOKUP (extension only)
- Date and Time Formulas
- Data Validation (Range Checking, Existence Checking, Data Type checking, Restricting Data Entry, Creating Alerts)
- Manipulating Visual Appearance of Data (Font, Colours, Borders, Alignment, Headings, Tables)
- Creating Graphical Representations of Data
To begin with open and practice with the following file to go over the how to's of Excel. This is a very basic introduction and you will cover more later.
excel_basic_tutorial.pdf |
Exercise 1: [Needs to be Submitted]
a) Duplicate the image below in Excel:
b) Once you have duplicated the above image, apply a discount rate of 15%. Use AutoFill for the cells.
c) Now, using a formula, work out the discount price for each item.
d) In cells A12 and C12, create the headings Total Price: & Total Amount Due:
e) In cells B12 and D12, use the AutoSum function to calculate the Total Price and the Total Amount Due.
Save this File as Ex1YourName.xls and submit.
c) Now, using a formula, work out the discount price for each item.
d) In cells A12 and C12, create the headings Total Price: & Total Amount Due:
e) In cells B12 and D12, use the AutoSum function to calculate the Total Price and the Total Amount Due.
Save this File as Ex1YourName.xls and submit.
Exercise 2: [Needs to be Submitted]
Next you will learn how to use other Mathematical functions (Sum, Average, Min, Max and Count)
a) Open the following Excel File
a) Open the following Excel File
sumavcount.xlsx |
Using MIN and MAX
1. In cell C13 type =MIN( .
Then select the cells C6:C12, by first selecting C6, then holding down the SHIFT key, select C12.
Ensure you close the formula with )
What was the minimum year?
2. Now using the same cells use MAX instead of MIN.What is the Maximum year?
Then select the cells C6:C12, by first selecting C6, then holding down the SHIFT key, select C12.
Ensure you close the formula with )
What was the minimum year?
2. Now using the same cells use MAX instead of MIN.What is the Maximum year?
Using the SUM and AutoSum function
Your result should be 15.
Using the Count Function
Using the Count Function
4. Now in the same cell F13, change SUM to COUNT.What's the difference? What does the COUNT function do?
Using the Average function
5. In cell D13, work out the Average Mileage by typing =Average(. Then select cells D6:D12. Then close with the ).What was your result?
Using the Product function
6.
a) In cell I6, type =PRODUCT(.Then select cell E6. Type a comma ,.
Then select cell F6 and use a closed bracket.
You should have the below result:
a) In cell I6, type =PRODUCT(.Then select cell E6. Type a comma ,.
Then select cell F6 and use a closed bracket.
You should have the below result:
b) Do the same for the cells I7:I12. You should have the below results.
What does the PRODUCT function do?
7. Save your work as Ex2Yourname.xls and submit via compass.
7. Save your work as Ex2Yourname.xls and submit via compass.
Exercise 3: [Needs to be Submitted]
1. Open the following:
ex3.xlsx |
2. Enter in the data for the columns in :
GameName |
Category |
Year |
Price |
Quantity |
Platform |
Description |
3.
a) In cell A13, use the COUNT function for the range of cells A6:A12
b) In cell E13, find the Total Quantity
c) In cell H6:12 calculate the Total Price, using the PRODUCT function
d) In C13, find the MIN Year
e) In D13, find the MAX Price
f) Apply a Discount of 10% and then calculate the Discount Price for each game.
g) Save your work as Ex3Yourname.xls and submit via compass.
a) In cell A13, use the COUNT function for the range of cells A6:A12
b) In cell E13, find the Total Quantity
c) In cell H6:12 calculate the Total Price, using the PRODUCT function
d) In C13, find the MIN Year
e) In D13, find the MAX Price
f) Apply a Discount of 10% and then calculate the Discount Price for each game.
g) Save your work as Ex3Yourname.xls and submit via compass.
CONDITIONAL STATEMENTS
Next you will learn how to use a Conditional Statement. This is known as the IF function. You will need to do the following, but it does not need to be submitted.
Think of the following Scenario:
If you get above 50% in your outcomes, you will get a S (Satisfactory), else you will get an N (Unsatisfactory).
This is an example of the IF function.
Excel also uses the IF statement.
Open the following file:
if.xlsx |
Make sure you are in the IF worksheet.
This is an invoice for a customer. Let's say we only want to apply a discount of 10% to any item that our customer has bought 3 or more of the same items. Therefore a discount will only apply to the Pencil and Book items. How can we do this using an IF statement?
This is an invoice for a customer. Let's say we only want to apply a discount of 10% to any item that our customer has bought 3 or more of the same items. Therefore a discount will only apply to the Pencil and Book items. How can we do this using an IF statement?
- Select cell D2.
- Click on the fx
You should see the following screen appear:
3. Select IF and hit OK.
If you don’t see IF in your list, run a search for it by typing IF in the search area and hit GO.
If you don’t see IF in your list, run a search for it by typing IF in the search area and hit GO.
4. You will now see once you have selected the IF and hit Ok the below screen:
5. Here we need to input what the logical test is.So remember we want to see if there are 3 or more of the item. So we will be looking at the Quantity column.
Type the following math problem C2>=3
This identifies cell C2 that we are checking and asks is it greater than or equal to 3?
Type the following math problem C2>=3
This identifies cell C2 that we are checking and asks is it greater than or equal to 3?
You will see False here as C2 holds the Quantity 1.
Try it with C3 - does it say TRUE?
6. Now, we need to tell Excel what to display in our selected cell, (the one where our cursor was D2!) if the statement is TRUE and if it is FALSE. Input 10% if TRUE and 0 if FALSE
Try it with C3 - does it say TRUE?
6. Now, we need to tell Excel what to display in our selected cell, (the one where our cursor was D2!) if the statement is TRUE and if it is FALSE. Input 10% if TRUE and 0 if FALSE
Hit OK.
7. Now, practice by checking if there is a discount that applies to the Pencil, Book and Ruler. You should have the following results:
7. Now, practice by checking if there is a discount that applies to the Pencil, Book and Ruler. You should have the following results:
8. Now calculate the Discount and Amount Due columns.
Exercise 4: [Needs to be submitted]
1. Open the following and answer the questions within the file.
ex4.xlsx |
2. Save as Ex4YourName.xls and submit.
Submit all Files in Schol.