Topic: Math and Trig Functions

The following articles are available. Click the article's title (shown in bold) to see the associated article.

Calculating Combinations The COMBIN worksheet function is used to calculate the number of different combinations that can be derived from a given set. This tip explains the two parameters for the function and provides an example of how to use it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting Radians to Degrees The DEGREES function, explained in this tip, is used to convert radians to degrees. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Deriving Antilogs Antilogs are not one of the trigonometric functions that are built into Excel. You can easily calculate these values by creatively using some of the other worksheet functions available. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Determining the Least Common Multiple A least common multiple is the number into which a series of numbers can be divided evenly. Excel provides a worksheet function, LCM, which allows you to easily determine the least common multiple of any series you designate. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Establishing a FLOOR and CEILING Two of the more esoteric rounding functions in Excel are FLOOR and CEILING. These functions allow you to round in very specific ways using multiples of numbers. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Finding the Nth Root of a Number Figuring out square roots is easy, but how do you figure out third, fourth, fifth, or any number of other roots? While Excel doesn’t provide built-in worksheet functions to handle this, you can use a simple formula to figure out the desired result. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Large Numbers in the MOD Function There is a known bug in the MOD function that stops it from working with large numbers and small divisors. This tip examines the bug and discusses ways to work around it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Returning the MODE of a Range The MODE function is used to determine the most frequently recurring value in a range. This tip explains how to use the function in your worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Rounding Numbers The primary method of rounding values is to use the ROUND function in your formulas. Here’s an introduction to this useful worksheet function. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Rounding to Even and Odd Values You can use the EVEN and ODD worksheet functions to round a value to the next highest even or odd value. This can come in handy for some data processing where you must work with such values. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Rounding to Powers of 10 Need to round a value to a power of 10? You can do it by using the ROUND function as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Rounding to the Nearest $50 Excel provides a worksheet function, MROUND, that allows you to round a value to the nearest multiple of a given rounding value. This tip explains how you can use this function to round to the nearest $50, but it also provides other formulas you can use if you don't want to use MROUND. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selective Summing The SUMIF function is used to return the sum of a set of values within a range of cells, but only if the values meet whatever criteria that you specify. This tip explains how to use the function properly. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

SUMIF Doesn't Recalc Automatically What are you to do if you suspect that some of your worksheet functions aren’t recalculating automatically? Here’s some ideas you can check out. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Summing Only Positive Values If you have a series of values and you want to get a total of just the values that meet a specific criteria, then you need to become acquainted with the SUMIF function. This tip shows how it can be used to sum just the positive values in a list. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Summing Only Visible Values The SUM function doesn’t allow you to ignore hidden values in a range being summed. Instead, you are better off using the SUBTOTAL function or a special user-defined function, both of which are described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Throwing Out the Lowest Score Sometimes you need to sum a set of values, without including the lowest value in the set. (This is sometimes done in grading papers, where the lowest individual score for a student is ignored.) You can easily create the necessary formula by using both the SUM and SMALL functions. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Understanding Operators An explanation of the operators used in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using the ABS Function The ABS function is used to return an absolute value. This tip explains how to use this simple function. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using the INT Worksheet Function The INT function is used to return the integer portion of a value—the part to the left of the decimal point. It is similar to the TRUNC function, but it always returns the next lower integer value, which becomes important when working with negative values. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using the IRR Function One of the handy financial functions available in Excel is IRR, which is used to calculate an internal rate of return for an investment. This tip explains how to use this function (and it’s not as difficult as you might think). Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using the MROUND Worksheet Function If you want to round a value to some multiple of a whole number, you’ll want to become familiar with the MROUND function. This tip explains how you can use this function. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using the SUBTOTAL Function One of the very versatile and flexible functions in Excel is SUBTOTAL. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using the TRUNC Worksheet Function The TRUNC function is used to return the integer portion of a value—the part to the left of the decimal point. It is similar to the INT function, with some interesting differences in what is returned. This tip explains not only the function, but the nuances that you need to be aware of. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using the XIRR Function The XIRR function is used to determine the rate of return with irregular payments. This tip explains how to use the function. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Tips, Tricks, and Answers

Macros 'Power Up' Excel!

Helpful Links

Great Info!

RSS Feeds