Creating A “Megaformula”
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):
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).
- 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.
- 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.
- 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.
- 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.
- 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.
- Activate the cell that displays the final result (in this case, cell A6). Notice that it contains a reference to cell A5.
- 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.
- Re-activate cell A6 and paste the copied text to replace the reference to cell A5. The formula in A6 is now:
- 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:
- Replace the reference to cell A3 with the formula text from cell A3. The formula now looks like this:
- Replace the reference to cell A2 with the formula text from cell A2. The formula now looks like this:
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.
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.
Alternatives To Nested IF Functions
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.
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:
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.
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.
Excel 2007 and later allows up to 64 nesting levels
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:
Using the CHOOSE function
In some cases, you can use the CHOOSE function. The first argument is an integer, and the value determines which of the subsequent arguments is evaluated and returned.
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:
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.
A Formula To Calculate A Ratio
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:
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 a text string, not a fractional value. For example, the ratio of 1:8 is not the same as 1/8.
Creating A Non-Graphic Chart Directly In A Range
This tip describes how to create a non-graphic chart. It uses formulas to display crude "bars" directly in a range of cells.
The figure below shows an example of what you can produce with his technique.
The formulas in columns E and G graphically depict monthly budget variances by displaying a series of characters in the Wingdings font. The number of characters displayed is determined by an IF function.
To re-create this chart in Excel, enter the data shown in columns A through D, and then enter the following formulas:
Assign the Wingdings font to cells E2 and G2, and then copy the formulas down the columns to accommodate all the data. Right-align the text in column E, and adjust any other formatting as you like.
Depending on the numerical range of your data, you may need to change the scaling. Experiment by replacing the '100' value in the formulas. You can, of course, substitute any character you like for the "n" in the formulas to produce a different character in the chart.
Creating A Linked Picture Of A Range
Many users overlook a useful Excel feature that can create a live "snapshot" of a range and placing it anywhere you like.
To use this feature:
Select a range of cells
Choose Edit, Copy
Hold down the Shift key, and then choose Edit, Paste Picture Link.
The result is an image of the selected range that will reflect any subsequent changes to the source.
This technique is great for printing noncontiguous ranges on a single page. After creating a series of linked pictures of ranges, set them to print on one page.
Note: The Paste Picture Link command is a hidden command, and it appears on the Edit menu only if you press the Shift key.
Creating A Thermometer Style Chart
Most people are familiar with "thermometer"-style graphs, which show the percentage of a project completed. It's easy to make such a display in Excel. The key is to create a chart that uses a single cell (containing a percentage value) as a data series.
The example below tracks daily progress toward a goal: 1000 new customers in a 15-day period.
Cell B18 contains the goal value. Cell B19 contains a simple sum formula:
Cell B21 contains the following formula, which calculates the percentage of the goal attained
As new data is entered in column B, the formulas display the current results.
To create the chart:
Enter the formulas listed above, along with the worksheet's sample data.
Select cell B21, and click the Chart Wizard button. Notice the blank row preceding cell B21. If you fail to include this blank row, Excel will use the entire data block--not just the single cell--to construct the chart. Since B21 is isolated from the other data, the Chart Wizard uses only the single cell.
In step 1 of the Chart Wizard dialog, specify a Column chart and a Clustered Column subtype (the first choice).
Click Next twice, and then in step 2 make additional adjustments: Add a Chart Title (Title tab), dump the Category (x) axis (Axes tab), delete the legend (Legend tab), and specify Show value (Data Labels tab). Click Finish to view the chart.
Double-click the column to display the Format Data Series dialog box.
Click the Options tab, and set the Gap width to 0 (this setting instructs the column to occupy the entire width of the plot area).
To change the pattern used in the column, click the Patterns tab and make your selection. The example shown here uses a gradient fill effect.
Double-click the vertical axis to bring up the Format Axis dialog. In the Scale tab of the Format Axis dialog, set Minimum to 0 and Maximum to 1.
Handle Missing Data In A Line Chart
When you create a line chart in Excel, missing data points (blank cells) won't be plotted, and the line will contain gaps. Excel provides two other ways of handling missing data:
Treat blanks as zeros
Interpolate the data by connecting the line between the nonmissing data points.
The figure below shows both options. In this example, interpolating the missing data seems the better choice in view of the data's time-based nature.
To set how Excel deals with missing data, select your chart and choose Tools, Options. In the Options dialog box, click the Chart tab and then select the appropriate option. Your choice will apply to all data series in the selected chart.
You can also represent data with the formula =NA() instead of leaving a cell blank. The chart will use interpolation for data cells that contain this formula, regardless of the setting in the Options dialog box.
Format Cells To Display In Thousands
In some cases, you may want to display thousands without zeros. For example, you would like 52,000 to appear as 52. Here's how to do it:
First, select the cells to be formatted, then choose Format, Cells. Click the Number tab. Select Custom from the Category list, and in the Type box enter 0, (that's a zero followed by a comma).
When this number format is applied, the cells will retain the correct numerical values, but they will be displayed without the last three digits.
To display values in millions, insert an additional comma at the end of the format string (0,,).
Another possibility is to display one or more decimal places--for example, 52,100 as 52.1. To do this, include a decimal point in your format string (0.0,).
You can also use these number formats in charts. To do so, double-click the chart axis to display the Format Axis dialog box. Then click the Number tab and specify the desired format. In Excel 2000 and later, the Scale tab of the Format Axis dialog box lets you specify the unit scaling directly. Just choose Millions from the "Display units" drop-down box.
Unlink A Chart Series From Its Data Range
Normally, an Excel chart stores data in a range. If you change the data in that range, the chart updates automatically. Sometimes you may want to unlink the chart from its data ranges and produce a static chart that remains unaffected by later changes in the data. For example, suppose you plot data generated by various what-if scenarios, and you want to save a chart that represents some baseline scenario for comparison with others.
One way to create a static chart is to copy and paste it as a picture. Activate your chart, hold down the Shift key, and choose Edit, Copy Picture (this option is available only when you hold down Shift as you select Edit). The Copy Picture dialog box will appear. Click OK to accept the defaults. Then click anywhere in your worksheet and choose Edit, Paste.
Another way to create a static chart is to convert the range references into arrays. Select a chart series and then click the formula bar to activate the SERIES() formula. Press F9 to convert the range references into arrays. Repeat this for each series in the chart. Now the chart cans till be formatted (it doesn't become a picture). The formula bar in the figure below shows the SERIES() formula after converting the range references to arrays.
Saving A Chart As A GIF FIle
If you would like to convert an Excel chart to a GIF file, you can save your worksheet as an HTML file, and Excel will automatically convert any charts to GIF files. If that seems like overkill, you can write a simple macro that will do the job.
Press Alt-F11 to activate the Visual Basic editor. Select your workbook in the Projects window, and choose Insert, Module to insert a new VBA module. Then type the following four-line procedure into the module:
Sub SaveChartAsGIF () Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif" ActiveChart.Export FileName:=Fname, FilterName:="GIF" End Sub
After the macro is entered, reactivate Excel and click the chart to be saved. Press Alt-F8 to display the Macro dialog box. Select the SaveChartAsGIF macro and click Run.
The procedure uses the chart's name as the GIF file name, and the file is stored in the same directory as the workbook. This simple macro does no error checking, so it will generate an error if a chart is not selected or if the workbook has not been saved.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks