Complex Spreadsheets

1. Complex Spreadsheets George HoltGeorge.Holt@tafensw.edu.au

2. Introduction • Good Design • Evolving from Users to Designers • Why analysis is essential? • Research skills to survive • Advanced Functions • Named Cells and Ranges • Lookup Functions • Linking • Text functions • Date Functions • Data and Filtering • Testing

3. Good Spreadsheet Design?

4. Problem Solving pitfalls Solving complex problems requires a plan. Issues that arise in complex problem solving include. • You should analyse the problem first on paper with a Inputs- Rules & processes – Outputs. • A mudmap can be used to plan a layout. • You must have a clear step by step plan. • You often have to break a big problem up into smaller chunks. • You must avoid mistakes and typo’s. Learn to proof read effectively. • If you get stressed it will adversely affect your logic. • A test and Revise method is usually more reliable than a one step brainstorming method.

5. Evolving from Users to Designers? • This unit of study is the first step in moving from a user or spreadsheets to a designer of spreadsheets • Designers apply more advanced analysis skills to create spreadsheets for other staff to use. Designer User

6. Users Designers What are the differences between Users and Designers?

7. Users Designers The answer is Analysis!!

8. Why is analysis essential? • It gives us a structured way of solving problems • It provides a means of breaking big problems into much smaller sections • It helps us to communicate to others in our team and get their feedback on how we can improve our design • If we can’t finish the design process another staff member can follow our plan and finish the job • It helps us to get the job right the first time

9. Research skills to survive Effective designers have good research skills and tend to solve their own problems: • Use Help effectively • Monitor websites and online newsletters • Analyse other designers spreadsheets • Use manuals and texts • Talk to other designers and share ideas

10. Advanced Functions • Named Cells and Ranges • Lookup Functions • Linking • Text functions • Date Functions • Data and Filtering

11. Named Cells and Ranges • Use plain English to create formulas. Eg • =IF(Gross>450,Gross*Super_Rate,"No Super") • =Hours*Pay_Rate • Individual cells or ranges of cells can be named

12. Lookup Functions Use Lookups to find the value for a particular criteria. This works especially well with named ranges • =Wage*VLOOKUP(Wage,Tax_Scale,2)

13. Linking • Linking provides a way to create relationships between spreadsheets. • Consider creating a spreadsheet to store costings and then have all of your other spreadsheets refer back to the costings sheet to get the most current data. Costings.xls Consumables Budget.xls Section Budget.xls

14. Text functions Text functions can be used to convert or display text in a variety of ways • =RIGHT(C7,7) displays 7 characters from the right. • =REPLACE(D6,5,1,"Dot") Replaces the decimal place with the word Dot.

15. Date Functions • Date Functions - format dates into different forms for calculations • Custom Formats – Change the appearance of dates • Calculations – Due date = Order date + 30

16. Data and Filtering • The Filter tool under the Data menu is fantastic for analysing large amounts of data. • Consider using filters in conjunction with macros and Charts to automate and provide a very clear visual image

17. Testing • It doesn’t work until you prove it works!!! • Use simple test data where you already know the answers to TEST the system • You NEVER allow someone to use an untested product!!!

18. Conclusion • Evolving from Users to Designers • Why analysis is essential? • Research skills to survive • Advanced Functions • Named Cells and Ranges • Lookup Functions • Linking • Text functions • Date Functions • Data and Filtering • Testing

19. D33+e32 =A3xb3 =a3*B3 =b9\c10 =b9/c10 +ac32-da45 =ac32-da45 Spot the Dud - Formulas

20. BODMAS? • Who’s body do you want buried where?Definitions of BODMAS on the Web: • A reminder of the order in which operations have to carried out when working with equations and formulas. Brackets Order Division Multiplication Addition Subtraction Sometimes also called BIDMAS, where I stands for Indices.www.lgfl.net/lgfl/leas/enfield/schools/southgate/accounts/staff/dwhitfield/web/pages/dictionary.html

21. =sum(B7..B37) =sum(b7:B37) =sum(b7;b37) =average(b3,b5,b7:b37) =avg(c3:c37) =average(c3:d33) =avgb3:b33) =average(c3:33d) =maximum(B7..B37) =min(b7:B37) =minimum(b7;b37) =max(b3,b5,b7:b37) =maximum(c3:c37) =avg(c3:d33) =average b3:b33 Spot the Dud - Functions

22. Merge and Centre Headings Consistent Decimals Use colour but be careful when printing to Black and White printers Use custom formats where required Divide your worksheet up with borders Be careful that your worksheet ends up fitting a page if required. Formatting

23. Layouts that work • Space your work out • Consider creating different sections for inputs, calculations and results • Use charts to illustrate complex data • Name worksheets descriptively • Create a Readme worksheet with notes on how the spreadsheet is to be used • Include comments in cells to explain the data or calculations

24. Pick the Duds – Maths and Formatting • Look at the ..\..\George\Adv Spreadsheets\Pick the dud weather.xls • Be careful with Auto features like Autosum • Use consistent formatting with decimals • Make sure you include the complete range of cells • Use Headings to describe the data • Label all data and include units of measurement • Format dates carefully • Space your page out and differentiate between inputs, calculations and outputs(results)