Excel provides no direct way to display the ratio between two values. For example, assume cell A1 contains 3, and cell B1 contains 24. The ratio between these two values is 1:8.

Following is a formula, contributed by Douglas J. Roach, that displays the ratio between the values in cells A1 and B1:

=(LEFT(TEXT(A1/B1,"####/####"),FIND("/",TEXT(A1/B1,"####/####"))-1)&":"
&RIGHT(TEXT(A1/B1,"####/####"),LEN(TEXT(A1/B1,"####/####"))
-FIND("/",TEXT(A1/B1,"####/####"))))

The formula automatically reduces the "fraction" to the simplest form, and it allows up to four characters on either side of the colon.

Jerry Meng pointed out a much simpler formula that produces the same result, but does not have the four-character limit:

=A1/GCD(A1,B1)&":"&B1/GCD(A1,B1)

Jerry's formula uses the GCD function, which is available only when the Analysis Toolpak Add-In is installed.

Note: Be aware that the result of these formulas is actually a time value, not a fractional value. For example, the ratio of 1:8 is not the same as 1/8. Rather, it is represented internally as 1:08 am.

Excel's IF function provides some simple decision-making capability to a worksheet. The IF function accepts three arguments:

• The condition being evaluated (should result in either TRUE or FALSE)
• The value to display if the condition is TRUE
• The value to display if the condition is FALSE

The formula below, for example, returns 1 if cell A1 contains "A". If cell A1 does not contain "A", the formula returns an empty string.

=IF(A1="A",1,"")

For more decision-making power, you can "nest" IF functions within a formula. In other words, you can use an IF function as the second argument for an IF function. Here's an example:

=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,"")))

This formula checks cell A1. If it contains "A", the formula returns 1. If it doesn't contain "A", then the second argument is evaluated. The second argument contains another IF function that determines if A1 contains a "B". If so, the formula returns 2; if not, the formula evaluates the IF function contained in the second argument and checks to see if A1 contains "C". If so, it returns 3; otherwise, it returns an empty string.

Excel allows up to seven levels of nested IF functions. The formula below works correctly, but Excel will not allow you to nest the IF functions any deeper than this.

=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4,
 IF(A1="E",5,IF(A1="F",6,IF(A1="G",7,IF(A1="H",8,""))))))))

The sections that follow present various ways to get around the limit of seven nested IF functions. Be aware that these techniques may not be appropriate for all situations.

Using a VLOOKUP formula

In many cases, you can avoid using IF functions and use a VLOOKUP function. This will require a separate table in your worksheet. In the figure below, the lookup table is in B1:C10. The formula in A2 is:

=VLOOKUP(A1,B1:C10,2)

Using defined names

Another way to overcome the nested IF function limit is to use named formulas. Chip Pearson describes this technique at his web site, so I won't repeat it here.

• Nested Functions, at cpearson.com

Using the CONCATENATE function

Yet another option was suggested to me by B. Ganesh: Use the CONCATENATE function. In this case, each argument for CONCATENATE consists of an IF function. Here's an example:

=CONCATENATE(IF(A1="A",1,""),IF(A1="B",2,""),IF(A1="C",3,""),
IF(A1="D",4,""),IF(A1="E",5,""),IF(A1="F",6,""),IF(A1="G",7,""),
IF(A1="H",8,""),IF(A1="I",9,""),IF(A1="J",10,""))

The CONCATENATE function can handle as many as 30 arguments -- which equates to testing 30 different conditions.

And, as Alan Williams pointed out, you can avoid using the CONCATENATE function and use the concatenation operator (&):

   =IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"")
    &IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"")
   &IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,"")
   &IF(A1="J",10,"")

This method is not limited to 30 comparisons.

Use Boolean multiplication

Another alternative, suggest by Daniel Filer is to use Boolean multiplication. This technique takes advantage of the fact that, when multiplying, TRUE is treated as 1 and FALSE is treated as 0. Here's an example:

=(A1="A")*1+(A1="B")*2+(A1="C")*3+(A1="D")*4+(A1="E")*5
+(A1="F")*6+(A1="G")*7+(A1="H")*8+(A1="I")*9+(A1="J")*10

Creating a custom VBA function

The final alternative is to create a custom worksheet function, using VBA. The advantage is that you can customize the function to meet your requirements, and your formulas can be simplified quite a bit.

This tip describes how to create what I call a "megaformula" -- a single formula that does the work of several intermediate formulas.

An Example

The goal is to create a formula that returns the string of characters following the final occurrence of a specified character. For example, consider the text string below (which happens to be a URL):

http://spreadsheetpage.com/index.php/tips

Excel does not provide a straightforward way to extract the characters following the final slash character (i.e., "tips") from this string. It is possible, however, do do so by using a number of intermediate formulas. The figure below shows a multi-formula solution. The original text is in cell A1. Formulas in A2:A6 are used to produce the desired result. The formulas are displayed in column B.

Following is a description of the intermediate formulas (which will eventually be combined into a single formula).

1. Count the number of slash characters (Cell A2)
   The formula in cell A2 returns the number of slash characters in cell A1. Excel doesn't provide a direct way to count specific characters in a cell, so this formula is relatively complex.
2. Replace the last slash character with an arbitrary character (Cell A3)
   The formula in A3 uses the SUBSTITUTE function to replace the last slash character (calculated in A2) with a new character. I chose CHAR(1) because there is little chance of this character actually appearing in the original text string.
3. Get the position of the new character (Cell A4)
   The formula in A4 uses the FIND function to determine the position of the new character.
4. Count the number of characters after the new character (Cell A5)
   The formula in A5 subtracts the position of the new character from the length of the original string. The result is the number of characters after the new character.
5. Get the text after the new character (Cell A6)
   The formula in A6 uses the RIGHT function to extract the characters -- the end result.

Combining the Five Formulas Into One

Next, these five formulas will be combined into a single formula.

1. Activate the cell that displays the final result (in this case, cell A6). Notice that it contains a reference to cell A5.
2. Activate cell A5. Press F2 and select the formula text (but omit the initial equal sign), and press Ctrl+C to copy the text. Press Esc.
3. Re-activate cell A6 and paste the copied text to replace the reference to cell A5. The formula in A6 is now:

=RIGHT(A1,LEN(A1)-A4)

4. The formula contains a reference to cell A4, so activate A4 and copy the formula as text. Then replace the reference to cell A4 with the copied formula text. The formula now looks like this:

RIGHT(A1,LEN(A1)-FIND(CHAR(1),A3))

5. Replace the reference to cell A3 with the formula text from cell A3. The formula now looks like this:

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),A2)))

6. Replace the reference to cell A2 with the formula text from cell A2. The formula now looks like this:

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

The formula now refers only to cell A1, and the intermediate formula are no longer necessary. This single formula does the work of five other formulas.

This general technique can be applied to other situations in which a final result uses several intermediate formulas.

NOTE: You may think that using such a complex formula would cause the worksheet to calculate more slowly. In fact, you may find just the opposite: Using a single formula in place of multiple formulas may speed up recalculation. Any calculation speed differences, however, will probably not be noticeable unless you have thousands of copies of the formula.

Caveat

Keep in mind that a complex formula such as this is virtually impossible to understand. Therefore, use this type of formula only when you are absolutely certain that it works correctly and you are sure that you will never need to modify it in the future. Better yet, keep a copy of those intermediate formulas -- just in case.

This tip describes a technique that should be in the arsenal of every Excel user. It describes how to use formulas to transform data.

The figure below shows a simple example. The text in column A consists of lower case letters. The goal is to transform these cells so they display "proper" case. This will be done by creating formulas that use Excel's PROPER function.

The steps below are specific to this example. But they can easily be adapted to other types of data transformations.

Creating the formulas

In this case, the formulas will go in column D. As you'll see, this is just a temporary location. The formula results will eventually replace the names in column A.

1. Enter the following formula in cell D2:

=PROPER(A2)

2. Copy the formula down the column to accommodate the data. In this case, the formula is copied down to cell D11. The worksheet now looks like this (the formula cells are selected, so they appear highlighted).

Copying and pasting the formula cells

In this step, the formula cells are copied, and pasted as values -- overwriting the original data in column A.

1. Select the formula cells. In this case, D2:D11.
2. Choose Edit - Copy
3. Select the first cell in the original data column (in this case, cell A2).
4. Choose Edit - Paste Special. This displays the Paste Special dialog box.
5. In the Paste Special dialog box, click the Value option button. This step is critical. It pastes the results of the formulas -- not the formulas.
6. Click OK.

At this point, the worksheet looks like this:

Deleting the temporary formulas

The formulas in column D are no longer necessary, so you can delete them.

Q. I have a range of time values, but when I try to sum them, the total is never greater than 24 hours.

When you add a range that contains time values, Excel ignores the hours that exceed 24. The solution is to use a custom number format.

1. Activate the cell that contains your total time
2. Choose Format, Cells.
3. In the Format Cells dialog box, click the Number tab.
4. Choose Custom from the Category list
5. Type [h]:mm into the box labeled Type.

Using brackets around the hour portion of the format string tells Excel to display hours that exceed 24 hours.

Q. I find that sometimes my formulas do not get fully calculated. This often happens when I use custom functions created with VBA.

Microsoft has acknowledged some problems with the Excel calculation engine in some version of Excel. In order to be assured that all of your formulas have been calculated, press Ctrl-Alt-F9 to force a complete recalculation.

This key combination will also update formulas that use custom VBA functions.

Q. Can I write a formula that returns the number of distinct entries in a range?

First, let's clarify the question. We're hunting for a formula that, given the range that contains the values 100, 99, 98, 100, 98, 100, 98, would return 3. In other words, this range contains three different values, some of them repeated.

This type of counting requires an array formula. The formula below, for example, counts the number of distinct entries in the range A1:D100.

=SUM(1/COUNTIF(A1:D100, A1:D100))

When you enter this formula, you must press Ctrl-Shift-Enter. Pressing only Enter will give you the wrong result. Excel will place brackets around the formula to remind you that you've created an array formula.

The preceding formula works fine in many cases, but it will return an error if the range contains any blank cells. The formula below (also an array formula, so input it with Ctrl-Shift-Enter) is more complex, but it will handle a range that contains a blank cell.

=SUM(IF(COUNTIF(A1:D100,A1:D100)=0, "", 1/COUNTIF(A1:D100,A1:D100)))

Q. I've created some clever formulas, and I don't want anyone else to see them. Is it possible to hide the formulas but display the results?

Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected. By default, every cell is locked and not hidden. But it's important to remember that these attributes have no effect unless the worksheet itself is protected.

First, to change the attributes, select the appropriate cell or range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden (or both). Unlock cells that accept user input, and lock formula and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells: The results of the formulas will be visible, but the formulas will not.

Now, to protect the worksheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog box. Make sure the Contents box is checked. You can enter a password to prevent others from unprotecting the sheet. Locked cells in a protected sheet cannot be edited, and other worksheet changes are disabled. For example, no one can insert rows or columns, change column width, or create embedded charts.

NOTE: Keep in mind that it is very easy to break the password for a protected sheet. If you are looking for real security, this is not the solution.

Q. I have a price list stored in a worksheet, and I need to increase all prices by 5 percent. Can I do this without reentering all the prices?

Excel provides two ways to accomplish this. The "traditional" technique goes something like this:

1. Insert or find a blank column near the prices.
2. In that column's first cell, enter a formula to multiply the price in that row by 1.05.
3. Copy the formula down the column.
4. Select and copy the entire column of formulas
5. Select the original prices, and choose Edit, Paste Special.
6. In the Paste Special dialog box, select Values to overwrite the original prices with the formulas' results.
7. And finally, delete the column of formulas.

The other, more efficient approach also uses the Paste Special dialog box. To increase a range of values (prices, in this example) by 5 percent:

1. Enter 1.05 into any blank cell.
2. Select the cell and choose Edit, Copy.
3. Select the range of values and choose Edit, Paste Special.
4. Choose the Multiply option and click OK.
5. Delete the cell that contains the 1.05.

Sometimes a formula may return an error message. Usually, you'll want to know when a formula error occurs. But now and then you may prefer to avoid the messages. You can do so by using an IF() function to check for an error.

For example, the formula below displays a blank if the division results in an error.

=IF(ISERROR(A1/B1),"",A1/B1) 

You can adapt this technique to any operation. The original formula serves as the argument for the ISERROR() function, and it repeats as the last argument of the IF() function. Like this:

=IF(ISERROR(OriginalFormula),"",OriginalFormula) 

Q. Is it possible to round a dollar amount to the nearest 25 cents? For example, if a number appears as $1.65, I would like to convert it to $1.75. Excel's ROUND() function seems to work only with whole numbers.

Yes, you can use Excel's ROUND() function to achieve the rounding you want. The following formula, which assumes that your value is in cell A1, will do the job for you.

=ROUND(A1/.25,0)*.25

The formula divides the original value by .25 and then multiplies the result by .25. You can, of course, use a similar formula to round values to other fractions. For example, to round a dollar amount to the nearest nickel, simply substitute .05 for each of the two occurrences of ".25" in the preceding formula.

Your worksheet may be set up with formulas that operate on a number of input cells. Here's an easy way to clear all input values while keeping the formulas intact.

Press F5 to display the Go To dialog box, and click the Special button. In the Go To Special dialog, choose the Constants button and select Numbers. When you click OK, the nonformula numeric cells will be selected. Press Delete to delete the values. The Go To Special dialog box has many other options for selecting cells of a particular type.

Q. My Excel workbook has a sheet for each month, named January, February, and so on. I also have a summary sheet that displays key calculations for a particular month. For example, one of my formulas is:

=SUM(February!F1:F10) 

Is there any way that I can enter the month name into a cell on my summary sheet, and then have my formulas use the data for the specified sheet?

Yes. Excel's INDIRECT function was designed specifically for this sort of thing. This function accepts a text string as an argument, and then evaluates the text string to arrive at a cell or range reference. In your case, assume that cell B1 on your summary worksheet holds the month name. The following formula utilizes the INDIRECT function to create the range reference used by the SUM function:

=SUM(INDIRECT(B1&"!F1:F10"))

Note that I use the ampersand operator to join the month name with the cell reference (expressed as text). Refer to the figure below. If cell B1 contains the text March, the SUM function returns the sum of the range March!F1:F10.

All spreadsheets support lookup functions, tools that return a value from a table by looking up another value in the table. An income tax table is a good example. You can write a formula that uses the VLOOKUP function to determine the tax rate for a given income amount.

The lookup functions in Excel are only appropriate for one-way lookups, however. If you need to perform a two-way lookup, you'll need more than the standard functions. The figure below shows a simple example.

The formula in cell H4 looks up the entries in cells H2 and H3 and then returns the corresponding value from the table. The formula in H4 is:

=INDEX(A1:E14, MATCH(H2,A1:A14,0), MATCH(H3,A1:E1,0)).

The formula uses the INDEX function, with three arguments. The first is the entire table range (A1:A14). The second uses the MATCH function to return the offset of the desired month in column A. The third argument uses the MATCH function to return the offset of the desired product in row 1.

You may prefer to take advantage of Excel's natural-language formulas. For example, enter the following formula to return Sprocket sales for June:

=June Sprockets 

If natural-language formulas aren't working, select Tools, Options, click the Calculation tab, and place a check mark next to "Accept labels in formulas." Be aware that using natural language formulas is not 100% reliable!

A companion file is available: Click here to download

Q. When I use Excel's AutoFiltering, the status bar displays the number of qualifying rows. But for no apparent reason, that number often vanishes. How do I keep this number visible while I work?

AutoFiltering a list hides rows that don't meet your filter criteria. After applying filtering criteria, Excel shows the record count on the status bar--but this value disappears when the sheet is calculated.

To display a permanent count of the visible rows in an AutoFiltered list, create a formula using Excel's SUBTOTAL function. The first argument for SUBTOTAL specifies the type of operation (an argument of 2 displays a count of the visible cells in a range).

The figure below shows a list in rows 6 through 3006. The formula in cell D3 is:

=SUBTOTAL(2,A6:A3006)

The formula counts the number of visible cells in the range (minus the header row). Apply different filtering criteria, and the formula updates to show the new count. The SUBTOTAL function only works for AutoFiltering and outlining. If you hide rows manually, it won't return the correct result.

Q. I need to calculate the sum of the three largest values in a range of 100 cells. The range isn’t sorted, so I can’t use a SUM function. Do you have any suggestions about how I could handle this problem?

Excel’s LARGE function returns the nth-largest value in a range, in which n is the function’s second argument. You need a formula that calls the LARGE function three times and then sums the results. The following formula, which assumes the numbers are located in the range A1:A100, will do the job:

=LARGE(A1:A100,1)+ LARGE(A1:A100,2)+ LARGE(A1:A100,3)

Another approach is to use an array formula like this one:

=SUM(LARGE(A1:A100,{1,2,3}))

The formula first passes an array of three values to the LARGE function, and then uses the SUM function to add the values returned by the LARGE function. Notice that the values 1 through 3 are enclosed in brackets rather than parentheses. After typing an array formula, press Ctrl-Shift-Enter instead of Enter.

Formulas of this type can become unwieldy as n gets larger. For example, to sum the top 30 values in a range, a formula must contain a list of integers from 1 to 30. Here is a more general version of the array formula:

=SUM(LARGE(A1:A100,ROW(INDIRECT ("1:30"))))

This formula uses the ROW function to generate a series of integers between 1 and 30, and uses this array as the second argument for the LARGE function. To sum a different quantity of numbers, just change the 30 to the desired number.

Sometimes you just don't want data to repeat itself. On an order form or an inventory sheet, for instance, you may not want a part number entered in one cell to repeat in another cell. You can use Excel's Data Validation feature to to prevent a value from appearing more than once in a range.

In the example below, the range A2:A20 requires unique part numbers. If the user enters a number that already exists, a message box pops up and asks for a different one.

To create this type of message box for your worksheet:

1. Select the cells for which you need to punch in unique entries (here, the correct range to select is A2:A20).

2. Choose Data, Validation and click the Settings tab.

3. Choose Custom from the Allow drop-down list. The Custom option requires a logical formula that will return either "True" or "False." This example requires a formula that will return "True" only if the content of the cell does not match one that already exists in the range. The COUNTIF function will do the job. Type the following formula into the Formula field:

   =COUNTIF($A$2:$A$20,A2)=1 

   This formula counts the number of cells in range A2:A20 that contain the same value that appears in cell A2. If the count is 1, the formula returns "True"; otherwise, it returns "False." Notice that the first argument for COUNTIF is an absolute reference to the entire validation range. In contrast, the second argument is a relative reference to the upper left cell in the validation range (it will be adjusted for each of the other cells in the range).

4. Next, to create the warning that appears in a pop-up message box when a duplicate value is entered into the selected range, click the Error Alert tab in the Data Validation dialog box. For the Style, select Stop (this option rejects existing values). Enter a title for the message box (such as Duplicate Data) and type your error message.

5. Click OK and try it out.

You'll find that you can enter any data into the validation range, but if you type an entry that already exists, you'll get the warning message. Click Retry to edit the cell's contents or choose Cancel to clear the cell.

While Data Validation is a useful feature, it contains a potentially serious design flaw. If you copy a cell and paste it to a cell that uses the feature, the Data Validation rules are wiped out. This problem also applies to cells that use Conditional Formatting. You'll need to keep this in mind when you're cutting and pasting in mission-critical applications.

A companion file is available: Click here to download

In the eyes of Excel, the world began on January 1, 1900. Excel is not capable of working with dates earlier than that.

People who use Excel to store historical information often need to work with pre-1900 dates. The only way to create a date such as July 4, 1776, in Excel is to enter it into a cell and have the program interpret it as text. Unfortunately, you can't manipulate dates stored as text -- if you want to alter their formatting, for example, or if you need to calculate the day of the week they fell on.

To address this problem, I created an add-in (for Excel 97 or later versions) called Extended Date Functions. With this add-in installed, you'll have access to eight new worksheet functions that let you work with dates in any year from 0100 through 9999.

Note: Be careful if you plan to insert dates that occurred before 1752. Differences between the historical American, British, Gregorian, and Julian calendars can result in inaccurate computations.

Excel includes three functions (ROUND, ROUNDUP, and ROUNDDOWN) that round values to a specified number of digits. In some cases, however, you may need to round a value to a specified number of significant digits.

For example, you might want to express the value 1,432,187 in terms of two significant digits (that is, as 1,400,000). Here's an elegant solution. The formula below rounds the value in cell A1 to the number of significant digits specified in cell A2:

=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))

If you've ever had to figure out which of the year's 365 days a particular date falls on, or how many days remain in the year, you've probably found that Excel lacks functions to perform the calculation. But you can create formulas to do the job.

The formula below returns the day of the year for a date in cell A1:

=A1-DATE(YEAR(A1),1,0)

Note: Excel automatically formats the cell as a date, so change the number format to another option (like General).

To calculate the number of days remaining in the year (assuming that the date is in cell A1), use the following formula:

=DATE(YEAR(A1),12,31)-A1