Main topics | | specific areas | | A brief description of what we'll be covering |
| | | | |
1. Boolean | | = logic | | We'll show you basics of Boolean logic and how that fits into Excel. |
| | = results | | Understanding how Boolean works, allows us to manipulate many of the |
| | = co-oercion | | functions we use to our advantage and allows us to employ some very |
| | | | useful tricks via co-oercion of Boolean results. Understanding the |
| | | | Boolean handling of results as you build up your formulae also helps |
| | | | you understand how and why a lot of the more advanced functions |
| | | | (which we'll be covering here) actually work. |
| | | | An example would be that if A1 contained "7", then B1 should contain |
| | | | "7", otherwise B1 should contain "0". Mosts users would employ the |
| | | | =IF(A1=7,7,0) in B1, however we can use the shorter Boolean approach : |
| | | | =(A1=7)*7 |
| | | | |
2. Array formulae | | | | It's estimated that 95% of most Excel users are unaware that array formulae |
| | | | exist. We'll teach you how to use them and how to recognise where they |
| | | | can be used more efficiently than a whole range of standard formulae. |
| | | | Array formulae allow us to apply a single formula to a range of cells |
| | | | where, normally, we'd be entering multiple formulae. |
| | | | An example would be that if A1:A10 contained "1" to "10" and we wanted |
| | | | the smallest number that was larger than 7, we could use the following |
| | | | single-celled array formula, rather than using separate =IF statements followed |
| | | | by a summarising =MIN function : |
| | | | {=MIN(IF(A1:A10>7,A1:A10,""))} |
| | | | |
3. Functions | | = =TEXT | | How to incorporate linked numbers and dates into your textual sentences so |
| | | | that they appear correctly formatted. This function is particularly useful |
| | | | when we're writing summaries that include linked results : here, the month and |
| | | | total figures are links from existing info on the sales sheet : |
| | | | "Sales for the month of May 2005 were £ 25,799.45" via the following formula : |
| | | | ="Sales for the month of "&TEXT('Sales'!B50,"MMM YYYY")& |
| | | | " were £ "&TEXT('Sales'!A1,"#,###.00") |
| | = =CODE | | How to return the ASCii code of a specified character. This is useful if |
| | | | you're continually having to edit text that contains odd ascii characters. |
| | | | For example, your column of names comes imported with copyright characters |
| | | | peppered within it : =CODE(LEFT(A1,1)) will allow you to quickly find what |
| | | | ascii code the copyright sign is. |
| | = =CHAR | | Similarly, =CHAR(ascii code number) will return the character. With the above |
| | | | example returning 169, we can then replace them all as follows : |
| | | | =SUBSTITUTE(A1,CHAR(169),"") |
| | = =ROW | | How to return the row number and/or the column number of a cell and then how |
| | = =COLUMN | | to use this to excellent effect in our array formulae and how to save |
| | | | time by using them in your standard formulae (ie =VLOOKUP) as you |
| | | | copy across or down |
| | = =LEN | | How to find the length of the entry in a cell |
| | = =SEARCH | | How to ascertain the position of specified text within a section of larger text. |
| | | | Once we combine =LEN and =SEARCH, we can begin to automate links, full |
| | | | filenames and filepaths very effectively. |
| | = =ISNUMBER | | How to test if your cell contains a numeric entry, and thusly, how to prevent |
| | | | any #VALUE! errors. Used with =ISNUMBER and =SEARCH, we can search |
| | | | for parts of text and particular cell contents, following up with actions. |
| | = =ISERROR | | How to intercept errors and perform alternative actions when they occur. |
| | = =ISNA | | How to intercept that dreaded #N/A error |
| | = =INDIRECT | | We'll teach you how to use =INDIRECT so that you can set up dynamic |
| | | | links : for example a link that looks at the "Sales" sheet if A1 contains |
| | | | "Sales" and looks at the "Profits" sheet if A1 changes to "Profits" etc etc. |
| | | | We'll also show how to set up multi-dependant data-validation dropdowns : for |
| | | | example, if your first dropdown choice is "England", the 2nd dropdown will |
| | | | only display English cities; and if "London" is then selected, the 3rd dropdown |
| | | | will only display London postcodes; similarly : Wales / Wrexham / Wrexham |
| | | | postcodes etc etc |
| | = =MATCH | | How to find the position of an item within a list. |
| | = =OFFSET | | Again, a different method of making your links dynamic, dependant upon |
| | | | values elsewhere in the sheet. Most commonly applied to simulate a VLOOKUP |
| | | | that looks left rather than right along a table of values once a match is found. |
| | = =SUMPRODUCT | | An extension of =SUMIF and =COUNTIF where we are not restricted to just |
| | | | a single criteria, but as many as you need. For example: count the amount of |
| | | | entries in your database where the salesperson is "John" and his sales are in |
| | | | the "North" and the product is "Widgets" and the sale value is over £ 5,000 |
| | = =SQL.REQUEST | | For those of you who know your SQL, you'll love this function. Here, we'll show |
| | | | you how to bring back sets of information from your workbook or from a |
| | | | different workbook, open or closed. As the results are an array, we're not |
| | | | limited to single-celled or single-record results, but whole results, as you'd |
| | | | expect with SQL. So, for example, if you want a table of results (Teams, |
| | | | Stadiums, Referees, scores, crowd size and scorers) where Arsenal played |
| | | | Chelsea from a database of football matches, =SQL.REQUEST will achieve |
| | | | this easily |
| | | | |
4. Named ranges | | = dynamicism | | Named ranges are a valuable tool. However, when we make them dynamic, they |
| | = dynamic print area | | come into their own and save us from ever having to worry about whether their |
| | = dynamic graphs | | sizes have altered. Here, we'll show you how to make them dynamic, expanding |
| | = dynamic formulae | | and shrinking as your data gets added to or reduced and how this can be applied |
| | | | to our print ranges, our graphs' data ranges and our everyday formulae so that |
| | | | we save hours not having to update our ranges |
| | | | |
5. Named formulae | | | | Create your own functions. For example, if you're a VAT accountant and you're |
| | | | continually having to convert gross amounts to net amounts, or net amounts to |
| | | | VAT amounts, we'll show you how to set up your own function, say, =GROSSTONET |
| | | | that converts your VAT-inclusive GROSS figures to VAT-exclusive NET figures, and |
| | | | say, =NETTOVAT that converts your VAT-exclusive NET figures |
| | | | to VAT amounts; all without the need for macros, programming or addins. |
| | | | |
6. D-functions | | = =DSUM | | An alternative to array formulae and =SUMPRODUCT if you find these |
| | = =DCOUNT | | powerful functions slowing down your worksheets as they compute their |
| | | | answers. We'll look at 2 of the Dfunctions which illustrate how to get your |
| | | | results where you criteria are shown more user-friendlier onscreen rather than |
| | | | buried in a formula which other users may not be able to follow easily. |
| | | | |
7. Automatic sorting | | | | We'll show you how to automate your sorting of info, without having to use the |
| | | | sort button manually, by use of the =RANK function and =COUNTIF or =ROW |
| | | | functions to cope with "ties". If you regularly have to design leagues or |
| | | | present tables that are sorted by criteria, then this should help you a lot |
| | | | |
8. Random numbers | | = =RAND | | How to generate random sets of numbers for use in models, games etc. If you need |
| | = =RANDBETWEEN | | to see how your profits vary given random sales each month between 5,000 and |
| | | | 6,000 units; these random functions will help you achieve this |
| | | | |
9. Volatile functions | | | | Here, we'll discuss which of Excel's functions are volatile : ie, which |
| | | | re-calculate whenever you enter data. Knowing which functions are volatile and |
| | | | slow down your workbook, allows us to plan which functions and methods to |
| | | | use, or ignore, so we end up with a quicker workbook |