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.


Pasting An Image To A UserForm Control

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

VBA programmers are familiar with Excel's UserForms. The UserForm object, as well as many of the ActiveX controls that are placed on a UserForm, can display an image -- the object's Picture property.

How to set the Picture property

If you believe Excel's online help, you'll think that there are two ways to set the Picture property for an object:

  • At design time: Browse for an image file, by clicking the "..." button in the Properties box
  • At run-time: Use VBA's LoadPicture function to retrieve an image file.

But there's another way. And it doesn't even require that the image is stored in a file. Here's the trick:

  1. Locate the image that you want to use, and copy it to the Clipboard.
  2. Activate the VB Editor and select the object that will contain the image (i.e., a UserForm, or a control that has a Picture property).
  3. Make sure the Properties window is displayed (press F4 it it's not).
  4. Click the Picture item. By default, this displays "(None)" as in the figure above.
  5. Press Ctrl+V to paste the image on the Clipboard to the control.

More

Importantly, the picture that you paste to a control need not be stored in a file. Any picture that can be copied to the Clipboard can be pasted to an object's Picture property. And this includes charts stored in a worksheet. The trick here is to copy the chart as a picture: Click the chart, press Shift, and choose Copy Picture from Excel's Edit menu. Once copied, you can paste the picture of the chart to your UserForm control.

When pasting a chart, it's important to understand that the picture is not linked to the original chart. So if the chart changes, the picture will not change.



Interactive Chart With No Macros

Category: Charts & Graphics | [Item URL]

A companion file is available: Click here to download

This tip presents a highly interactive workbook, without a single macro. The workbook allows you to select two U.S. cities, and then displays a comparison chart for the following variables.

  • Temperature
  • Precipitation
  • Percent Sunshine
  • Wind

http://spreadsheetpage.com/graphics/tips/climatedata.gif

You can download the workbook and examine how it's done. Hint: It's done with named ranges and worksheet controls.



Using Custom Number Formats

Category: General | [Item URL]

A companion file is available: Click here to download

One of the most useful (and underutilized) features in Excel is the ability to create custom number formats. Although Excel offers a wide variety of standard number formats, it's often advantageous to develop custom formats. For example, if you use large values, you can scale the display of those values so they appear "in thousands." You can make 123,456,789 appear as 123,457 by applying this format:

  #,##0,

A number format consists of three parts: code for negative, code for zero, and code for positive values. The code for each part is separated with a semicolon. If you would like a cell to appear empty, use this format (which is three semicolons and nothing else):

;;;

To apply a custom number format:

  1. Select the cell or range that you want to format
  2. Choose the Format Cells command (or press Ctrl+1)
  3. Click the Number tab on the Format Cells dialog box
  4. Click the Custom category
  5. Enter the number format code into the edit box labeled Type.

Rather than provide a complete tutorial on custom number formats, I refer you to Excel's help. You'll find a comprehensive reference for the formatting codes.

NOTE: It's important to understand that a number format affects only the way in which the number appears. A number format does not change the underlying value in the cell.


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.



Controlling User Scrolling

Category: General VBA | [Item URL]

An Excel worksheet contains millions of cells. Most of the worksheets that you develop use only a tiny portion of the available area in a worksheet. Novice users sometimes get los" in a worksheet. For example, they may hit PgDn a few times and be faced with an apparently blank worksheet.

Hiding Rows and Columns

You can prevent users from scrolling around unused areas of a worksheet by hiding the unused rows and columns using the Format Column Hide and Format Row Hide commands. For example, if the active area in your worksheet consists of the range A1:G25, you can hide columns H through IV and rows 26 through 16384. The result is an apparently smaller workbook that doesn't display any unused rows or columns.

After hiding unused rows, you may discover a problem: If the user presses the PgDn key when the active cell is near the last unhidden row, the worksheet scrolls up and displays a blank area -- in fact, the entire worksheet may scroll out of view. Obviously, this can cause even more confusion than displaying empty rows.

Trapping the PgDn Key

The solution to the problem described above is to trap the PgDn key. In VBA, you can use Excel's OnKey method to execute a subroutine whenever a particular keystroke occurs. The subroutine below, (which is executed when the workbook is opened), causes a subroutine named DownOne to be executed whenever the user presses the PgDn key.

Sub Auto_Open()
    Application.OnKey "{PgDn}", "DownOne"
End Sub

You'll also need an Auto_Close subroutine to restore the key to normal when the workbook is closed.

Sub Auto_Close()
    Application.OnKey "{PgDn}"
End Sub

The DownOne subroutine, listed below, moves the active cell to the row below -- but only if the row is not hidden. The net effect is that pressing PgDn mimics the down arrow key.

Sub DownOne()
    If Not ActiveCell.Offset(1, 0).EntireRow.Hidden Then _
        ActiveCell.Offset(1, 0).Activate
End Sub

Fine-Tuning

The procedures listed above cause the DownOne subroutine to be executed whenever PgDn is pressed. However, you may want this subroutine to be executed only when the workbook with the hidden rows and columns is activated. In other words, you may want PgDn to operate normally in all workbooks except the one that contains the hidden rows and columns.

The procedures listed below accomplish this. Whenever a sheet is activated, the TrapKey subroutine is executed. The TrapKey subroutine sets up the OnKey event if the active workbook is the workbook that contains the hidden rows and columns; otherwise, the OnKey event is cancelled.

Sub Auto_Open()
    Application.OnSheetActivate = "TrapKey"
End Sub

Sub Auto_Close()
    Application.OnSheetActivate = ""
End Sub

Sub TrapKey()
    If ActiveWorkbook Is ThisWorkbook Then _
        Application.OnKey "{PgDn}", "DownOne" Else _
            Application.OnKey "{PgDn}"

End Sub

Sub DownOne()
    If Not ActiveCell.Offset(1, 0).EntireRow.Hidden Then _
        ActiveCell.Offset(1, 0).Activate
End Sub


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))


Page 3 of 17 pages
[Previous page]   [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 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

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

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