Functions in EXCEL for ST2004

In the following it is presumed that the user has selected, from the View tab, to display the ‘Formula Bar’. A typical display is to the right. When entering a function into a cell, the use typically types into the formula bar.

Absolute and Relative addressing.

The basic EXCEL syntax is that a cell contains either data or a function. If the latter the syntax begins with “=”. Eg, the cell C6 might contain the text “=Function( one or more arguments)”; the simplest is “=B6”. You can enter this conveniently by typing = and clicking on cell B6. If the cell containing the function (C6) is copied to another cell (eg D8) then the argument list automatically updates. D8 is ‘1 col over, 2 rows down’ from C6; B6 is 1 cell to the left of C6. This will be reflected in the argument in D8; it will be C8. The address is relative. Often one wants the function in a set of cells to refer to a common argument; this is achieved by writing, in C6, “=$B$6”. Copying this to D8 will result in its content being “=$B$6”. Other variants are “=B$6” and “=$B6”. The function key F4 toggles through options. Arguments can be a single cell – as above; an array such as “=Average(B6:B10)”; or multiple arguments such as “=Max(B6,Min(C6,D6))”.

The function “=RAND()” takes no arguments, yet “=RAND” will fail. RAND() is a ‘volatile function’ .

Often we need ‘patterned data’, such as 1,2,3 ... is a set of cells. Many options are available. The simplest is the use of “Series” under “Fill” – in the Editing Area on the Home page. I usually use columns to store these.

Available functions

Right clicking on the fx gives access to EXCEL’s functions, and their syntax. The ‘Category’ dropdown menu is a useful starting point for finding what you need. For example the ‘Text’ category contains the function “=CHAR()”; the ‘Lookup and Reference’ category has “=LOOKUP()” and =”MATCH()”. Associated with all functions is a section with “Help on this function”.

Two really important functions that we have used are =COUNTIF and =SUMIF. These take the form of “=COUNTIF(“array of cells”, COND)”. There are more general versions =COUNTSIF and =SUMSIF. Frequently you will need to use absolute addressing for the array. One easy way to do that - and it’s also more likely to give you code that is readable - is to use the Formula tab to give the array a NAME of your choosing.

The condition COND at its simplest is equality to something stored in another cell, eg A3. Then the syntax is simply “=countif(Name, A3)”; it can be useful to use versions such as “=countif(Name, A$3)”. The most general syntax is if you require a “£” condition concerning the contents of A3 Then the cond can be written as “ <= “ &B6, using the ampersand concatenation character in EXCEL

Other simpler functions used recently include “=NORMINV(RAND(),....)”, “-LN(RAND())”, “=MAX(...)” , “=MIN(...)”, “=RANK(....)”. Functions can be nested; eg “-LN(RAND())”.

Replications

Repeating a calculation with a different set of random numbers is crucial to the course. In EXCEL this is done most easily by using the DataTable available under “What-if..” in the DataTools section on the Data tab. This is a very useful tool for checking how the results of a (possibly complex ) calculation would change if one of ‘input’ values changes to each of a list that you specify. RAND() does not have any inputs, so forcing a recalculation just leads to a new version of the previous, differing only in that the RAND() values are changed.

1  Use the EXCEL cheat to replicate any set of calculations involving random numbers 10 times with different sets of random numbers. Suppose for example that you have used EXCEL to generate a password of length 6 stored as 6 cells with one character each. To do this:

  1. In a new part of the worksheet
  2. Enter into 6 cells exactly the same formulae as is in the cells holding the ‘last stage’ of your procedure in 1(a) above. These will comprise the top row of an array
  3. Enter values 1 to 10 in a column which form the left column of the array
  4. Select the entire array, including all of the top row and left column
  5. Press the What-if button (on the Data Tab). Select Data Table... and enter into the Column Input Cell the address (eg J1) of an arbitrary empty cell. Leave the Row Input Cell blank. Press OK.
  1. The body of your table, previously empty, should populate with passwords created exactly as you have specified in the top row, differing only in that different random numbers have been used. These are a set of realisations of ‘Random Password’