Tips, Tricks, and Answers

The following articles are available for the 'Math and Trig Functions' topic. Click the article''s title (shown in bold) to see the associated article.

   Calculating Combinations
The COMBIN function is used to determine the number of combinations that can be made from a group of elements. This tip explains the function and how you can use it.

   Converting Radians to Degrees
When applying trigonometry to the values in a worksheet, you may need to convert radians to degrees. This is done by using the DEGREES worksheet function, described in this tip.

   Deriving Antilogs
Creating math formulas is a particular strong point of Excel. Not all the functions that you may need are built directly into the program, however. Here's how you can use the existing functions of Excel to derive antilogs, which do not have functions built into the program.

   Determining the Least Common Multiple
Need to figure out the least common multiple of a range of values? It is a snap when you use the LCM function, described in this tip.

   Establishing a FLOOR and CEILING
Excel includes a surprising number of functions you can use to round your data. Two such functions are FLOOR and CEILING, described in this tip.

   Finding the Nth Root of a Number
Finding a square root is easy because Excel provides a worksheet function for that purpose. Finding a different root may not be as easy, unless you adapt the formula presented in this tip.

   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.

   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.

   Rounding by Powers of 10
Need to round a value by a power of 10? You can do it by using the ROUND function as described in this tip.

   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.

   Rounding to Even and Odd Values
Want to round values so they are always even or odd? You can do it quickly and easily by using the EVEN and ODD worksheet functions.

   Rounding to the Nearest $50
When preparing financial reports, it may make your data easier to understand if you round it to the nearest multiple, such as the nearest $50 increment. You can do that using the worksheet functions described in this tip.

   Selective Summing
If you want to add up the contents of a range of cells based on what is contained in a different range of cells, you need the SUMIF function. Here's how you can do your own selective summing by using this handy worksheet function.

   Strange ATAN Results
You may use Excel's trigonometric functions to do some quick calculations, and suddenly notice that the results in your worksheet are different than the results you get using a calculator. The reason could be as simple as understanding what Excel expects in its functions.

   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.

   Summing Only Positive Values
If you have a series of values and you want to get a total of just the values that meet 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.

   Summing Only Visible Values
When you use SUM to determine the total of a range of values, Excel doesn't really pay attention to whether the values are visible or not. Here's why and what you can do about it.

   Throwing Out the Lowest Score
Want to add up a bunch of scores, without including the lowest one in the bunch? You can make a small change to your formula to get the desired result.

   Using the ABS Function
Need to find the absolute value of a number? That's where the ABS function comes into play.

   Using the INT Worksheet Function
The INT function allows you to convert a value to an integer. The effect the function has depends on the characteristics of the number it is converting.

   Using the IRR Function
When working with finances, you often need to know the rate of return on a given investment. The most common type of calculation used is the internal rate of return, which in Excel is calculated using the IRR worksheet function.

   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.

   Using the SUBTOTAL Function
Need to sum up different ranges of cells? One of the tools you can use is the handy SUBTOTAL function, described in this tip.

   Using the TRUNC Worksheet Function
Want to chop off everything after a certain point in a number? The TRUNC function can help with this need.

   Using the XIRR Function
One of the financial worksheet functions provided in Excel is the XIRR function. This is used to figure out an internal rate of return when the payments associated with an investment are not evenly spaced. Here's how to use the function.