Is A Particular Word Contained In A Text String?
Category: Formulas / General VBA | [Item URL]Here's a VBA function that might be useful in some situations.
The ExactWordInString functions returns True if a specified word is contained in a text string.
You might think that this function is just a variation on Excel's FIND function or VBA's Instr function. There's a subtle difference. The ExactWordInString function looks for a complete word -- not text that might be part of a different word.
The examples in the accompanying figure should clarify how this function works. Cell C2 contains this formula, which was copied to the cells below:
=ExactWordInString(A2,B2)
The function identifies the complete word trapped, but not the word trap, which is part of trapped. Also, note that a space is not required after a word in order to identify it as a word. For example, the word can be followed by a punctuation mark.
The function, listed below, modified the first argument (Text) and replaces all non-alpha characters with a space character. It then adds a leading and trailing space to both arguments. Finally, it uses the Instr function to determine if the modified Word argument is present in the modified Text argument.
To use this function in a formula, just copy and paste it to a VBA module in your workbook.
Function ExactWordInString(Text As String, Word As String) As Boolean
' Returns TRUE if Word is contained in Text as an exact word match
Dim i As Long
Const Space As String = " " Text = UCase(Text)
' Replace non-text characters with a space
For i = 0 To 64
Text = Replace(Text, Chr(i), Space) Next i
For i = 91 To 255
Text = Replace(Text, Chr(i), Space) Next i
' Add initial and final space to Text & Word
Text = Space & Text & Space
Word = UCase(Space & Word & Space)
ExactWordInString = InStr(Text, Word) <> 0 End Function
* Update *
Excel MVP Rick Rothstein sent me a much simpler function that produces the same result. In fact, it
uses just one statement:
Function ExactWordInString(Text As String, Word As String) As Boolean
ExactWordInString = " " & UCase(Text) & " " Like "*[!A-Z]" & UCase(Word) & "[!A-Z]*" End Function
Formulas To Perform Day Of Month Calculations
Category: Formulas | [Item URL]Many events are scheduled for a particular occurrence of the day within a month. For example, payday might be the last Friday of every month. Or, a meeting might be scheduled for every second Monday of the month. Excel doesn't have a function that can calculate these types of dates, but it's possible to create a formula. In the figure below, the formula in cell D4 calculates the date based on the parameters in column C. The formula in D4 is:
=DATE(C3,C4,1+((C6-(C5>=WEEKDAY(DATE(C3,C4,1))))*7)+(C5-WEEKDAY(DATE(C3,C4,1))))
This formula is not always accurate, however. If you specify a day number that doesn't exist (for example, the 6th Friday), it returns a date in the following month.
Cell D6 contains a modified formula that displays "(none)" if the date isn't in the month specified. This formula is much longer:
=IF(MONTH(DATE(C3,C4,1+((C6-(C5>=WEEKDAY(DATE(C3,C4,1))))*7)+ (C5-WEEKDAY(DATE(C3,C4,1)))))<>C4,"(none)",DATE(C3,C4,1+
((C6-(C5>=WEEKDAY(DATE(C3,C4,1))))*7)+(C5-WEEKDAY(DATE(C3,C4,1)))))
In some cases, you might need to determine the last occurrence of a day in a particular month. This
calculation requires a different formula (refer to the figure below):
=DATE(C9,C10+1,1)-1+IF(C11>WEEKDAY(DATE(C9,C10+1,1)-1),
C11-WEEKDAY(DATE(C9,C10+1,1)-1)-7,C11-WEEKDAY(DATE(C9,C10+1,1)-1))
In this figure, the formula in cell D10 displays the date of the last Friday in March, 2008.
The download file for this tip contains another example that has an easy-to-use interface. The user can select the parameters from drop-down lists. The megaformula in the Calculated Date column is very complex because it needs to covert words into values.
Making An Exact Copy Of A Range Of Formulas, Take 2
Category: General / Formulas | [Item URL]When you copy a range of formulas and paste them to a new location, Excel adjusts the cell references automatically. Most of the time, this is exactly what you want. Consider this simple formula:
=SUM(A2:A13)
If you copy this formula and paste it to the next column, the references are adjusted and the pasted formula is:
=SUM(B2:B13)
Making an exact copy of a single formula is easy: Press F2, highlight the formula, and press Ctrl+C to copy it as text. Then paste it to another cell. In some situations, however, you might need to make an exact copy of a range of formulas. In an older tip, I described a rather complicated way to do this. See Making An Exact Copy Of A Range Of Formulas.
Matthew D. Healy saw that tip and shared another method, which uses Notepad. Here's how it works:
1. Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a
"backwards apostrophe," and is usually on the same key that has the ~ (tilde).
2. Select the range to copy.
3. Press Ctrl+C
4. Start Windows Notepad
5. Press Ctrl+V to past the copied data into Notepad
6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make
sure that the sheet you are copying to is in formula view mode.
8. Press Ctrl+V to paste.
9. Press Ctrl+` to toggle out of formula view mode.
Note: If the paste operation back to Excel doesn't work correctly, chances are that you've used Excel's
Text-to-Columns feature recently, and Excel is trying to be helpful by remembering how you last parsed
your data. You need to fire up the Convert Text to Columns Wizard. Choose the Delimited option and
click Next. Clear all of the Delimiter option checkmarks except Tab.
Calculating Easter
Category: Formulas | [Item URL]Easter is one of the most difficult holidays to calculate. Several years ago, a Web site had a contest to
see who could come up with the best formula to calculate the date of Easter for any year. Here's one of
the formulas submitted (it assumes that cell A1 contains a year):
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6
Just for fun, I calculated the date of Easter for 300 years from 1900 through 2199. Then I created a pivot
table, and grouped the dates by day. And then, a pivot chart:
During this 300-year period, the most common date for Easter is March 31 (it occurs 13 times on that data). The least common is March 24 (only one occurrence). I also learned that the next time Easter falls on April Fool's Day will be in 2018.
Converting Unix Timestamps
Category: Formulas | [Item URL]If you import data you might encounter time values stored as Unix timestamps. Unix time is defined as the number of seconds since midnight (GMT time) on January 1, 1970 -- also known as the Unix epoch. For example, here's the Unix timestamp for August 4, 2008 at 10:19:08 pm (GMT):
1217888348
To create an Excel formula to convert a Unix timestamp to a readable data and time, start by converting the seconds to days. This formula assumes that the Unix timestamp is in cell A1:
=(((A1/60)/60)/24)
Then, you need to add the result to the date value for January 1, 1970. The modified formula is:
=(((A1/60)/60)/24)+DATE(1970,1,1)
Finally, you need to adjust the formula for the GMT offset. For example, if you're in New York the GMT offset is -5. Therefore, the final formula is:
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)
A simpler (but much less clear) formula that returns the same result is:
=(A1/86400)+25569+(-5/24)
Both of these formulas return a date/time serial number, so you need to apply a number format to make it readable as a date and time.
Naming Techniques
Most Excel users know how to name cells and ranges. Using named cells and ranges can make your formulas more readable, and less prone to errors. Most users, however, don't realize that Excel lets you provide names for other types of items. This document describes some useful naming techniques that you may not be aware of.
Naming a constant
If formulas in your worksheet use a constant value (such as an interest rate), the common procedure is to insert the value for the constant into a cell. Then, if you give a name to the cell (such as InterestRate), you can use the name in your formulas. Here's how create a named constant that doesn't appear in a cell:
1.
Select the Insert Name Define command to display the Define Name dialog box. 2. Enter the name (such as InterestRate) in the field labeled Names in workbook.3. Enter the value for the name in the Refers to field (this field normally holds a formula). For example, you can enter =.075.
Try it out by entering the name into a cell (preceded by an equal sign). For example, if you defined a name called InterestRate, enter the following into a cell:
=InterestRate
This formula will return the constant value that you defined for the InterestRate name. And this value does not appear in any cell.
Names are actually named formulas
Here's another way of looking at names. Whenever you create a name, Excel actually creates a name for a formula. For example, if you give a name (such as Amount) to cell D4, Excel creates a name for this formula:
=$D$4
You can use the Define Name dialog box and edit the formula for a name. And you can use all of the standard operators and worksheet functions. Try this:
1. Create a name for cell D4. Call it Amount.
2. Enter =Amount into any cell. The cell will display the value in cell D4.
3. Use the Insert Name Define command and edit the refers to field so it appears as =$D$4*2 You'll find that entering =Amount now displays the value in cell D4 multiplied by 2.
Using relative references
When you create a name for a cell or range, Excel always uses absolute cell references for the range. For example, if you give the name Months to range A1:A12, Excel associates $A$1:$A$12 (an absolute reference) with the name Months. You can override the absolute references for a name and enter relative references. To see how this works, follow the steps below to create a relative name called CellBelow.
1. Select cell A1.
2.
Select the Insert Name Define command to display the Define Name dialog box. 3. Enter the name CellBelow in the field labeled Names in workbook.4. Replace the value in the Refers to field with =A2 (this is a relative reference) 5. Click OK
Try it out by entering the following formula into any cell:
=CellBelow
You'll find that this formula always returns the contents of the cell directly below.
NOTE: It's important to understand that the formula you enter in Step 4 above depends on the active cell. Since cell A1 was the active cell, =A2 is the formula that returns the cell below. If, for example, cell C6 was the active cell when you created the name, you would enter =C7 in step 4.
Using mixed references
You can also used "mixed" references for you names. Here's a practical example of how to create a name that uses mixed references. This name, SumAbove, is a formula that returns the sum of all values above the cell.
1. Activate cell A3.
3. In the Names in workbook field, enter SumAbove. 4. In the Refers to field, enter =SUM(A$1:A2)
Notice that the formula in Step 3 is a mixed reference (the row part is absolute, but the column part is relative). Try it out by entering =SumAbove into any cell. You'll find that this formula returns the sum of all cells in the column from Row 1 to the row directly above the cell.
Creating A List Of Formulas
Most users have discovered that Excel has an option that lets you display formulas directly in their cells: Choose Tools Options, click the View tab, and select the Formulas checkbox. However, Excel doesn't provide a way to generate a concise list of all formulas in a worksheet. The VBA macro below inserts a new worksheet, then creates a list of all formulas and their current values.
NOTE: My Power Utility Pak add-in includes a more sophisticated version of this subroutine, plus several other auditing tools.
To use this subroutine:
1. Copy the code below to a VBA module. You can also store it in your Personal Macro Workbook, or create an add-in.
2. Activate the worksheet that contains the formulas you want to list.
3. Execute the ListFormulas subroutine. The subroutine will insert a new worksheet that contains a list of the formulas and their values.
The ListFormulas Subroutine
Sub ListFormulas()
Dim FormulaCells As Range, Cell As Range Dim FormulaSheet As Worksheet
Dim Row As Integer
' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
' Exit if no formulas are found
If FormulaCells Is Nothing Then MsgBox "No Formulas." Exit Sub
End If
' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name
' Set up the column headings
Range("A1") = "Address" Range("B1") = "Formula" Range("C1") = "Value" Range("A1:C1").Font.Bold = True End With
' Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%") With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False) Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value Row = Row + 1
End With Next Cell
' Adjust column widths
FormulaSheet.Columns("A:C").AutoFit Application.StatusBar = False End Sub
Cell Counting Techniques
Excel provides many ways to count cells in a range that meet various criteria:
• The DCOUNT function. The data must be set up in a table, and a separate criterion range is required.
• The COUNT function. Simply counts the number of cells in a range that contain a number.
• The COUNTA function. Counts the number of non-empty cells in a range.
• The COUNTBLANK function. Counts the number of empty cells in a range.
• The COUNTIF function. Very flexible, but often not quite flexible enough.
• An array formula. Useful when the other techniques won't work.
Formula Examples
Listed below are some formula examples that demonstrate various counting techniques. These formula
all use a range named data.
To count the number of cells that contain a negative number:
=COUNTIF(data,"<0")To count the number of cells that contain the word "yes" (not case sensitive):
=COUNTIF(data,"yes")To count the number of cells that contain any text:
=COUNTIF(data,"*")To count the number of cells that contain text that begins with the letter "s" (not case-sensitive):
=COUNTIF(data,"s*")To count the number of cells that contain the letter "s" (not case-sensitive):
=COUNTIF(data,"*s*")To count the number of cells that contain either "yes" or "no" (not case-sensitive):
=COUNTIF(data,"yes")+COUNTIF(data,"no")To count the number of three-letter words:
=COUNTIF(data,"???")To count the number of cells that contain a value between 1 and 10:
=COUNTIF(data,">=1")-COUNTIF(data,">10")To count the number of unique numeric values (ignores text entries):
=SUM(IF(FREQUENCY(data,data)>0,1,0))To count the number of cells that contain an error value (this is an array formula, entered with
Ctrl+Shift+Enter):
=SUM(IF(ISERR(data),1,0))
Using the formulas in VBA
You can also use these techniques in your VBA code. For example the VBA statement below calculates the number of three-letter words in a range named data, and assigns the value to the NumWords variable:
NumWords = Application.COUNTIF(Sheets("Sheet1").Range("data"), "???")
The other formula examples listed above can also be converted to VBA.
Summing And Counting Using Multiple Criteria
If you peruse the Excel newsgroups, you've probably realized that one of the most common questions involves summing or counting using multiple criteria. If your data is set up as a database table you can use database functions such as DCOUNT or DSUM. These functions, however, require the use of a separate criteria range on your worksheet.
This tip provides a number of examples that should solve most of your counting and summing problems. Unlike DCOUNT and DSUM, these formulas don't require a criteria range.
The example formulas presented in this tip use the simple database table shown below. You will need to adjust the formulas to account for your own data.
Sum of Sales, where Month="Jan"
This is a straightforward use of the SUMIF function (it uses a single criterion):
=SUMIF(A2:A10,"Jan",C2:C10)
Count of Sales, where Month="Jan"
This is a straightforward use of the COUNTIF function (single criterion):
=COUNTIF(A2:A10,"Jan")
Sum of Sales, where Month<>"Jan"
Another simple use of SUMIF (single criterion):
=SUMIF(A2:A10,"<>Jan",C2:C10)
Sum of Sales where Month="Jan" or "Feb"
For multiple OR criteria in the same field, use multiple SUMIF functions:
=SUMIF(A2:A10,"Jan",C2:C10)+SUMIF(A2:A10,"Feb",C2:C10)
Sum of Sales where Month="Jan" AND Region="North"
For multiple criteria in different fields, the SUMIF function doesn't work. However, you can use an
array formula. When you enter this formula, use Ctrl+Shift+Enter:
=SUM((A2:A10="Jan")*(B2:B10="North")*C2:C10)
Sum of Sales where Month="Jan" AND Region<>"North"
Requires an array formula similar to the previous formula. When you enter this formula, use
Ctrl+Shift+Enter:
=SUM((A2:A10="Jan")*(B2:B10<>"North")*C2:C10)
Count of Sales where Month="Jan" AND Region="North"
For multiple criteria in different fields, the COUNTIF function doesn't work. However, you can use an
array formula. When you enter this formula, use Ctrl+Shift+Enter:
Sum of Sales where Month="Jan" AND Sales>= 200
Requires an array formula similar to the previous example. When you enter this formula, use
Ctrl+Shift+Enter:
=SUM((A2:A10="Jan")*(C2:C10>=200)*(C2:C10))
Sum of Sales between 300 and 400
This also requires an array formula. When you enter this formula, use Ctrl+Shift+Enter:
=SUM((C2:C10>=300)*(C2:C10<=400)*(C2:C10))
Count of Sales between 300 and 400
This also requires an array formula. When you enter this formula, use Ctrl+Shift+Enter:
=SUM((C2:C10>=300)*(C2:C10<=400))
Chart Trendline Formulas
When you add a trendline to a chart, Excel provides an option to display the trendline equation in the
chart. This tip describes how to create formulas that generate the trendline coefficients. You can then use
these formulas to calculate predicted y values for give values of x.
These equations assume that your sheet has two named ranges: x and y.
Linear Trendline
Equation: y = m * x + b m: =SLOPE(y,x) b: =INTERCEPT(y,x)Logarithmic Trendline
Equation: y = (c * LN(x)) + b c: =INDEX(LINEST(y,LN(x)),1) b: =INDEX(LINEST(y,LN(x)),1,2)Power Trendline
Equation: y=c*x^b c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2)) b: =INDEX(LINEST(LN(y),LN(x),,),1)Exponential Trendline
Equation: y = c *e ^(b * x) c: =EXP(INDEX(LINEST(LN(y),x),1,2)) b: =INDEX(LINEST(LN(y),x),1)2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b c2: =INDEX(LINEST(y,x^{1,2}),1)
b = =INDEX(LINEST(y,x^{1,2}),1,3)
3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2) C1: =INDEX(LINEST(y,x^{1,2,3}),1,3) b: =INDEX(LINEST(y,x^{1,2,3}),1,4)
Higher Order Polynomial Trendline
Notice the pattern in the two preceding sets of formulas.
Making An Exact Copy Of A Range Of Formulas
Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you
want to make an exact copy of these formulas, beginning in cell A11 on Sheet1. By "exact," I mean a
perfect replica -- the original cell references should not change.
If the formulas contain only absolute cell references, it's a piece of cake. Just use the standard copy/paste
commands. But if the formulas contain relative or mixed references, the standard copy/paste technique
won't work because the relative and mixed references will be adjusted when the range is pasted.
If you're a VBA programmer, you can simply execute the following code:
With Sheets("Sheet1")
.Range("A11:D20").Formula = .Range("A1:D10").Formula End With
Following are step-by-step instructions to accomplish this task without using VBA (contributed by Bob
Umlas):
1. Select the source range (A1:D10 in this example).
2. Group the source sheet with another empty sheet (say Sheet2). To do this, press Ctrl while you
click the sheet tab for Sheet2
3. Select Edit - Fill - Across worksheets (choose the All option in the dialog box).
4. Ungroup the sheets (click the sheet tab for Sheet2)
5. In Sheet2, the copied range will be selected. Choose Edit - Cut.
6. Activate cell A11 (in Sheet2) and press Enter to paste the cut cells. A11.D20 will be selected.
7. Re-group the sheets. Press Ctl and click the sheet tab for Sheet1
8. Once again, use Edit - Fill - Across worksheets.
9. Activate Sheet1, and you'll find that A11:D20 contains an exact replica of the formulas in
A1:D10.
Note: For another method of performing this task, see
Making An Exact Copy Of A Range Of
Formulas, Take 2
.
Comparing Two Lists With Conditional Formatting
Category: Formatting / Formulas | [Item URL]Excel's Conditional Formatting feature has many uses. Suppose you need to compare two lists, and
identify the items that are different. The figure below shows an example. These lists happen to contain
text, but this technique also works with numeric data.
The first list is in A2:B19, and this range is named OldList. The second list is in D2:E19, and the range
is named NewList. The ranges were named using the Insert - Name - Define command. Naming the
ranges is not necessary, but it makes them easier to work with.
As you can see, items in OldList that do not appear in NewList are highlighted with a yellow
background. Items in NewList that do not appear in OldList are highlighted with a green background.
These colors are the result of Conditional Formatting.
How to do it
1. Start by selecting the OldList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:
=COUNTIF(NewList,A2)=0
5. Click the Format button and specify the formatting to apply when the condition is true (a yellow
background in this example).
The cells in the NewList range will use a similar conditional formatting formula.
1. Select the NewList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:
=COUNTIF(OldList,D2)=0
5. Click the Format button and specify the formatting to apply when the condition is true (a green
background in this example).
6. Click OK
Both of these conditional formatting formulas use the COUNTIF function. This function counts the
number of times a particular value appears in a range. If the formula returns 0, it means that the item
does not appear in the range. Therefore, the conditional formatting kicks in and the cell's background
color is changed.
The cell reference in the COUNTIF function should always be the upper left cell of the selected range.
Locate Phantom Links In A Workbook
Category: Formulas | [Item URL]
Q. Whenever I open a particular Excel workbook, I get a message asking if I want to update the
links. I've examined every formula in the workbook, and I am absolutely certain that the
workbook contains no links to any other file. What can I do to convince Excel that the workbook
has no links?
You've encountered the infamous "phantom link" phenomenon. I've never known Excel to be wrong
about identifying links, so there's an excellent chance that your workbook does contain one or more
links -- but they are probably not formula links.
Follow these steps to identify and eradicate any links in a workbook.
1.
Select Edit, Links. In many cases, this command may not be available. If it is available, the Links dialog box will tell you the name of the source file for the link. Click the Change Source button and change the link so it refers to the active file.2.
Select Insert, Name, Define. Scroll through the list of names in the Define Name dialog box and examine the Refers to box (see the figure below). If a name refers to another workbook or contains an erroneous reference such as #REF!, delete the name. This is, by far, the most common cause of phantom links 3. If you have a chart in your workbook, click on each data series in the chart and examine the SERIES formula displayed in the formula bar. If the SERIES formula refers to another workbook, you've identifiedyour link. To eliminate the link move or copy the chart's data into the current workbook and recreate your chart.
4. If your workbook contains any custom dialog sheets, select each object in each dialog sheet and examine the formula bar. If any object contains a reference to another workbook, edit or delete the reference.
Next, save your workbook and then re-open it. It should open up without asking you to update the links.
Dealing With Negative Time Values
Category: Formulas | [Item URL]Because Excel stores dates and times as numeric values, it's possible to add or subtract one from the
other.
However, if you have a workbook containing only times (no dates), you may have discovered that
subtracting one time from another doesn't always work. Negative time values appear as a series of hash
marks (########), even though you've assigned the [h]:mm format to the cells.
By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a
date/time combination that falls before this date, which is invalid.
The solution is to use the optional 1904 date system. Select Tools, Options, click the Calculation tab,
and check the 1904 date system box to change the starting date to January 2, 1904. Your negative times
will now be displayed correctly, as shown below.
Be careful if you workbook contains links to other files that don't use the 1904 date system. In such a
case, the mismatch of date systems could cause erroneous results.
Converting Non-numbers To Actual Values
Category: Formulas | [Item URL]Q. I often import data into Excel from various applications, including Access. I've found that
values are sometimes imported as text, which means I can't use them in calculations or with
commands that require values. I've tried formatting the cells as values, with no success. The only
way I've found to convert the text into values is to edit the cell and then press Enter. Is there an
easier way to make these conversions?
This is a common problem in Excel. The good news is the Excel 2002 is able to identify such cells and you can easily correct them If you're using an older version of Excel, you can use this method:
1. Select any empty cell
2. Enter the value 1 into that cell
3.
Choose Edit, Copy4. Select all the cells that need to be converted
5.
Choose Edit, Paste Special6.
In the Paste Special dialog box, select the Multiply option, then click OK.This operation multiplies each cell by 1, and in the process converts the cell's contents to a value.
Compare Ranges By Using An Array Formula
In Excel, you can compare the cells in two ranges with an array formula. For instance, to see if all of the values in A1:A100 are identical to those in B1:B100, type this array formula:
=SUM(IF(A1:A100=B1:B100,0,1))
Note: This is an array formula and it must be entered using Ctrl-Shift-Enter.
The formula will return the number of corresponding cells that are different. If the formula returns 0, it means that the two ranges are identical.
Calculate The Number Of Days In A Month
Excel lacks a function for calculating the number of days in a particular month, so you'll need to construct your own formula.
If cell A1 contains a date, this formula will return the number of days in the month:
Identify Formulas By Using Conditional Formatting
How many times have you accidentally deleted or overwritten cells containing formulas only to discover the mistake after it's too late? One solution is to write-protect important cells. Another approach is to give those cells a visual flag.
This clever technique was submitted by David Hager. It uses Conditional Formatting (available in Excel 97 or later) to apply special formatting to cells that contain formulas--something that's not normally possible. With this technique you can set up your worksheet so that all formula cells get a yellow background, for example, or so that negative values are in boldface.
Follow these steps:
1.
Select Insert, Name, Define.2. In the Define Name dialog box, enter the following in the 'Names in workbook' box CellHasFormula
3. Then enter the following formula in the "Refers to" box =GET.CELL(48,INDIRECT("rc",FALSE))
4.
Click Add, and then OK.5. Select all the cells to which you want to apply the conditional formatting.
6.
Select Format, Conditional Formatting7.
In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and then enter this formula in the adjacent box (see the figure below):=CellHasFormula
8.
Click the Format button and select the type of formatting you want for the cells that contain a formula. 9. Click OK.After you've completed these steps, every cell that contains a formula and is within the range you selected in Step 4 will display the formatting of your choice.
How does it work? The key component is creating a named formula in Steps 2 and 3. This formula,
unlike standard formulas, doesn't reside in a cell, but it still acts like a formula by returning a value -- in
this case either 'True' or 'False'. The formula uses the GET.CELL function, which is part of the XLM
macro language (VBA's predecessor) and cannot be used directly in a worksheet. Using a value of 48 as
the first argument for GET.CELL causes the function to return 'True' if the cell contains a formula. The
INDIRECT function essentially creates a reference to each cell in the selected range.
Displaying Autofilter Criteria
Excel's AutoFilter feature definitely ranks right up there when it comes to handy tools. This feature, which you access with the Data, Filter, AutoFilter command, works with a range of cells set up as a database or list. When AutoFiltering is turned on, the row headers display drop-down arrows that let you specify criteria (such as "Age greater than 30"). Rows that don't match your criteria are hidden, but they are redisplayed when you turn off AutoFiltering.
One problem with AutoFiltering is that you can't tell which criteria are in effect. Stephen Bullen developed a custom VBA worksheet function that displays the current AutoFilter criteria in a cell. The instructions that follow are for Excel 97 or later.
Press Alt+F11 and insert a new module for the active workbook. Then enter the VBA code for the FilterCriteria shown below.
Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen
Dim Filter As String Filter = ""
On Error GoTo Finish With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish Filter = .Criteria1
Select Case .Operator Case xlAnd
Filter = Filter & " AND " & .Criteria2 Case xlOr
Filter = Filter & " OR " & .Criteria2 End Select End With End With Finish: FilterCriteria = Filter End Function
After you've entered the VBA code, you can use the function in your formulas. The single-cell argument
for the FilterCriteria function can refer to any cell within the column of interest. The formula will return
the current AutoFilter criteria (if any) for the specified column. When you turn AutoFiltering off, the
formulas don't display anything.
The figure below shows the FilterCriteria in action. The function is used in the cells in row 1. For
example, cell A1 contains this formula:
=FilterCriteria(A3)
As you can see, the list is currently filtered to show rows in which column A contains January, column
C contains a code of A or B, and column D contains a value greater than 125 (column B is not filtered,
so the formula in cell B1 displays nothing). The rows that don't match these criteria are hidden.
Calculating A Conditional Average
Category: Formulas | [Item URL]In the real world, a simple average often isn't adequate for your needs.
For example, an instructor might calculate student grades by averaging a series of test scores but
omitting the two lowest scores. Or you might want to compute an average that ignores both the highest
and lowest values.
In cases such as these, the AVERAGE function won't do, so you must create a more complex formula.
The following Excel formula computes the average of the values contained in a range named "scores,"
but excludes the highest and lowest values:
=(SUM(scores)-MIN(scores)-MAX(scores))/(COUNT(scores)-2)
Here's an example that calculates an average excluding the two lowest scores:
=(SUM(scores)-MIN(scores)-SMALL(scores,2))/(COUNT(scores)-2)
Display Text And A Value In One Cell
Did you know that you could combine text and values in a single cell?For example, assume cell A12 contains the value 1435. Enter the following formula into another cell: ="Total: "&A12
The formula cell will display: "Total: 1435."
The ampersand is a concatenation operator that joins the text with the contents of cell A12.
Applying a number format to the cell containing the formula has no effect, because the cell contains text, not a value. As a work-around, modify the formula to use the TEXT function (the second argument for the TEXT function consists of a standard Excel number-format string).
="Total: "&TEXT(A12,"$#,##0.00") This formula will display "Total: $1,435.00."
Here's another example formula that uses the NOW function to display some text along with the current date and time:
Automatic List Numbering
It's fairly easy to create a formula that generates consecutively number items in nonconsecutive cells.
Refer to the figure below.
Column A consists of formulas that refer to column B. The formula in cell A1 is: =IF(B1<>"",COUNTA($B$1:B1)&".","")
This formula, which is copied down to the other cells in column A, displays the next consecutive item number if the corresponding cell in column B is not empty. If the cell in column B is empty, the formula displays nothing. As items are added or deleted from column B, the numbering updates automatically.
Calculate The Day Of The Year And Days Remaining
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:
Rounding To “n” Significant Digits
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))))
Working With Pre-1900 Dates
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.
Using Data Validation To Check For Repeated Values
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.
Sum The Largest Values In A Range
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.
Count Autofiltered Rows
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.
Perform Two-Way Table Lookups
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:
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!
Referencing A Sheet Indirectly
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.
Delete All Input Cells, But Keep The Formulas
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.
Round Values To The Nearest Fraction
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.
Avoid Error Displays In Formulas
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)
Change Cell Values Using Paste Special
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.
Hiding Your Formulas
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.
Counting Distinct Entries In A Range
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)))
Force A Global Recalculation
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.
Summing Times That Exceed 24 Hours
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.
Transforming Data With Formulas
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.
Creating A “Megaformula”
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)))
=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.
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.
=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.
• Note:
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:
=VLOOKUP(A1,B1:C10,2)
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.
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.
A Formula To Calculate A Ratio
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 a text string, not a fractional value. For example, the
ratio of 1:8 is not the same as 1/8.
Multiple Condition Tests
One of the most basic functions in any spreadsheet is to return an answer based upon some condition. This becomes especially useful when counting or summing based upon that condition. One condition is useful, and is easily achieved using COUNTIF or SUMIF. These are incredibly useful and flexible functions, but limited as they are to single conditions, they can be lacking.
Multiple conditions, such as counting the number of items sold by part number AND by month, greatly extends the functionality of our solution. There are a number of ways that this can be achieved within Excel, but this paper is focusing on one particular function, the SUMPRODUCT
function, which by creative use has evolved to a flexibility undreamt of by its originators in
Microsoft. Because this usage has been driven outside of Microsoft, by real-world Excel users, you will not see it documented within Excel help, or in MSDN.
SUMPRODUCT is one of the most versatile functions provided in Excel. In its most basic form,
SUMPRODUCT multiplies corresponding members in given arrays, and returns the sum of those products. This page discusses the classic use of SUMPRODUCT, how creativity and inbuilt flexibility has enabled it to evolve into a far more useful function, and explains some of the techniques being deployed.
This article comes in two parts. This first part discusses SUMPRODUCT, how it has evolved, how it works, whilst Part 2 provides a number of real world problems and the solutions,
Standard Use of SUMPRODUCT
In it's classic form, SUMPRODUCT multiplies each value in one array by the corresponding value in another array, and returns the summed result. As an example, if cells A9:A11 contain the values 1,2,3 and B9:B11 contain 10,20,30, then
=SUMPRODUCT(A9:A11,B9:B11)
returns 140, or (1*10)+(2*20)+(3*30)=10+40+90=140.
This is a useful function, but nothing more than that. A further, more 'creative' use of
SUMPRODUCT has evolved, and is still evolving, driven as far as I can see mainly by the regular contributors of the Microsoft Excel newsgroups. This has been a creative and productive process
that has significantly increased the useability of SUMPRODUCT, but in a way that you will not find documented in Excel's Help.
Evolving Use of SUMPRODUCT
Within Excel, there are two very useful functions that support conditional counting and summing, namely COUNTIF and SUMIF. Very useful functions, but limited in that they can only evaluate a single test range.
In certain instances, a very simple double conditional test between two values can be emulated by testing for the lower condition and subtracting anything that is beyond the upper condition. For instance, the formula =COUNTIF(A1:A10,>=10) COUNTIF(A1:A10,>20) calculates how many items in A1:A10 that fall between 10 and 20, by counting all items greater than 10, which also includes those items greater than 20, and then subtracting the count of those items in A1:A10 that are greater than 20. Whilst this emulates a double conditional test, it is very limited, it cannot work on different ranges, or more conditions.
Multiple conditions are so useful to test ranges (say between two dates), and double tests (one array = A and another = B), and whilst this can be managed using array functions
=SUM(IF(test_A,IF(test_B, etc.,
this is somewhat unwieldy, and is an array formula. And there is a better way, using
SUMPRODUCT.
Note that in this section, all formulae given are using the '*' (multiply) operator format, but this in itself is one of the biggest discussion points around the SUMPRODUCT function, one which is discussed below.
To understand how SUMPRODUCT can be used, first consider the following data.
A B C
1 Make Month Price
2 Ford June 7,500 3 Ford June 8,300 4 Ford May 6,873 5 Ford June 11,200 6 Renault June 13,200 7 Renault June 14,999 8 BMW June 17,500 9 BMW May 23,500 10 BMW June 18,000 Table 1.
We can easily count the number of Fords with
=COUNTIF(A1:A10,"Ford")
which returns 4.
Similalrly, it is straight-forward to get the value of Fords sold, using
=SUMIF(A1:A10,"Ford",C1:C10)
