Wiki-Notes‎ > ‎(1) Website‎ > ‎

Spreadsheet functions (Monica Slevin)

Quick note: If you see a function that follows the format:

FOO(A1:A30)
that means that the formula is taking the sum of not just cells A1 and A30, but actually that entire range of cells -- from A1 through A30, inclusive.

Here is a quick reference to some commonly-used spreadsheet functions. I have chosen not to include a range of cells in the formulas, but you can insert your own range in the empty sets of parentheses.

function  what it does
 AVERAGE()  returns the mean of the selected cells
 SUM()  returns the sum of the selected cells
 MAX()  returns the maximum number out of all the selected cells
 MIN()  returns the minimum number out of all the selected cells
 STDEV()  returns the standard deviation of the selected cells
 LEN() returns the length of a word. For example, LEN("TEACH") would return 5.

There are different ways to express mathematical operations using this "function" language.

^            Exponential
-            Negation
* \          Multiplication/Division
+ -         Addition/Subtraction
=           Equality
<>         Inequality
<           Less Than
>           Greater Than
<=         Less Than or Equal To
>=         Greater Than or Equal To

Source: MedCalc

Here is an example of a function that converts a given number of inches to centimeters:
function in2mm(inNum) {
 
// Function to convert from INCHES to MILLIMETERS
 
var outNum = 0;     // this will hold the answer
 
var factor = 25.4;  // multiply input by this factor to get output
 
if (typeof inNum != "number") {  // check to make sure input is a number
   
throw "input must be a number";  // throw an exception with the error message
 
}
  outNum
= inNum * factor;  // calculate the answer
 
return outNum;  // return the answer to the cell which has the formula
}
Comments