Tips, Tricks, and Answers

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

   Calculating Fractions of Years
When working with dates and the relationship between dates, Excel provides a variety of worksheet functions that may prove helpful. One such function is YEARFRAC, which allows you to calculate what fraction of a year is represented by the number of days between two dates.

   Converting Strings to Numbers
When working with data in a macro, there are two broad categories you can manipulate: numbers and text. Sometimes you need to convert information from one category (data type) to another. Here is how you convert text to numbers.

   Converting to Hexadecimal
Excel allows you to easily convert values from decimal to other numbering systems, such as hexadecimal. This tip explains how to use the DEC2HEX worksheet function.

   Converting to Octal
If you need to do some work in the base-8 numbering system (octal), you'll love two worksheet functions provided by Excel for this purpose. These functions allow you to convert values to octal and convert them back again.

   Counting Displayed Cells
When you filter data, Excel displays only a portion of what is really in a worksheet. If you want to count the number of cells that are displayed after filtering, then you'll want to explore the techniques in this tip.

   Counting Unique Values with Functions
Using Excel to maintain lists of information is not unusual. When working with the list you may need to determine how many unique values it contains. This tip shows you how.

   Functions Within Functions
Functions are the heart of Excel's power. The program allows you to compound that power by handily putting one function inside another function.

   Getting Help when Entering Functions
Need a quick memory jog when entering a worksheet function? Here's a shortcut that will be invaluable.

   Iterating Circular References
Does your data require that you perform calculations using circular references? If so, then you'll want to be aware of the way in which Excel handles those references.

   Numbers in Base 12
Different professions use numbers in entirely unique ways. You may need to come up with a number that represents the number of 12-unit groupings. This tip examines a way this can be done.

   Random Numbers in a Range
Excel provides several different functions that you can use to generate random numbers. One of the most useful is the RANDBETWEEN function, which allows you to generate a random number between a lower and upper boundary that you specify.

   Returning a Blank Value
Is it possible for a formula to return a blank value? It depends on how you define your terms. This tip examines all the ins and outs of returning "nothing" from a formula and how that affects some of the more common worksheet functions.

   Selecting Random Names
Got a tone of names from which you need to select a few random names? There are several ways you can extract what you need; several different ideas are explained in this tip.

   Understanding Functions
The heart of any formula you put together in Excel is worksheet functions. This tip introduces you to worksheet functions and what you can do with them.

   Using the WEEKNUM Function
Need to know which week of the year a particular date falls within? Excel provides the WEEKNUM function so you can easily calculate this statistic.

   Working with Roman Numerals
Understanding and using a function to replace an Arabic number with Roman numerals. And, as a bonus, how to change them back.