Excel



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.

Google Online Preview   Download