Topic: Worksheet Functions

The following are additional topics related to the category listed above. A bracketed number after the topic indicates how many articles are related to that subject.

Tips, Tricks, and Answers

The following articles are available. 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Counting Unique Values with Functions Using a few categories of functions in Excel to count unique values. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Functions Within Functions Using functions within functions in Excel to create just about any formula you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Getting Help when Entering Functions Need a quick memory jog when entering a worksheet function? Here’s a shortcut that will be invaluable. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Random Numbers in a Range The RANDBETWEEN function is a valuable worksheet function that allows you to generate a random number between some boundary values that you specify. This tip explains how to use the function. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Returning a Blank Value Some people want a formula to return a blank value based upon a calculation or comparison. Such a condition is impossible in Excel—formulas can never return blank values. (In fact “blank value” could be considered an oxymoron, as any value is, by definition, non-blank.) This tip explains the details of such statements. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selecting Random Names Got a list of names from which you need to select a few at random? There are several ways you can accomplish this task, using either formulas or macros. Each of the most common methods are described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Understanding Functions Excel uses Functions to assist in creating spreadsheets that perform a multitude of calculations. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Understanding Functions in Macros Functions are a special type of subroutine that returns a value to whatever program called it. This tip explains how to create functions and how to pass parameters to a function. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Working with Roman Numerals Understanding and using a function to replace an Arabic number with Roman numerals. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

More Topics

Tips, Tricks, and Answers

Confused by Time?

Helpful Links

Great Info!

RSS Feeds