Working With Circular references in Excel

Posted on by jkpieterse

Have you ever experienced the dreaded “Circular reference warning” popping up when you opened an Excel file or entered a formula?

Excel detects a circular reference when a chain of calculations visits the same cell more than once. Many users get (very) confused by this message and have no idea what it is about. I’ll tried to demystify that message in a new article:

Working with Circular references in Excel

The article contains these chapters:

Types of circular references

Simplistically speaking there are only a few types of circular references to discern:

Deliberate circular references

Accidental circular references

Calculation settings

If you want to work with circular references, the calculation settings of Excel are very important. This page gives you some pointers!

Properly setting up circular references

Whereas I am no fan of using circular references, they can be beneficial to your model and really solve the problem you are trying to solve. So here is some advice on how to properly work with them.

Reasons why circular references may not be detected

This page shows a couple of reasons why circles are not detected.

Enjoy!

Regards,

Jan Karel Pieterse

www.jkp-ads.com

Charts and Things

Posted on by Dick Kusleika

A list of people who will enjoy chartsnthings, “A blog of data sketches from the New York Times Graphics Department. Maintained by @KevinQ.”

  • People who say “data visualization” instead of “chart”
  • People who can find fault in any chart that they didn’t make
  • People who get lost in the minutia of charting psychology
  • Me
  • Maybe you

Quick VBA Tip: Parentheses

Posted on by Dick Kusleika

I’d like to make an assertion. If you have an open parenthesis preceded by a space, you should remove the parentheses. Here’s an example:

Sub test()

Dim myCollection As Collection
Dim myObject As Object

myCollection.Add (myObject)
myCollection.Add myObject

End Sub

When you put parentheses around an object, VBA evaluates that object and, absent a property, returns the default property. The first Add might error or might not work as expected. Here’s another example:

Sub test2()

Dim dValue As Double

dValue = 1.1

Debug.Print (dValue)
Debug.Print dValue

End Sub

In this case the parentheses don’t do any harm, but they’re still unnecessary. And finally:

Sub test3()

Dim lValue As Long

lValue = CLng(1.1)

End Sub

In this case there is no space before the open parenthesis, so it’s not just preferred, but necessary.

Can you think of a case where an open parenthesis preceded by a space is required or preferred?

Excel Power Analyst Bootcamp Almost Here

Posted on by Dick Kusleika

Excel Power Analyst Bootcamp Omaha

It’s almost here. Sign-up today to get the early-bird discount. Seats are limited.

Align Primary and Secondary axes

Posted on by Tushar Mehta

There are instances when there are data series plotted on both the primary and secondary axes. For example, suppose we want to plot the two series A and B in Figure 1, with the elements in column B as the x-axis values. The A series will be a column cart on the primary axis and the B series will be a line chart on the secondary axis.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/data_visualization/06%20Visual%20Effects.shtml

Tushar Mehta

Incrementing Dates in Excel Cells

Posted on by Dick Kusleika

I complete a timesheet every 14 days. I got tired of doing math in my head, so around August 13, 2010 I put a stop to it. Here’s what the date cell on my timesheet looks like now.

 

F2 to edit the cell, “+14” and enter. It’s nowhere near too long as formulas go, but it’s starting to bother me. Time to consolidate. Select the 14s.

 

 

Press Control+= (F9 works too, but my muscle memory is control and equal sign)

 

 

Enter. Next pay period, my timesheet will look like this

 

SQLite and Dropbox

Posted on by Dick Kusleika

I want to put a SQLite3 file in a shared Dropbox folder and run an Excel app with that as the backend database. I’m using the SQLite ODBC Driver and some VBA to drive a simple userform.

My theory is that the file access will be so short and infrequent that I won’t have any file locking problems.

Who wants to test it out? You need a Dropbox account, the aforementioned driver, and a copy of the workbook below. Oh, and Excel if you didn’t already figure that one out.

Install the driver. Leave a comment or send an email to dkusleika@gmail.com with your Dropbox info and I’ll share a folder with you. Then open the workbook and start adding and modifying records in the userform. Ideally, I’d like to coordinate a five minute period where a few of us agree to hammer on it and try to break it.

You can download SqliteContacts.zip