Microsoft Excel Spreadsheet Basics and Tips

Array Formulae e.g. =A1:A4 * B1:B4 allow actions to be applied on multiple cells

COUNT, and SUM are useful for counting/adding cells

COUNTIF, and SUMIF are useful for counting/adding cells which fulfill a certain criterion

e.g. =COUNTIF( A1:A100 , 10 ) returns number of cells with value of 10
=COUNTIF( A1:A100 , <0 ) returns number of cells with negative value
=COUNTIF( A1:A100 , * ) returns number of cells with text
=COUNTIF( A1:A100 , "hello" ) returns number of cells with text "hello"
=COUNTIF( A1:A100 , "????" ) returns number of cells with four letter word

More Examples:

=SUM(IF(ISNUMBER( A1:A100), 1 0)) returns number of numerical values in the range
=SUM(IF(ISERR( A1:A100), 1 0)) returns number of errors cells in the range
=SUM(IF(FREQUENCY( A1:A100 , A1:A100 ) >0 , 1 0)) returns number of unique numeric values in the range

F4 to cycle through the above options - useful when building tables or arrays

One of my favourite uses for Excel is to simulate the financial markets. I make investments based on which way I think a particular asset price will move, but also simulate possible future scenarios, to see what I might make or lose if unexpected outcomes occur. There is a RAND() function that generates an evenly distributed random number between 0 and 1 and also NORMDIST, NORMINV and NORMSDIST functions that returns normal (gaussian) distributions.

So how do you simulate a "Normal" or "Gaussian" random series (e.g. to simulate a "Random Walk" as in the book excellent economics book "A Random Walk Down Wall Street" by Burton Malkiel)

=NORMINV(RAND(),MEAN,STDEV)

Where MEAN, STDEV are cells or variables defined in the spreedsheet.

I have also seen this approximation used:

=SQRT(-2*LN(1-RAND()))*COS(RAND()*2*PI()))*STDEV*MEAN