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]

A companion file is available: Click here to download

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

Category: Formulas | [Item URL]

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.
  4. Click OK

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.
  2. Select the Insert Name Define command to display the Define Name dialog box.
  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

Category: Formulas / General VBA | [Item URL]

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
    With FormulaSheet
        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

Category: Formulas | [Item URL]

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

Category: Formulas | [Item URL]

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.

http://spreadsheetpage.com/graphics/tips/table.gif (5737 bytes)

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((A2:A10="Jan")*(B2:B10="North"))

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

Category: Formulas / Charts & Graphics | [Item URL]

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)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
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.


Page 1 of 4 pages
[Next page]

Search for Tips


All Tips

Browse Tips by Category

Tip Books

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

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy