5 Really Cool Excel Functions - dummies

By Ken Bluttman

If you add this mix of useful functions to your plate of Excel goodies, and you will be that much more of an Excel master.

Easily generate a random number

The Excel RAND function returns a number between 0 and 1. And that’s it. Usually, you have to massage the returned number into something useful. The typical thing to do is multiply it by some number to get it within a range of values, add the lower limit to that, and finally use INT to turn the whole thing into an integer. The days of drudgery are over!

The RANDBETWEEN function returns a random integer between two values. Two arguments are used: the low end of the range and the high end of the range. Just what you need! For example, =RANDBETWEEN(5, 10) returns a whole number between 5 and 10. Always.

Convert to Roman numerals

C, V, L, I are easy to mix up. Is C for 100 or 1000? What is L for? Whew — you don’t have to memorize these anymore.

The ROMAN function takes care of it all. Just throw a number in the normal format you are familiar with, and out comes the equivalent Roman numeral. Easy! The syntax is

=ROMAN(number to convert, optional style)

Factor in a factorial

If you like multiplication, you will love the FACT function. A factorial, simply put, is the product of multiplying sequential integers. In math notation, 6! (notice the exclamation point) is 1 x 2 x 3 x 4 x 5 x6, which equals 720. Try it on your calculator or use an Excel sheet, of course.

The FACT function makes the tedious entry go away, which I think you will like. FACT just takes a number — the number of integers to use for the grand product.

Determine part of a year with YEARFRAC

If you need to know what percentage of a year a range of dates is, Excel has the perfect function for you! YEARFRAC returns a percentage of a year. You feed the function a start and end date, and an optional basis for how to count dates (such as a 360-day year, a 365-day year, and so on). The number given back from the function is a percentage — a number less than 1, assuming that the range of dates is less than a full year. An exact one-year range returns 1, and a range longer than a year returns a number larger than 1.

Find the data TYPE

The content in a cell may be text, a number, a logical value, an error, or an array. The TYPE function tells you which type the content is. When you’re looking at a cell, it’s obvious what the type is. However, if your formulas are using cell references then you may wish to put the TYPE function into the formula before attempting a mathematical operation. This ensures you can have a valid result returned instead of an error. For example, A4 has 25 and A5 has “Apple”. An attempt to add these results in an error. Instead put the TYPE function into the formula to determine if the calculation should take place. The formula would look like this:

=IF(TYPE(A4)=1&TYPE(A5)=1,A4+A5,"Unable to calculate")

The result in this case is Unable to calculate because you cannot add a number with text.

The TYPE function returns five possible values: 1=number; 2=text; 4= a logical value (And, Or, and so on); 16= an error; 64=an array.