Back

Math Formulas

• Without a doubt, the Math Function that I (and most people I come in contact with) use the most is the Round Function. The syntax for this is =ROUND(number,number of digits), where "number" is the value you want to round (it can be a number or a formula) and "number of digits" is the number of decimal places you want to round. For example, =ROUND(1/3,2) will return 0.33. This is particulary useful when dealing with currency calculations.
  Note, you can specify a negative number or zero for "number of digits". Specifying zero will return an integer while a negative number will round to the left of the decimal place. An example of this is =ROUND(1000/3,-2), which returns a value of 300.
• There are also few ways to round a number/formula up or down.
  • If you want to round up a number/formula (away from zero), then you want to use the Roundup Function. The syntax is =ROUNDUP(number,number of digits). Note that the syntax is very similar to the Round Function. =ROUNDUP(1/3,0) will return the value of 1.
    If you are looking to round down your number/formula (towards zero), then use the Rounddown Function. The syntax is =ROUNDDOWN(number,number of digits).
  As with the Round Function, "number of digits" can be negative.
  • Well the ability to round up (or down) is very useful, but what about a situation where you would want to round up (or down) to the nearest nickel? Excel has the ability to do this:
    Two functions, Ceiling and Floor, can do this for you. The syntax is =CEILING(number,multiple) and =FLOOR(number,multiple). "number" is the value/formula you want to round and "multiple" is the multiple you want to round to. =CEILING(1/3,0.05) will return 0.35, while =FLOOR(1/3,0.05) will return 0.30.
    Note that "multiple" must be the same sign as "number", you can't use =CEILING(-2,0.10).
  • What if you want to round a number to the next even or odd number (away from zero)? Well, Excel can do that too!
    Again, there are two functions Even and Odd that will do the trick. The syntax for these formulas is =EVEN(number) and =ODD(number), where "number" is the number/formula you want to round.
  TOP
• The Int Function can be used to round a number/formula down to the nearest integer. The syntax is =INT(number), where "number" is the number (or cell) that you want to round down to an integer.
TOP
• The Absolute Function will return the absolute value of a number (or formula). The syntax is =ABS(number).
  =ABS(-2) and =ABS(2) will both return the value of 2.
  TOP
• The Countif Function can be used to count the number of cells in a range that match the criteria you specify. The syntax is =COUNTIF(range,criteria), where "range" is the range of cells that you want to count and "criteria" is the criteria that specifies those cells to count. "criteria" can be a number, an expression, or text. An expression or text must be surrounded by quotation marks (").
  TOP
• Excel has two functions that can generate random numbers, the Rand and Randbetween functions. The Rand Function will generate a random number greater than, or equal to, 0 and less than 1. The syntax is =RAND().
  The Randbetween Function offers you the ability to declare the top and bottom ranges of the random number being generated. The syntax is =RANDBETWEEN(bottom,top), where "bottom" is the smallest integer you want returned and "top" is the largest integer you want returned. However, this function is only available of you have the Analysis Toolpak installed. Note that this function only returns integers so if you want a random number that isn't an integer, you'll have to use the Rand Function and multiply the value returned (=RAND()*12, for example).
  TOP
• Excel has the ability to return the roman numeral equivalent in text, I'm not sure why anybody would need this, by using the Roman Function. The syntax is =ROMAN(number), where "number" is the number/formula you want to convert.
  TOP
• Ever wanted to know the sign of a number/formula (yes, believe it or not, I have actually had a need for this)? Then the Sign Function is what you are looking for. The syntax is =SIGN(number), where "number" is the number/formula you want to determine it's sign. This will return 1 if "number" is positive, 0 if "number" is zero, and -1 if "number" is negative.
  TOP
• The function you need to sum a range of cells (or a bunch of numbers, or a bunch of formulas) is the Sum Function. The syntax for this is =SUM(number1,number2,..), where "number#" is a number/formula/cell (or range of cells) you want to add. You can specify up to 30 arguments for this function (number1,number2,...,number30).
  TOP
• What if you only want to sum numbers/formulas/cells if a certain condition is met (like the Countif Function)? Well, the Sumif Function is just what you are looking for. The syntax is =SUMIF(range,criteria,sumrange),where "range" is the range of cells that you want to evaluate, "criteria" is the criteria that specifies those cells to sum, and "sumrange" is the range you actually want to add. As with the Countif Function, "criteria" can be a number, an expression, or text. An expression or text must be surrounded by quotation marks (").
  TOP

Copyright ^© 2001 by Barrie R. Davidson
Last updated September, 2001