Worksheet Functions

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
Excel provides, as part of the Analysis Toolpak, a worksheet function that can use to determine what percentage of a year is represented by the difference between two dates. This tip explains how to put this function to work.

Converting Strings to Numbers
When creating a macro, you'll often need to convert strings to numbers. For instance, you might have some user input that needs to be converted. This is done using the Val function, as described in this tip.

Converting to Hexadecimal
One of the numbering systems often used by programmers is hexadecimal. Excel includes a function (DEC2HEX) that allows you to convert decimal values to hexadecimal.

Converting to Octal
One of the numbering systems often used by programmers and electrical engineers is base 8, or octal. Excel allows you to convert decimal values to and from octal, using the functions highlighted in this tip.

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
Using functions within functions in Excel to create just about any formula you need.

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
Circular references often, but not always, represent a mistake in the logic of your workbook. If you purposefully add a circular reference, you can configure Excel to treat that reference in a particular way. This tip explains how that configuration is done.

Numbers in Base 12
Do you want to display information in "base 12" so that you can easily see how many dozens of an item you have? This tip shows a couple of ways to get the desired information.

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
Excel uses Functions to assist in creating spreadsheets that perform a multitude of calculations.

Using the WEEKNUM Function
The Analysis Toolpak includes the WEEKNUM function, which returns the week of the year in which a particular date falls. Understanding how the function works is important to interpreting its results.

Working with Roman Numerals
Understanding and using a function to replace an Arabic number with Roman numerals.

 

More Information

The following are additional topics related to the subject of 'Worksheet Functions'. A bracketed number after the topic indicates how many articles are related to that subject.