5.2 F UNCTIONS
5.2.3 Common Functions
This is the most common of all functions and adds a column or row of numbers. As SUM is the most common it has been placed on the standard toolbar and the button is called AutoSum .
Select the cell where the sum is required.
Click the AutoSum button .
The function is entered with a suggested range.
It uses the numbers in close proximity to the active cell. If the range to sum is incorrect (it stops at a blank cell), click and drag the required range.
Press <Enter> to complete the function and enter the result.
Note: If numbers have been summed to create totals then the AutoSum button sums the sub totals and ignores the individual numbers, in effect creating a grand total.
All other functions can be entered using the Insert Function button , although they can be typed if the correct syntax is known.
Average
The AVERAGE function combines a simple formula, a sum divided by a count. An example of the function:
=AVERAGE (C5:C19) To enter the Average function
Select the cell where the Average is required.
Click the Insert Function button to display the Insert Function dialog box.
Select Average from within Select a function.
Click OK to display the Average function dialog box.
If the range taken from the numbers suggested in the Number 1 cell matches the required range, click OK.
If not,
Click the collapse button, , to return to the worksheet to select the required range.
Or
Note: Click on the expand button to review the dialog box.
Type in the arguments in Number1.
Click OK to complete the function entry and display the result.
Count
Count is a statistical function that only counts non-blank numeric cells (0 is counted, blank cells are not). Count is used to analyse large lists where the data changes frequently.
Max
Max (maximum) is a statistical function that returns the largest number in a specified range.
Min
Min (minimum) is a statistical function that returns the smallest number in a specified range.
When used together, MAX and MIN give the spread in a range of numbers.
Standard Deviation
The STDEV (standard deviation) is a statistical function and is a measure of how far values vary from the average.
There are other variations of standard deviation:
STDEVA Statistical function, standard deviation based on a sample including logical values and text
STDEVP Statistical function, standard deviation of an entire population
DSTDEV Database function, standard deviation of a range in a database
DSTDEVP Database function, standard deviation of an entire population in a database
IF
The logical function IF compares the contents of a cell and, if a logical test is met, performs one action and, if not, perform another.
Select the cell for the result.
Click the Insert Function button to display the Insert Function dialog box.
Select Logical from the Function category.
Select IF from within Function name.
Click OK to display the IF function dialog box.
As an example:
for Logical_test enter A1<10 in Value_if_true enter A1*3 in Value_if_false enter A1*2
Click OK to complete the entry
Enter a number in A1 and if the contents of cell A1 is less than 10 then the number is multiplied by 3, if not, the number is multiplied by 2. The function is expressed as:
=IF(A1<10,A1*3,A1*2)
The IF function is sometimes described as IF Then Else. IF the condition is true, Then do this, Else do that.
Rank ( )
It returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
Syntax: =RANK (Number, Ref, Order) where
Number: is the number whose rank you want to find.
Ref: is an array of, or a reference to, a list of numbers. Nonnumeric
Order: is a number specifying how to rank number.
If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.
If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.
Remarks: RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
Examples: If A1:A5 contain the numbers 7, 3.5, 3.5, 1, and 2, respectively, then:
RANK (A2, A1:A5, 1) equals 3 RANK (A1, A1:A5, 1) equals 5 5.3 Text Functions
5.3.1 Left, Right and Mid
A Text Function is a function which deals specifically with strings of text: letters, words and phrases. The text functions Left, Right and Mid are used to extract just part of the information from within a cell.
The LEFT Function
Picks the number of stated characters starting from the left of the cell.
Click the Insert Function button .
Select the category Text.
Choose Left from Function name.
Click OK.
Enter the Text or select a cell with the text in. To select a cell, click the collapse button to hide the box temporarily.
Enter the number of characters to extract.
Click OK.
Function format =LEFT(cell, no of chars) e.g. =LEFT(D5,3) The RIGHT Function
Picks the number of stated characters starting from the right of a cell
Follow the same steps as for LEFT.
The MID Function
Picks the number of stated characters starting from any character in the cell.
Follow the same steps as for LEFT but add the character number to start from in Start_num.
Function format =MID(cell, start number, no of chars) e.g.
=MID(D5,2,3)
Extracts from D5 three characters starting from the second character.
Note: The main use for text functions is to manipulate text such as initials or first names from cells containing full names or to extract information from stock numbers. Text functions are used when the cell contents are not in the required format, usually after importing the data from another source e.g. Microsoft Access or similar.
5.3.2 & and Value
The & (ampersand) symbol is used to connect the contents of two or more cells that contain text.
&Use & by typing to join cell contents
The above diagram shows & in action.
Cells A4, B4 and C4 contain base information. D4 joins all this information together to create another cell of related information.
Note: The use of speech marks around one space to separate the parts.
To use numbers as text
Sometimes a number is required to be entered as text, for example stock numbers that begin with zeros or numbers with a / in them. Excel would normally calculate such entries as if they were numbers e.g.
00345 is displayed as 345 or 12/6 is converted to a date, the 6-Dec.
To enter numbers as text start the entry with an apostrophe.
5.3.3 Upper, Lower and Proper
These functions are used to convert text entries into the required form.
LOWER (text) changes the entry into lower case. UPPER (text) changes the entry into upper case (capitals) and PROPER (text) changes the entry into lower case with capital first letters. These functions should be used in cells different to the cells holding the original data.
Lower function or Upper functionand Proper function
Note: These functions are used when data is required in a different format to that which has been entered or imported.
5.4 Exercise 5
The table below shows the lists of some students and the marks scored in 7 subjects.
1. Using MS-Excel reproduce the list.
2. Fill the various empty spaces using appropriate functions in MS-Excel.
To assign grades use the following scale.
Name Sex
Subject
Total Average Max Mark Min mark
Grade in
Rank
Maths Physics IT Chem Maths Physics IT Chem
Abeba H. F 89 87 67 90
Abreham G. M 76 87 87 67
Abreham B. M 28 98 82 45
Ahmed B. M 45 35 50 56
Ali A. M 78 54 56 45
Eyerus A. F 74 58 47 75
Hedija M. F 67 89 65 47
Maximum Minimum
Scale >80 >=65 >=50 >39 <=39
Grade A B C D F
3. Save your work in your folder by the file name Roster.
4. Insert two rows between Ali and Eyerus and add data of your own.
5. Insert a column for English between Sex and Maths and add data of your own.
6. Insert a column between Min Mark and Grade in Maths to calculate Grade in English.
7. See practically the difference between relative and absolute cell references.
6.0 CHARTS
6.1 Charts
It can be difficult to find vital information like changes in trends or performance from rows and columns of numeric data. A picture of the figures - a graph or chart, helps to identify subtle changes that may have otherwise been missed. Charting data is quick, easy and flexible.
Some of the standard chart types available are:
Column Shaded vertical columns Bar Shaded horizontal bars Line Points connected by a line Pie Data as slices of circular pie
XY(Scatter) Two data sets plotted against each other Area Shows values changing in relation to whole Doughnut Similar to a pie, but with a hole in the middle Radar Axes radiating from a central point
Stock High, Low and Close series
There are also various 3-D versions and different versions of the same chart type.
There are two ways in which charts can be created, as part of a worksheet, appearing on the sheet, with the data (an embedded chart) or as a completely separate sheet. The procedure for creating the charts is the same for both.
Sales 1996
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sales in £'s
6.2 Creating Charts
To create a chart, the ChartWizard is used. The ChartWizard takes the user through the procedure, step by step.
The data to chart can be selected as a range or multiple ranges before starting to create a chart or during the creation process.
To create a chart
Using the mouse select the data you want to create a chart of.
Select Insert | Chart or click the Chart Wizard button .
Select from Chart type.
Then from Chart sub-type.
Note: The chart can be viewed by holding Press and hold to view sample.
This only works when a range has been selected first.
Click Next>.
Step 2 of the Chart Wizard.
Check or enter Data range (the data to be charted) and Series in, check rows or columns to match the data.
Note: To select a data range click on the collapse button in the Data range box. Click and drag a range. Separate each data range by a comma. Press <Enter> or click to continue.
Click Next>.
At step 3 complete the required Chart Options. Remember to view each of the tabs to see the available options. There may be six tabs.
Click Next>.
Select either, As new sheet and name the sheet (Default Chart1), or, As object in and select a sheet to place the chart (Default is the active sheet).
Note: Selecting As object in creates an embedded chart – this topic is covered in greater detail on the next page.
Click Finish to create the chart.
TIP
To create a column chart quickly, select the data to chart and press<F11>.
6.3 Embedded Charts
As well as creating charts on a sheet by themselves they can be placed on the same sheet as the data. This is called an embedded chart.
To place an embedded chart on a sheet, at step 4 of the chart wizard select As object in: to place the chart on a worksheet. Any worksheet can be selected, the active worksheet, i.e. the location of the data source, is the default.
When complete, the chart is placed on the worksheet and can then be moved and resized.
To move an embedded chart
Handles
Click on the chart to select (the chart is already selected if the handles are displayed).
Click and drag on the chart area (the area just inside the frame) to the required position.
Release the mouse button to place the chart.
To resize an embedded chart
Select the chart by clicking on it.
Click and drag any of the chart handles in any direction, release the mouse button.
6.4 formatting charts
There are various ways in which charts can be changed and customised to the user's requirements. Practically everything on the chart, including the colours, axes, words, gridlines, background, etc., can be changed after it has been created.
To format a chart
Select the part of the chart to change (place the cursor over it and click) and select Format Selected ... or
Double click on the required part.
The Format dialog box is then displayed for that part of the chart (some parts have a tabbed dialog box). Make the changes, as required.
The above picture is the dialog box used to format an axis. Use scale to set the number range on the vertical axis.
Click OK.
6.5 Printing Charts
The method used to print charts is exactly the same as printing a worksheet. The chart can be seen with Print Preview. Page Setup can be altered, although not all features are applicable with charts.
To prepare for printing a chart
Make the chart active.
Select File | Page Setup.
Make the necessary changes within Page, Margins, Header/Footer and Chart (shown above) tabs.
Click OK when complete.
To print a chart
Make the chart active.
Select File | Print.
Check Printer type, complete Print range and Number of copies if required.
Click OK to print.
Note: If a chart is embedded on a worksheet the whole sheet can be printed normally (including the chart) or the chart only can be printed by selecting it first.
TIP
To print a single copy of a chart with all the default options, make the chart active by selecting it and then click the Print button on the Toolbar.6.6 Chart Menu Options
The Chart drop down list on the menu bar controls all the features relevant to charts, their construction, location and appearance.
Chart menu options Make the chart active
Select the Chart from the menu bar.
Chart Type: Allows a change of chart type.
Source Data: Two tabs Range and Series to make changes to the data.
Chart Options: Six tabs to change titles, axes, legends, etc.
Location: Allows a change in chart location, embedded/on new sheet.
Add Data: Additional data ranges added to the chart.
Add Trendline: Adds a trendline.
3D View: Changes the chart perspective.
Choose the required sub menu.
Make the required changes.
Click OK.
6.7 TRENDLINES
Trendlines can be added to charts to show a pattern in a set of data.
They can only be added to unstacked 2D, bar area, column, line, stock, XY scatter and bubble charts.
They can used to display forward and backward forecasting.
Adding a Trendline
On the chart, click on a data series.
Select Chart | Add Trendline or right click and choose Trendline.
On the Type tab, select the required option from the first five (Moving Average is covered next).
Note: The Polynomial option has an order box attached.
Click the Options tab.
This is used for controlling Name, Forecast (Forward and Backward) and three check box options.
Click OK when complete.
To format a Trendline
Either double clicks on the Trendline or right click and select Format Trendline.
The same Type and Options tabs are present, but with Patterns (for formatting the line) added. Click OK when complete.
6.8 3d Chart View
To change the 3D view of a chart
Select Chart | 3D View.
Adjust the sideways and/or vertical rotation of the chart.
Click OK when complete (use the Default button to return to the start position).
To format 3D charts
Double click on the data series or data point.
There is a Shape tab, to change column shape, select the required shape.
Select Series Order tab to move any series up or down using the Move Up, Move Down buttons.
The materials series is hiding the rest (it can be moved down to display the others in front).
6.9 Data Labels and Markers To add data labels
In the chart double click on a data series or select a single data point and double click.
The Format Data Series or Format Data Point dialog box is displayed.
Click the Data Labels tab.
Select from the available options and click OK to display the data labels.
To create picture markers in a chart
Picture markers (graphic objects) can be added to bar and column charts.
Copy the picture or object from any source with Edit | Copy.
Select Edit | Paste.
Sales
0 500 1000 1500 2000
Jan Feb Mar Apr
Sales
An example of a Sales Chart with WordArt added 6.10 Stacked Bars
To display a stacked bar
When creating a chart select Column in Chart type.
There are 4 options:
Compare the contribution of each value to a total across categories 2D.
100% stacked compares the percentage of each value 2D.
Compare the contribution of each value to a total across categories 3D.
100% stacked compares the percentage of each value 3D.
Select the required option. Use the Press and hold to view sample button to see results.
Create the rest of the chart as normal.
6.11 Protecting Excel Files
Excel offers a very similar style of protection to Microsoft Word. Just click on File, Save As and follow these steps:
1. Click on Tools in the upper right corner of the file save dialog box 2. Click on General Options
3. You can enter a password in the box next to Password to open if you wish the file to be completely inaccessible without the password
o You can click on the Advanced button next to the password box to choose a higher level of encryption that is even harder to break into 4. You can enter a password in the box next to Password to modify if it is OK
for others to open the file, but you want to restrict who can make changes to the file
5. Click on OK to close the General Options box 6. Select a name for your file and click Save
Exercise 6
1. Create a new workbook, enter the given data and format it as it looks like.
ANNUAL SUMMARY 2010
No Item Quantity Unit Price Total Price
1 Computer 5 11000
2 Printer 3 6000
3 Scanner 2 5000
4 Plotter 1 15000
5 Power Supply 7 2000
6 Television Set 2 5000
Overall Total
2.Format Unit Price with Birr currency style, comma style and two decimal places
3. Rename sheet 1 by the name September 2010.
4. Save the workbook by the name Annual Sales in your folder.
5. Use appropriate formulas to fill total price column and overall total price.
6. Change the text direction of the second row and observe the effect.
8. Rename sheet 3 by the name copy.
9. Draw a column chart with the following free set conditions.
Title: - Annual Sales
Axis: - Item (x-axis), Unit price (y-axis).
Legend:-Left Data level:-Value
10. Practice on drawing different charts using the above data.
7.0 BUILDING AN EXCEL DATABASE
7.1 Basic Database Concepts
In your earlier work with Excel, you created worksheets to store and summarize information or data. Often you organize this information so that you can easily find the entries for a series of values, or calculate the totals for a group of numbers.
In Excel, a database is simply a more organized set of data. By organizing the data into a database, you can use the built-in database commands to find, edit, and delete selected data without manually scrolling through the information.
Database: A tool you use to store, organize, and retrieve information.
Excel treats the database as a simple list of data. You enter the database information just as you would enter data into a worksheet. When you select a command from the Data menu, Excel recognizes the list as a
Excel treats the database as a simple list of data. You enter the database information just as you would enter data into a worksheet. When you select a command from the Data menu, Excel recognizes the list as a