Charts
Charts can be used to graphically represent your spreadsheet.To create a simple chart, select the spreadsheet, and from the Insert ribbon, choose the type of chart you want to create.
It sounds simple - well it is!
When your Chart is selected, you have some extra menu options.
You can use these to manipulate your chart easily.
or here
ACTIVITY 1
Using the same data, create a Pie Chart.
ACTIVITY 2
Using the Spreadsheet from last week, create a 3D Bar Chart.
Try removing the Blank lines
• Click on the Select Data button. This will show you the data ranges that are included in your chart.
• Remove the two blank areas by clicking on each and clicking remove, then click OK.
• If your chart does not look like you expected, try using the Switch Row/Column button
• Add a title by clicking on Layout Ribbon, Chart Title and Above Chart.
• Type the heading into the text box that appears.
FUNctions
So far we have really only looked at one function - the AutoSum function. Excel actually has lots of inbuilt functions that can be used to perform heaps of different tasks. The advantage of using functions is that you don't need to worry about how the calculation is performed, all you need to do is to tell Excel what values it needs to use for the functions.Simple Functions
Use file 2015BasicFunctions.xlsxThe simple functions can be easily accessed from the Autosum button on the Home Ribbon.
Use the drop down options to choose what type of calculation you want.
Start by adding in the formulas to complete the spreadsheet.
Start by using Autosum to total the items for 2015.
Once you complete 2015, you can copy the formula across to the other years.
Use the other functions from the drop down in Autosum to complete the Average, Minimum (Min), Maximum (Max) and Count formulas from the simple formula button, the range should include all 5 years.
Once you complete the formula for Swimwear, you copy the formula down for the other items.
This is what mine looks like
Formulas Ribbon
The Formulas TAB can be used to access all of Excel’s huge range of in built Functions.
Insert Function will open the Function Search facility.
The icons within the Function Library section incorporate all of Excel’s Functions broken down into categories.
These notes will not cover all of the possible functions, but will look at the ones most commonly used. By all means, experiment with any of the others once you are comfortable with the process.
Financial Functions
There are many inbuilt Financial Functions. They include things like Present and Future values, Interest Rates, Internal rate of Return and Depreciation as well as Functions relating to Securities.
The amounts for formulas can be entered straight into the Function Arguments box or entered as Cell References. Using Cell References allows you to do simple changes to the values and see the effect immediately.
The first function we will look at is Depreciation – Fixed Declining Balance. Depreciation is used by business to write off an asset over a period of time, usually similar to the expected life of the asset.
Use File 2015Financial Functions.xlsx
Start by clicking in the cell where we want the first year’s depreciation to go in. This is B10. Then select the function from the list – the one we want is DB.
Complete the depreciation for all 4 years. As I have mentioned, you dn;t need to know how it calculates the answer - give the formula the information it requires and assume that the answer is correct.
Our second financial function is going to be PMT – this will calculate the repayments for a loan.
Start with your cursor in cell B20 and select the function.
The Rate is the rate for the loan, but it must match the frequency of repayments. If you are looking Monthly repayments, the annual interest rate needs to be divided by 12.
The advantage of using the cell references is that you can play with the figures. If you find that you can’t afford the repayments, you can try with a different loan amount or even different interest rate.
Activity 3
Set up a spreadsheet that will compare a couple of loans so that you can make a decision on how much you can afford to borrow.- The interest rate will be the same as you have negotiated with your credit union for a great rate on a car loan.
- The annual rate will be 9%. You will be making fortnightly repayments to correspond with your wages payments.
- You want to compare how much the payments will be for a loan of $6500, $8000, $10000 and $15000.
Logical and Lookup Functions
The most commonly used Logical Function is the IF Function. This simple spreadsheet is completed using an IF statement and a LOOKUP function.Use file 2015IfLookupBasic.xlsx
The first part of this exercise is to get the Hourly Rates into the spreadsheet from the Pay Rates Table using the VLOOKUP function.
The function will use the value that is in column B and find it in the lookup table. You can then use the values in any adjacent columns. The lookup column itself is column 1, so values can be in column 2 or higher if your lookup table contains multiple columns.
N.B. The table that you are looking up into, MUST have the lookup column sorted lowest to highest i.e. A - Z; 0 - 99
Start in cell D7
A simple calculation can be used to get the total Pay.
We would like to award all of our employees who have worked more than 20 hours this week.
Using an IF function, we can highlight this information very easily.
The first part of this function requires a logical test that can equate to being either true or false. This would usually be done using comparison operators greater than (>), Less than (<) or equal to (=)
= (equal sign) A1=B1
> (greater than sign) A1>B1
< (less than sign) A1<B1
>= (greater than or equal to sign) A1>=B1
<= (less than or equal to sign) A1<=B1
<> (not equal to sign) A1<>B1
The rest of the IF function tells Excel what to do if the comparison is TRUE and then what to do if it is FALSE.
Text Functions
As well as functions that deal with numbers, there are a range of functions that deal with text within cells. These Functions include Concatenate, Trim, Upper and Proper. The following spreadsheet shows an example of a couple of the Text Functions.Use file 2015TextFunctions.xlsx
The completed spreadsheet would look like the following:
Date and Time Functions
Excel also has functions that deal with Dates and Times. Excel knows about dates and as well as the inbuilt functions, you can use dates in calculations. There are functions that will give you the day of the week and today’s current date and or time.=Today()
=Now()
Today() and Now() will both give you the current date. The difference between the two is that Now() will also include the time. Excel will assume that numbers entered in a certain way are dates and will display them as such, even though Excel really treats the date like a number. The date is actually a number based on the number of days since 1/1/1900. This date actually has a date number of 1. Because Excel stores dates as numbers, you can do what I call "date maths". It is also how Excel compares dates so you can subtract one date from another and work out how many days are between the two dates.
Naming Ranges
As well as using the official range name such as A1:D20, you can give a range of cells a more meaningful name, which can then be used in formulas and functions. In the Formulas TAB there is a section that relates to named areas.You can use these buttons to manage named areas within your spreadsheet - naming is not compulsory, but can make it easier to modify a formula later as the names have more meaning than the default Excel range.
You can create a range name quickly by selecting the range on the spreadsheet and typing in the name in the Name Box.
Now is the time to practice our skills that we have just learnt.
Activity 4 - Simple Exercise
Complete the following spreadsheet given that Full Fee students will pay $4.75 per hour and Health Care Card (HCC) students will pay $1.20
Use file 2015SimpleExercise.xlsx
Watch the solution on YouTube
Activity 5 - Really Great Fun Exercise
Use file 2015funexercise.xlsx
The following information relates to this spreadsheet:
• Assume that the date returned is 1/04/2015
• Overdue – was the item overdue? If overdue, this should say "Yes", otherwise "No".
• Days Overdue – how many days is the item overdue by?
• Fine Amount – DVDs are fined at $1.00 per day. Books are fined at $0.25 per day.
Watch the solution on Youtube
No comments:
Post a Comment