You will have already come up with some examples of how spreadsheets can be useful in general but now you need to bring out the heavier guns with illustrations of how formulae work (and we're not talking about the simple SUM + - * and / here!)
The sort of formulae or features you illustrate depends on the type of anaylsis you are doing. The list below are some that are really useful and not too complicated ones:
- VAT calculations (including the more difficult one of working out how much VAT is included in a price)
- using data stored on one sheet (or entered by a user) in calculations on a second sheet which then display the result on either a third sheet or next to where they entered it. (That always goes down well!)
- MIN or MAX (shows which value is the lowest or highest in a selection)
- Validation techniques (and nice or nasty messages that pop up when they enter something they shouldn't)
- Things that change colour depending on their value when data is entered that affects them
- IF
- nested IF statements (the formuale look awful but are so useful)
- VLOOKUP (or HLOOKUP) get a value from a table and stick it somewhere else or use it in a calculation (eg for an insurance quote look up the car's insurance group in a table and then apply a particular premium based on another table of ages etc.)
- MROUND often overlooked but this will round horrible looking figures to easy to understand ones - eg 34666 could be displayed as 35000 to the nearer 1000 or 98 as 100 to the nearer 100. Often end users only want an approximate guide and, after lots of assumptions and guesswork about predictions, a figure to seventeen decimal places is pretty irrelevant.
- SUMIF adds up just those bits you need
- COUNT counts cells with things in them
- COUNTIF counts cells with just certain things in
- LOWER changes text to lower case letters
- UPPER changes text to UPPER CASE LETTERS
- PROPER Changes Text To This Sort Of Display (really really useful for names and addresses where some idiot has just stored the data in capitals.
- RANK puts things in order
- Conditional formatting changes the colour of text or cells depending on their content
- NOW() gives you today's date
- Filters and sorting tools get rid of things you don't want and put the rest in order
- Hidden rows and columns can do lots of intermediate calculations then go away and leave just the answer without confusing everyone
- Hiding row and column headers, tabs and even more can make your spreadsheet look nothing like a spreadsheet
- Protection stops people messing up those long formulae you spent hours getting right.
- Good alignment, use of correct decimal places, decent fonts and shading rather than lots of grid lines can also immensely impove the appearance of items for publication or display
- Publishing on the web or as a Live or Google document can be amazingly valuable when collaborating with others on collecting data
- Using the spreadsheet as a form that people fill in on-line
The list goes on... but that should do for now. You need to make some examples of some of these at work. I'll include a link to some data when I've found some for you to work with.
