160 likes | 173 Views
European Computer Driving Licence. Module 4 – Spreadsheets Chapter 4.4 - Functions. A function is a formula used in a calculation Excel has over 200 functions to help with many applications For ECDL4 you will learn about: =SUM =AVERAGE =MIN =MAX =COUNT. Functions.
E N D
European Computer Driving Licence Module 4 – Spreadsheets Chapter 4.4 - Functions
A function is a formula used in a calculation Excel has over 200 functions to help with many applications For ECDL4 you will learn about: =SUM =AVERAGE =MIN =MAX =COUNT Functions
Although you can use the AutoSum button to add up a column or row of numbers, you can also type the function Click where you want the total to appear Type =SUM( Click and drag the cells you want to add up Press the Enter key The SUM function
Use this to work out the average of a range of cells Click where you want the result of the function to appear (eg cell B13) Type =AVERAGE( Select the cells you want to find the average of Press the Enter key Watch out: any blank cell formatted as Number will upset your average! The AVERAGE Function
To display the highest number in a range of cells: Click where you want the result of the function to appear (eg cell B15) Type =MAX( Select the range of cells Press the Enter key To show the lowest number, replace =MAX( with =MIN( The MAX and MIN functions
To count the number of entries in a range of cells: Click where you want the result of the function to appear (eg cell B19) Type =COUNT( Select the range of cells Press the Enter key Note the COUNT function only counts cells that contain a number – it will ignore blank cells or cells which contain text! The COUNT Function
To add a new record, you will need to insert a new row Right click the row header where you want the new row to appear Select Insert from the shortcut menu A blank row will appear. Type in your new data! Adding a new record
Select the sheet which contains the data you want to copy (eg Birth Stats) and select the cells (eg A1 to D1) Click the Copy icon Click the sheet you want to copy the cells to (eg Daily Weights) Click in the appropriate cell and click the paste icon You may need to widen the columns to fit the data! Copying data between sheets
Type a numeric value in the first cell (eg Day 1) Move the mouse pointer to the bottom right corner until the pointer changes to a black plus-sign (the fill handle) Click and drag across (a tool tip will appear as you drag!) Release the mouse button Excel will automatically increment the value! Handy Hint: This also works with Days, Months and plain numbers! Filling a series
To “hide” a row, click any-where in the row (eg row 12) From the Format menu, select Row then Hide The row will disappear from view – the row number will not display To display the “hidden” row select the row headers above and below (eg 11 and 13) From the Format menu select Row then Unhide Hiding and Unhiding Rows
When working on a big spreadsheet, it is useful to have either row labels or column headings showing no matter how far you scroll in the spreadsheet Click in the cell nearest to A1 that you don’t want to freeze (eg cell B4) Select Window, FreezePanes from the menu – black borders will appear next to the frozen panes Freezing row and column titles
To “unfreeze” panes, select Window then Unfreeze Panes from the menu The black borders will disappear It doesn’t matter where the active cell is when doing this! Unfreezing Panes
If you have several workbooks open, you will see a taskbar button for each one To switch between open workbooks, click the taskbar button You can also select Window from the menu bar and select the workbook you want to work on! This is handy if you have more than 3 workbooks open and Windows XP “groups” the taskbar buttons! Opening several workbooks
To save an existing workbook with a different file name, select File then Save As from the menu Type in the new file name in the File Name box then click Save You can also use File, Save As to save to a different location Saving under another file name
You can choose to save a workbook in a different file format Click File then Save As and click in the File type box Select the file type from the drop down list Scroll down to see more options! Saving as a different file type
To post a spreadsheet file on the web, save as a Single File Web Page (.htm or .html) To save as a text file that can be read by other operating systems, choose Text File To save as a template, select Template To save in a format suitable for older spreadsheet or database programs, select csv format You can also save as a Lotus 1-2-3 spreadsheet (.wk4) or dBase (.dbf) Different file formats For more information on file formats, type File Formats into Excel’s Help section!