More Functions

There’s no way I can fit descriptions of every Numbers function into this book. But I’ve cherry-picked a few, in three categories (Numeric Functions just ahead, and then Statistical Functions and Reference Functions). I’ve included those that I think most people will find handy in many circumstances. I don’t go over them thoroughly, but you’ll be able to find your way starting with the information here.

I list each function with its syntax and an example of what it would look like with its arguments. Similar functions, such as ROUNDUP and ROUNDDOWN are grouped, with only one description and example if they use identical syntax.

Numeric Functions

Here’s a baker’s dozen out of Numbers’ forty-something numeric functions that you might need in your basic bag of tricks.

Rounding Functions

When you specify the number of decimal places to be displayed in a cell, using the Format Inspector’s Cell pane, the displayed number is rounded to meet your demands. But it’s always rounded to the nearest number—so a one-decimal-place format shows 4.75 as 4.8 and 4.74 as 4.7. If you need to round to the nearest hundred (as in three-digit whole numbers, not hundredth), or the nearest even or odd number, or perhaps always up to the next whole number instead of to the nearest one (which might mean going down), Numbers’ rounding functions provide succor.

Keep these facts in mind when using rounding functions:

• Zero is considered an even number when Numbers rounds to the nearest even number.
• Some rounding functions intrinsically use up or down rounding; if you get unexpected results from a rounding function, it may be because you didn’t realize which way the rounding would go.
• Rounding “up” doesn’t always mean going to a higher number; it means going away from zero. Round up a positive number, and, yes, the rounded number goes higher. But round “up” a negative number, and the result is further away from zero, so it’s lower. So, round 155 up to the nearest hundred you get 200; but round -155 “up”—that is, away from zero—and you get -200. Essentially, when working with a negative number, a rounding function takes the absolute value of the number (ignoring the negative), rounds it, and then makes it negative again.

  Rounding down is an analogous procedure: it means going toward zero. So, rounding down 155 gives you 100, while rounding down -155 results in -100.

• To refer to the digits to the left of the decimal point (the integer), use a negative number. Here’s what happens when you round 563.487 to these specified number of places:

  1 place = 563.5 2 places = 563.49 -1 place = 560 -2 places = 600

• Cell formatting (see Standard Data Formats) can “interfere” with the display of rounded numbers. The rounded number may be 563.49, but if the cell is formatted to display a single decimal place, you’ll see 563.5. The two-decimal number, however, is still stored in the cell.

You may find it hard to believe, but there are ten rounding functions described here. I start with the plain-vanilla ROUND, move on to ROUNDUP and ROUNDDOWN, and then look at more specialized options:

• ROUND
  ROUND(value, digits)
  ROUND(3.1415,3)

  This function rounds to the nearest number (a 5 rounds upward), using the specified number of digits. ROUND(3.1415,3) returns 3.142.

• ROUNDUP · ROUNDDOWN
  ROUNDUP(value, digits)
  ROUNDUP(3.145,3)

  These functions round up or down as specified, unlike ROUND, which goes to the nearest number. ROUNDUP(3.1411,3) returns 3.142; ROUNDDOWN(3.1417,3) returns 3.141.

• INT · EVEN · ODD
  INT(value)
  INT(4.2) · EVEN(4.2) · ODD(4.2)

  INT rounds to the nearest integer. EVEN and ODD round up to the nearest even or odd integer; keep in mind that for negative numbers, “up” means going toward zero.

  • INT(17.52) returns 17; INT(-17.52) returns -18
  • EVEN(17.52) returns 18; EVEN(-17.52) returns -18
  • ODD(17.52) returns 19; ODD(-17.52) returns -19
• MROUND · FLOOR · CEILING
  MROUND(value, factor)
  MROUND(17, 3) · FLOOR(17, 3) · CEILING(17, 3)

  MROUND rounds a number to the nearest multiple of the factor specified; if the number is halfway between multiples, it’s rounded away from zero. MROUND(17,3) returns 18, the multiple of 3 nearest to 17.

  FLOOR and CEILING work similarly, with FLOOR always rounding down to the multiple (returning a 15 for the example above), and CEILING—you guessed it—rounding up (to 18 for the example).

• TRUNC
  TRUNC(value, digits)
  TRUNC(3.141592, 4)

  TRUNC truncates a number to the specified number of digits, just chopping off the excess with no rounding at all. TRUNC(3.141592,4) returns 3.1415.

Other Numeric Functions

Generating random numbers is great for testing purposes, and dealing with negative numbers might help if you’re always running in the red:

• RAND · RANDBETWEEN
  RAND() · RANDBETWEEN(low value, high value)
  RAND() · RANDBETWEEN(1,10)

  RAND returns a random number from zero to one (including zero, but not one), out to 15 decimal places; the empty parentheses must be included with the function. It is normally used with some multiplier. RANDBETWEEN returns an integer “between” the low and high values given as arguments, including those values; RANDBETWEEN(1,3) can return a 1, 2, or 3.

Forcing Formula Recalculations

Numbers doesn’t have a Recalculate command because, like all spreadsheets, it recalculates formulas when necessary, so you don’t have to worry about formula results being outdated if you change some cells, or other formulas. And, since you can’t turn off Numbers’ automatic recalculation, you don’t need a command to trigger one.

But because I find it useful to set up certain tests using randomly generated numbers, and then change the numbers to make sure I still get the expected results in cells and charts, I was quite disappointed at the lack of a recalculation command to force new random numbers to show up.

Here’s the easiest way I’ve found to make Numbers recalculate random-number formulas:

1. Click in any cell adjacent to a cell with a RANDBETWEEN or RAND formula. In this case, “adjacent” includes the cells just touching the outside corners of a formula cell, making a complete frame around the random-number cells (Figure 114).

   

   Figure 114: Any blue-shaded cell around these random-number generating formulas can be used to trigger a recalculation.

2. Press Command-X to trigger the recalculation.

You don’t have to actually cut anything from the cell, so you can use an empty cell. Or, you can temporarily cut something (a label, say) and paste it right back, or undo the cut. Oddly enough, the paste won’t trigger a second recalculation, but the undo will.

• ABS
  ABS(value)
  ABS(-14)

  Returns the absolute (positive) value of a number or expression; ABS(9-12) and ABS(12-9) both return 3.

• SIGN
  SIGN(value)

  Identifies a value as being positive or negative. It returns a 1 when the argument (presumably a cell reference) is positive and a -1 when it is negative. A zero is returned if the argument is zero.

Statistical Functions

A quick look at the Statistical category in the Function Browser can be overwhelming if you’re not a statistician. But you might find several functions of use whether you’re handling student grades, regional sales, or your fantasy football season:

• MEDIAN
  MEDIAN(values)
  MEDIAN(K2:K120) or MEDIAN(1,5,9, B2)

  Median returns the middle number in a group of numbers as if they were ordered from largest to smallest (regardless of their actual order). Note that this is not the same as an average; the median in the list 3, 6, 12 is 6, while its average is 7. If there’s an even number of numbers (and therefore no “middle” number), MEDIAN returns the average of the two numbers in the middle.

• MODE
  MODE(values)
  MODE(B3:B35)

  Use MODE to identify the number that occurs most frequently in a list. If there’s a tie, the first one is returned; if there are no repeats, you’ll get an error.

• RANK
  RANK(value, range, high or low)
  RANK(M5,M3:M20,1)

  RANK provides the ranking of a number within its group. When you have a list of student grades, for instance, and want to know where each student stands in the class, you can use RANK to find out. The value attribute is the student’s grade (a cell reference, of course); range is the grades for all the students so that a comparison can be made; high or low is a number that indicates whether you want the highest number or the lowest number ranked first (maybe it’s golf scores).

  Use 1 for the largest to be first; otherwise, use zero. Identical numbers are ranked identically: if there are two 5’s, they are both ranked as 12, for instance, but the next number ranking adjusts for that, so a 6 would then be ranked as 14, skipping the 13.

• LARGE · SMALL
  LARGE(values, ranking) · SMALL(values, ranking)
  LARGE(A2:D10,1)

  These functions let you get a number from the high (LARGE) or low (SMALL) end of a group. A ranking of 1 means you want the largest or smallest number; a ranking of 2 means you want the second largest or second smallest, and so on.

• PERCENTILE
  PERCENTILE(range, percentile)
  PERCENTILE(D2:D200, .95)

  You’re probably familiar with percentiles in regard to standardized testing, when, say, a score of 1492 is referred to as being in the 95th percentile, meaning 95% of the scores were lower than that.

  With that as an example, PERCENTILE’s range argument is the collection of scores for all test-takers; the percentile argument (which is expressed as either a decimal or a percentage) is .95; and it would return 1492—the value beneath which 95% of the scores fell.

• QUARTILE
  QUARTILE(range, quartile-number)
  QUARTILE(K1:K120, 2)

  QUARTILE returns the dividing point for a specific quartile of a set of numbers. The attribute quartile-number specifies which quartile you want: 1 for the first (which is the 25th percentile), 2 for the second quartile (50th percentile), or 3 for the third (75th percentile). You can also use zero or 4, but they return the same numbers as MIN and MAX, respectively.

Reference Functions

This short list begins with the seemingly subtle but incredibly handy reference function, CHOOSE. Then there are some lookup functions, which, while also handy, and simple in concept, can be off-putting if you have only Numbers’ explanation and syntax guide to help you along. Luckily, you have a Take Control guide for a better explanation. These functions let you look up a value (almost always in another table), and grab information from somewhere in that value’s row or column—or even from anywhere within a defined block of cells that includes the value. So, for instance, you can look up a state’s sales-tax rate from a tax-rate table, based on the address of the person ordering.

This list ends with a quickie, HYPERLINK, that lets you embed a clickable Web link in your table—or an email address that will open the default Mail app, open a message window, put in an email address, and fill in the subject line. Pretty good for a quickie!

• CHOOSE
  CHOOSE(index, value list…)
  CHOOSE(B2,"Excellent","Good","Fair","Poor")

  CHOOSE returns an item from the value list whose position is identified with the index value; if the index value is 3, the function returns the third item in the value list.

  If, for instance, you have ratings of 1–4 for the condition of the cards in your Magic the Gathering cards collection that you’ve catalogued in Numbers, you can put a formula (the example above) in a cell to translate the numeric rating into a text description.

• VLOOKUP
  VLOOKUP(what-to-look-up, range-to-look-in, column-in-range-for-result, match)
  VLOOKUP(A2,B2:D5,2,0)

  This function returns the value from a specified column (“v” is for “vertical”) within a range, based on the position of a supplied value. So, if the supplied value is New Jersey you could wind up with the abbreviation NJ or the sales-tax rate .07, depending which column you look in.

  Say you’re working in a Sales table, which has the state entered in one column (using Pop-up Menus to ensure the spelling is correct so it will be found with the lookup), and you want its abbreviation and tax rate entered into columns B and C. Your Reference table contains state abbreviations and sales tax rates stored in A2:B52 and A2:C2, respectively (Figure 115).

  

  Figure 115: VLOOKUP functions in the Abbrev and Tax Rate columns of the Sales table grab information from the Reference table. The SELECT STATE entry in the Sales table is the default prompt for the pop-up menu that lists the states.

  The VLOOKUP formula for the abbreviation lookup goes in the second column of the Sales table, starting in B2. Its first argument is “what to look up” in the Reference table. You want to find whatever state is in the first column, so you begin with a reference to cell A2:

  VLOOKUP(A2

  The second argument is the cell range where the formula should look for that first value. The range must include the value you’re looking up in its first column, and the value you want returned in another of its columns; in this case, it’s the block A2:B52 in the Reference table. Using the syntax for referring to cells in a table other than the current one, the formula looks like this so far:

  VLOOKUP(A2,Reference::A2:B52

  Now you must specify which column of the lookup range you just defined contains the answer you’re looking for. The abbreviations are in the second column of the range, so you use a 2:

  VLOOKUP(A2,Reference::A2:B52,2

  The final argument for the VLOOKUP function indicates whether, in the case of no match being found, you want the next-best thing—the closest value that is no higher than the search value. For that, you enter a 1 or leave out the argument. In this case, we want only an exact match, so we enter a zero as the last variable:

  VLOOKUP(A2,Reference::A2:B52,2,0)

  When you specify an exact match but none is found, the function returns an error in the cell (see Error Reports, earlier). This isn’t because the formula is wrong, but because no match was found (so check for that before wasting time trying to fix your formula).

  Note: If you’re typing, you enter a number for the last option. If you’re working with tokens in the formula editor, your choices are exact match or close match, so you don’t have to remember which number represents what option.

  So far, I’ve been using easy-to-read cell references in the formula, which will work in the initial cell, but not when you autofill down through the rest of the column. You’ll need absolute cell references (see Relative vs. Absolute Cell References for that), so the formula looks like this:

  VLOOKUP(A2,Reference::$A$2:$B$52,2,2)

  When it comes to looking up the tax rate, there are two adjustments to the previous formula: the look-in range expands to three columns (A2:C52), and the column for the result is the third of the three in the range (the Rate column in the Reference table). So, the formula in C2 in the Sales table would be:

  VLOOKUP(A2,Reference::$A$2:$C$52,3,2)

Try it: The 27·Lookup example sheet in The Hands-on Example Spreadsheet has abbreviated tables with these lookup formulas in it.

Note: For all lookup functions, when you look up text, case is ignored (they are “case-insensitive”), so ALABAMA matches Alabama.

• HLOOKUP
  HLOOKUP(what-to-look-up, range-to-look-in, row-in-range-for-result, match)
  HLOOKUP(A2,B2:D5,2,0)

  This function returns the value from a specified row (“h” is for “horizontal”) within a range. Its function and structure is analogous to VLOOKUP, described just above.

• HYPERLINK
  HYPERLINK(URL, displayed-text)
  HYPERLINK("mailto:address?subject=subjectText", "displayedText")
  HYPERLINK("http://www.takecontrolbooks.com","Take Control Books")
HYPERLINK("mailto:TheBoss@example.com? subject= Promotions","Click to apply for job")

  The HYPERLINK function serves two purposes: providing a link to a Web page, and automating an email message window.

  Linking to a Web page is simple. The first argument is the full URL, including the http://www. prefix. The second, optional, argument is what you want displayed in the cell; if you leave out the argument, the URL is shown. Each argument must be inside quote marks.

  Note: The first sheet of The Hands-on Example Spreadsheet uses the HYPERLINK function twice. One link goes to the catalog Web page for this ebook, and the other link goes to an unaltered copy of the file. (I actually “laid out” some of that page using a table so I could embed the links.)

  Embedding an email function works similarly. You must supply the email address, the subject line for the message, and the text you want displayed in the cell. It can be a little confusing because you put this information inside quotes along with required text. In this example, “your” text is TheBoss@example.com, Promotions, and Click to apply for job.

  HYPERLINK("mailto:TheBoss@example.com? subject= Promotions","Click to apply for job")

  Note that, again, each argument (one is the address and the subject line, the other is the text to be displayed) is inside quote marks.

  Clicking such a link opens the user’s default mail app, opens a message window, and inserts the email address and subject line.