Excel - University College London
Excel physics – trainers notes
▪ Download files
▪ Introductions
▪ Ask how much experience
▪ Tell them to work with neighbour if they are having difficulties
Outline – 3 major areas, all covered in workbooks
▪ Formulae and functions
▪ Lists – sorting and filtering data
▪ Subtotals
▪ Logical IF
▪ Conditional formatting
▪ Charts
Recap
▪ Cell addresses
▪ Name box
▪ Labels, values, formulae
▪ Sheets
Formulae and functions (Part 1 pages 23-35)
Start with =
+ - * / ^ %
Remember to use brackets properly
ACTIVITY
1. Open dept-student-numbers.xls.
In the ByDept sheet enter a formula in cell B9 to calculate the total number of students. (677)
In the ByYear sheet enter a formula in B6 to calculate the number of students in 1990. (230)
Copying formulae
Explain about copying formulae
ACTIVITY
▪ Copy this formula into the adjacent cells (C6 through to O6)
Absolute referencing (Part 2 pages 13-18)
ACTIVITY to lead onto absolute referencing
2. Open bonus.xls
Enter a formula to calculate Cruise’s total salary (Basic & Bonus). Copy this into the cells below.
If this doesn’t work well, use absolute referencing for the reference to cell B3.
Other functions
Use fx button
Mention average, min, max, count, trigonometrical (tan, cos, sin) – up to them to explore
ACTIVITY
3. Use the count (COUNT, COUNTA, COUNTBLANK, COUNTIF) functions to count the number of cells in the block A3:C12 whic h contain numbers; contain anything; are blank; and where the salary is greater than £200. =COUNTIF(A3:C12,">200")
4. Return to the ByYear sheet in the dept-student-numbers.xls file
Calculate the average, minimum, maximum and median student numbers for yoga and sanscrit
Viewing formulae
5. Use Tools>Options>View to view the formulae, then revert to the normal view
6. If you have time... Task 6 (Part 2, p 16) (florist)
Names (only if there is time)
7. Task 7 (Part 2, p 18)
Paste Special
Where you only want to paste formats, or values, not formulae. Also allows you to transpose (swap rows & columns).
8. Copy the data in the ByYear sheet and transpose it, starting at cell A11
Lists (sorting and filtering) (Part 2 pages 1-9)
Rules:
▪ One only per sheet
▪ Blank row above and below, blank column to right
▪ Labels (headings) in first row with data immediately below
▪ No empty columns or rows (but empty cells are OK)
Sorting & Filtering
▪ Button, or Data>Sort
▪ Autofilter – point out custom options
▪ Removing filters
9. Open the employees.xls file and sort by country then city
Apply a filter to extract sales representatives
Add to this filter to extract sales representatives who were born before 01/01/1960
Remove all filters
Subtotals
Where you may want to collect data from a list into groups and produce summary statistics (not just totals in fact...)
10. Open the club.xls file and sort by Place, then calculate subtotals based on the Paid to Date and Total Due fields.
Use the – signs to collapse the display so that only rows containing subtotals are shown.
Remove all subtotals
Logical If
Simple example would be to test if a cell value was less than 50 (i.e. looking for failed exams)
ACTIVITY
11. Open the student-marks.xls file.
Use IF(logical-test, do this if true, do this if false) to enter “pass” in column E for marks of 50 or greater, and “fail” for marks below 50.
Conditional formatting
12. Use conditional formatting to display marks (i.e. cells D5:D14) above 70 in red.
Charts
13. Choose an appropriate chart type for the following data:
▪ Student numbers by department (dept-student-numbers.xls ByDept sheet)
Should be a bar or column chart. A pie chart is acceptable.
▪ Monthly sales for 2002 and 2003 (monthly-sales-2003-02.xls)
A line chart
▪ Infant height dependence on age (baby-heights.xls)
x-y scatter plot - age values are not evenly spaced and so a line chart would be misleading
14. Add y error bars to the baby-heights chart (see Task 16, p.51 for step-by-step guidance)
15. Add a trend line to the baby-heights chart (Chart>Add Trendline)
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- objective create a microsoft excel based program that
- accessible spreadsheets with microsoft excel 2010
- creation of module spreadsheet
- this is a text file named readme
- spreadsheet budgeting steps in banner msu
- module one get started with excel
- excel basic operations
- excel university college london
- single ccas spreadsheet under secretary of defense for
Related searches
- excel university coupon code
- city university of london ranking
- american military university college code
- florida state university college scholarships
- university in london uk
- cornell university college of business
- cornell university college report
- university college london finance msc
- arizona state university college code
- national university college code
- indiana university college ranking
- walden university college of nursing