MacLochlainns Weblog

I fielded a question from blog reader on a three year old post about the EOMONTH (End Of Month) function in Excel. The reader want to write a mega-function (a term for putting two or more functions together to solve a problem, according to Mr. Excel). The function would return the first day of any month for the first day of the month and the first day of the next month for any day after the first day of the last month.

I wrote a quick little solution for the user, as shown below:

=IF(A1=EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+1,EOMONTH(A1,0)+1)

The results would look like:

The user ran into a problem with the example because the date was being calculated and not an integer value. The formula returned the first day of the next month when it should have returned the first day of the current month. That meant the source value in the cell wasn’t an integer. It was a real number. Integer values in Excel are numeric values for 12:00:00 A.M. of any day, and real numbers can be any time of the day.

Unfortunately, the EOMONTH function is a pseudo-overloaded function, and the EOMONTH function takes an int or double (integer or real number) as it’s parameter. That’s because Excel only supports a NUMBER data type, which can be an integer or real number.

The EOMONTH function always returns an integer. The IF function I provided was designed to compare an integer-based date cell value against the result of the EOMONTH function. It wasn’t robust enough to support a comparison of 11/1/13 at 12:01 A.M. against the result of the EOMONTH function (11/1/13 at 12:00 A.M.). That’s why it returned the first of the next month instead of the first of the current month.

The more complete solution requires using the TRUNC function around the source date (B4 or C4 in the following example) to ensure the IF statement compares dates not time-stamps.

=IF(TRUNC(B4)=EOMONTH(B4,-1)+1,EOMONTH(B4,-1)+1,EOMONTH(B4,0)+1)

You should note that the TRUNC function effectively rounds down to the integer and removes the fractional portion of the real number before making a comparison. It’s an explicit casting process when we take proactive measures to ensure the value before the IF function compares it.

I’ve worked with every release of Microsoft Excel, and I know it takes effort to keep up to date with certain releases. Clearly, the Data Analysis eXpression (DAX) Language introduced in Excel 2010 went unnoticed by many, which was sad. DAX is truly a powerful extension to the analytical and modeling approaches in Microsoft Excel.

I’d like to recommend Microsoft Excel 2013 Building Data Models with PowerPivot to those who haven’t learned how to use DAX in Excel 2010, 2011, or 2013. DAX works with tables but if you don’t use tables, I guess you can skip DAX because you must have infinite time to produce marginal analytical outcomes (tongue in cheek humor). However, if you’re like most folks, you want a book to get you up-to-speed quickly, and that’s what this book will do for you.

Just one caveat if you’re using an Oracle or MySQL database, use the prepackaged analytic functions before you download the data set. You should always pre-select data before applying analytics in Excel. Remember the more refined the data model you start with the easier it is to structure analytical tools to leverage the data model. While DAX is powerful, it doesn’t replace the speed and query optimized behaviors of effective Oracle or MySQL queries.

Somebody posted a comment on my Excel UDF Tutorial, and on reflection it appears he’d like to navigate a range in VBA. While I don’t see a purpose in that because it duplicates either the VLOOKUP or HLOOKUP function (depending on whether you’re navigating a column or row).

Anyway, here’s how you’d do it in VBA:

Function Finding(lookupWord As String, lookupRange As Range, lookupColumn As Integer) As Integer
 
  ' Define variables.
  Dim lookupIndex As Integer
  Dim lookupMax As Integer
  Dim returnValue As Integer
 
  ' Assign values to variables.
  lookupIndex = 1
  lookupMax = lookupRange.Rows.Count
  returnValue = 1
 
  ' Print starting and ending index values.
  MsgBox ("[" + CStr(lookupIndex) + "][" + CStr(lookupMax) + "]")
 
  ' Loop until found, but exit when not found.
  Do Until lookupRange.Cells(lookupIndex, 1).Value = lookupWord
    ' Print diagnostic value to determine what's evaluated.
    MsgBox ("[" + CStr(lookupIndex) + "] [" + CStr(lookupRange.Cells(lookupIndex, 1).Value) + "]")
 
    ' Fail safe exit when no matching value is found.
    If lookupIndex = (lookupMax + 1) Then
      ' Assign a 0 (zero) as a false return flag value.
      returnValue = 0
      Exit Do
    End If
 
    ' Increment counting index value.
    lookupIndex = lookupIndex + 1
 
  Loop
 
  'Return a 1 when the value is found and zero when it isn't.
  Finding = returnValue
End Function

I only chose a DO UNTIL loop because that was in the comment. It would be more appropriate to, from my perspective, use a FOR range loop, like this:

  For lookupIndex = 1 To lookupMax
    ' Loop contents.
  Next lookupIndex

Hope this helps.

Debbie asked a question about why a comparison wasn’t working in the Microsoft Excel SUMIF function when the initial array value was empty. This post answers that question.

The SUMIF function takes a lookup range, which anticipates a one column with a lookup value and another column with a value. The SUMIFS function works along the same line but accepts multiple lookups against a series of lookup list columns.

This shows a simple model of the SUMIF function:

The formula in cell G3 is:

=SUMIF(B$3:B$5,"<"&$E$3,C$3:C$5)

If you left off the $ symbol and made the comparison "<"&E3, you could add the sums for any number of values that are less than a value in the same row (or counting index with named ranges). You could also make the inequality symbol a parameter with this modified formula:

=SUMIF(B$3:B$5,$E$5&$E$3,C$3:C$5)

Now, your analysis model lets you choose whether you want to look for the sum greater than or less than a specific number in the lookup range. Named ranges compare index values between the lookup range and comparison range values, which allows them to be in different rows or spreadsheets. The only problem is that the label has an inverted inequality sign. You can fix that by constantly editing the description or by simply replace the text of the label with a dynamic text string, like this:

=CONCATENATE("Comparison """,$E$5,""" Value")

After applying the fix to the label, it looks like:

When the lookup column contains all nulls, the same formula returns a zero, as shown in the illustration.

You'll also get an error flag, unless you unchecked the Flag formulas that refer to empty cells in the error checking options. The IFERROR function can't be used to suppress this type of error.

Why doesn't the error return a null value? That's because an equality operator compares values in the array against a static or dynamic lookup value. While equality operators don't implicit cast values for comparison, they also don't compare null values. Only the ISBLANK function lets you find an empty cell and it can't be used inside a SUMIF function call.

Somebody asked how to solve a problem with the =AVERAGEIFS function and non-contiguous ranges. The solution requires a combination that uses a group of =SUMIFS function divided by an equivalent group of =COUNTIFS functions.

The following illustration shows the problem. It has groups of quarters, and within the quarter groups rows hold products and columns hold weeks. Unfortunately, the =AVERAGEIFS function disallows non-continguous source ranges. It does support multiple criteria, which may be non-contiguous ranges provided they match the number of elements in the source range.

While the sums can be calculated by adding the Total column, the average can’t. You would use the following to calculate the actual average:

=IFERROR(((SUMIFS(D5:P5,D5:P5,">0")+SUMIFS(D18:P18,D18:P18,">0")+SUMIFS(D31:P31,D31:P31,">0")+SUMIFS(D44:P44,D44:P44,">0"))/(COUNTIFS(D5:P5,">0") + COUNTIFS(D18:P18,">0") + COUNTIFS(D31:P31,">0") + COUNTIFS(D44:P44,">0"))),0)

The resolution adds a set of range sums and divides it by an equivalent set of range counts where both values are greater than zero. An =IFERROR function substitutes a zero in the event that the denominator is zero. This last step ensures that the an empty set of cells won’t raise an exception.

I know it helps the person who asks but hope it helps others too.

Somebody was trying to query Oracle via Microsoft Excel 2007, and didn’t have an Oracle ODBC Data Source that enable them to choose Oracle. I’d referred them to this post on adding MySQL’s ODBC driver to Windows 7 but that didn’t help enough. Posting this screen shot may not have helped either.

It appears I assumed too, like everyone would know that it’s on Windows 7 when Oracle 11g (or another Oracle database) is installed locally. The person who posed the question doesn’t have Oracle installed locally and can’t install it.

For those who don’t have an Oracle instance running locally, you can download the Oracle Data Access Components for Windows from the Oracle web site. It’s in the driver sections, as shown in the screen capture below:

This driver contains the necessary OLE DB and ODBC drivers, as you can see in this screen shot.

Once you’ve downloaded it, you can return to this post where you set up an Excel query against a remote Oracle database. You should take note that Microsoft’s future direction adopts Oracle ODBC, like the approach they’ve chosen with MySQL’s ODBC driver. As always, I hope this helps.

I finally got around to finishing my testing of Excel 2011 querying MySQL. That meant installing and configuring Excel 2011 and MySQL 5.5.9 on Mac OS X (Snow Leopard). While installing Microsoft Office is pretty trivial, installing and configuring MySQL wasn’t. You can read about installing and configuring MySQL here. A quick test after this, led me to discover that you still need a third party ODBC, as covered in this earlier blog. You should take note that Microsoft’s future direction adopts Oracle ODBC, like the approach they’ve chosen with MySQL’s ODBC driver.

I downloaded and installed one of third party ODBC tool sets. I opted for OpenLink Software’s ODBC Driver. Instructions for the install with screen shots are in this blog page. The only downside of this was the discovery that Microsoft’s solution requires Rosetta, like Excel 2008. Rosetta enables Power PC application to run on Intel-based Mac OS X.

Launching the Database icon from Excel 2011, I configured the Data Source Name, which you can find here with screen shots. After you configure the Data Source Name, restarting Excel 2011 is the best choice because otherwise you may see several non-fatal errors.

The following screen shots show you how to establish a connection between Excel 2011 and MySQL, and how to query data from the MySQL database:

1. After you click the Database icon, you see the following dialog. Select a Data Source Name and click the OK button to begin a query.

2. The OpenLink MySQL Lite Login screen requires the user name and password. Click the Connect button to launch the Microsoft Query, which appears to be a native Power PC application that requires Rosetta to run it

3. Microsoft Query appears to be a native Power PC application that requires Rosetta to run it. If you want to enter a query, click the SQL View button.

4. Having clicked the SQL View button you now have a work area where you can enter a standard SQL SELECT statement, like the one below. Then, you click the Return Data button.

5. This dialog lets you select where you want to put the return result set from the query. The default is the absolute cell reference of the top and left most cell, $A$1. Click the OK button to query and load the data into the worksheet.

6. Now you can see the data in the worksheet. The only problem is the extraneous characters returned into the column headers of the table. While tedious, they’re easy to fix. The following illustrates the downloaded result set from the previous query:

7. If you perform a query with a join operation, the column names are never displayed whether you provide aliases to the query or not. It means you have to convert the table to a range, remove the false headers, and recreate the table. This appears to be a limitation of Microsoft Query and unlike the behavior in Excel 2010 on Windows. Perhaps it’s all wrapped up in the emulation provided by Rosetta but I couldn’t find any information about what’s happening. That leaves me with pure speculation, which I never like. If you find the reason, post a comment with a link because everyone would benefit.

As always, I hope this helps those who want to work only in the Mac OS X environment. The risk is Rosetta because it will go away, the only question is when and whether the vendors will fix their dependency first or not. The problem with this solution is that Microsoft Query doesn’t return any tables when it appears that it should.

This blog post shows you how to configure Excel 2011 on Mac OS X to query a MySQL database natively installed on the same Mac. If you need installation instructions for the MySQL database natively on Mac OS X, use this post. The configuration is only required the first time and then you may reuse the connection later.

Before you can begin these steps inside Excel 2011, you need to download and install OpenLink Software’s ODBC Driver. I’ve posted instructions in this other blog page because all the screen shots make page loading a problem. You open a new Workbook and click on the Data tab. Then, on the Database icon in the Data ribbon. It lets you import data from the database.

The configuration steps are as follows:

1. This first step prompt you to install Rosetta which allows native Power PC applications to run your Intel-based Mac OS X. It will eventually no longer be supported with the release of Lion (at least that’s the rumor at the time of writing). Click the Install button to get this working.

2. The install took a minute on my Mac Pro, so I figured you should see the progress bar in case it takes a half minute or so.

3. After the completion, you now configure a Data Source Name. This type of configuration is provided for by components on the Windows OS (see this post for an example), which don’t exist on Mac OS X. It appears that Microsoft didn’t want to provide those components, which are a dependency for Excel 2011, and that’s why you need OpenLink Software, specifically the iODBC Data Source Connector. You should note that it looks virtually the same as the Windows OS component. Click the Add button to proceed.

4. The next screen lets you choose a driver from those installed on the Mac OS X system. If you went to the bother to install the free MySQL Connector/ODBC (instructions here), it won’t work because the Microsoft Component requires Rosetta to work. It appears that Microsoft Query isn’t a native Intel-port but rather a Power PC port. Choose one of the OpenLinnk drivers and click the Finish button to continue.

5. The next step requires that you configure the Data Source Name (DSN), provide a hostname and listener port for MySQL (the default port is 3306). After you enter these values, click the Connection tab to the right of the Data Source tab in the dialog box.

6. This step requires that you set the user name, password, and database. The ability to pick the database in the iODBC Data Source Connector is the result of the MySQL Lite tool. It requires that you’ve configured the database first. After entering the data, click the Options tab in the dialog to set the next set of values.

7. This step doesn’t initially let you do much but afterward you can set the character set. Click the Preference tab to proceed with the configuration.

8. There’s nothing you need do here, but I’d suggest in a real situation that you click the Always include VIEWS in table list check box before you click the Finish button.

9. The Data Source Name setup is complete, the next screen lets you launch a connection to the MySQL database. Click the OK button to complete the configuration.

10. At this point, you repeat the Step #1. It launches the iODBC Data Source Chooser dialog. Click the Data Source Name and then the Test button.

11. The test launches a dialog to test the connection, as shown below. Enter the Password in the dialog.

12. If you’ve configured everything correctly, you’ll see the following confirmation dialog. Click the OK button.

Hope this helps you if you’re looking to connect Excel 2011 to query MySQL.

The original blog post with the images was causing a page loading delay, so I’ve moved the post to a page of it’s own. This link send you to the original post with how you setup MySQL Connector/ODBC driver, add a new data source in Windows 7, and a new data import source in Excel 2007. As with other “how-to” posts, it includes screen shots to clear up any ambiguity. I apologize for any inconvenience caused by moving the content.

If you want instructions for using Excel to access an Oracle database, check this older post. I’m working on documentation for a native Mac OS X and Excel 2011 solution and will update this when it’s ready.

As always, I hope this helps those trying to sort through how this works. Naturally, a Visual Basic for Applications (VBA) solution is a better alternative once you’ve set up the data source. This is also on my calendar for documentation.

I’d hoped for a better solution with Excel 2011 on Mac OS X but it appears we still need the third party drivers to connect Excel to MySQL. It’s the same as I reported earlier on Excel 2008. Here’s the message and you can click on it to go the web site and links to buy the drivers.

I tried getting away with installing the MySQL Connector/ODBC before downloading one of those recommended by Microsoft. I discovered that it didn’t work.

Therefore, I download and installed the OpenLink Software ODBC Software, instructions with screen shots are in this blog page. I discovered that this software support track isn’t purely native Mac OS X on Intel-based software because it requires Rosetta like Excel 2008. It also only connects with Microsoft Query, which appears to be a Power PC native application too, at least based on the interface and look and feel. I didn’t do much more research because I’ve concluded that connectivity isn’t ready for prime time on the Mac OS X platform. Microsoft still has more work to do.