Tag Archives: Tips

Break Even Calculation International Phone

Break Even Calculation with an Unlocked iPhone and International Rates

iPhone 4 PhotoI just upgraded my wife to a new iPhone 4S and since she’s finished with her contract, AT&T will now unlock her old iPhone 4.

Having an unlocked phone is advantageous when traveling overseas because you can pick up a Sim card with a phone plan and save some money. The question I want to answer here is, “Is it worth it?”

Phone Plans

I’ve spent time in the UK and the best place to get a Sim card or even buy an inexpensive mobile phone is with O2. Great coverage, products, service, and you can find them practically everywhere. Just what you need when “on Holiday” and are looking for a mobile phone plan.

With and unlocked iPhone you can pick up a Sim at O2 for £13.50 that gives you 100 minutes of talk, unlimited text, and 100MB of data. My phone plan with AT&T includes international roaming, which is free, but the international roaming rate in the UK is $1.39 per minute. Ouch!

The Conversion

The problem is that I need to convert British Pounds to American Dollars so I can make the comparison. You can find this information online with a search engine (Google, Bing, Yahoo). It would be nice if Microsoft would include currency conversion in the CONVERT function, but I digress.

The Calculation

Break Even Calculation International PhoneGiven a Sim only plan of £13.50, and a conversion rate of 1.6123 dollars per pound, my cost is $21.77 USD. That works out to $0.22 per minute, verses the $1.39 that AT&T will charge me while in the UK.

If I divide my cost in dollars, by the AT&T international rate in the UK, I can talk roughly 15 minutes on my AT&T plan before it becomes cost effective to purchase a Sim card.

The Smart Move

With a smart phone, data is king. So while it’s nice to consider the break even point for talk time on an international plan, the bottom line is that with 100MB of data in the aforementioned Sim plan on O2, there is no comparison.

When going on Holiday or spending time in the UK, pick up a Sim card and stick it in your unlocked iPhone. It’ll be the best spent money on the trip.

vlookup shark

The VLOOKUP Function – Inside Out

vlookup sharkAs part of Shark Week I’ve committed to write something for VLOOKUP week. (It’s what I get for using twitter.) So without further ado.

I love the VLOOKUP Function in Excel. As the name implies, it’s a vertical lookup. Meaning the function will lookup data in columns.

The VLOOKUP Function Arguments

The VLOOKUP function has four arguments and in my opinion the fourth argument always gets overlooked, yet it’s the first thing you need to know. So, like reverse polish notation, we’ll start from the inside and work out to explain each argument.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

range_lookup

The range lookup argument it either TRUE or FALSE. I use FALSE 98% of the time, because using FALSE means the VLOOKUP function will find an exact match. If no match is found then it returns the #N/A error value. Note: And by exact, they mean EXACT. An extra space character, which is not visible to the naked eye, will cause and error.

The strange thing is that you don’t even need the fourth argument, but if it’s missing the default value is TRUE. Bad choice by my estimation, but that because I rarely use TRUE. If the range lookup value is TRUE then the VLOOKP function will produce an approximate match.

This can be quite handy if you want to return something like grades, you know: A, B, C, D, F, when given a numerical value between 0 and 100. Teachers must love TRUE. The only catch here is that the first column of the lookup table has to be sorted in ascending order. You can find out more here.

col_index_num

This argument is just the column number from the table you are looking up. However, this column will contain the data you want the VLOOKUP function to return. For example, I have a table with Names in the first column and Cites in the second column. I want the VLOOKUP function to return the City value so the index number is 2, for the second column in the table.

table_array

The table is where VLOOKUP gets its information. This is where the data is looked up. The reference to the table may take several forms. You normally use an absolute range reference, like $A$1:$B$5. In Excel 2003 I like to use a defined Range Name. In newer versions of Excel I use a TABLE to store the information, and hence the Table Name is what I use for the second argument.

lookup_value

We finally come to the lookup_value. This is a single reference the VLOOKUP function uses to find a match in the first row of the Table. For example, if I want to lookup a Name and find the corresponding City, the lookup_value should reference a name and the first column of the table should be a column of names.

VLOOKUP Function in Action

In the screen shot below you can see the VLOOKUP function shown in the formula bar, which is for cell B2.

=VLOOKUP(A2,myTable,2,FALSE)

I’m using FALSE in the fourth argument for an exact match. For the third argument, I want to return data from column 2 of the Table, which is for the City. For the second argument, the reference is the Table name myTable, which is the range D1:E5. Finally, the the first argument, A2 is a reference to a name.

VLOOKUP Function Inside Out

So the VLOOKUP function in cell B2, looks in cell A2 and finds a name (Ted), then goes to the table, myTable, and locates an exact match in the first column (row 3), then goes over to the second column and returns that value (Bryan) to cell B2.

Note: The myTable reference refers to the range D2:E5, and doesn’t include the header row.

Analysis ToolPak AddIn

Check the Analysis ToolPak Add-In in Excel 2003 [VBA Code]

Analysis ToolPak AddInI recently began using the NETWORKDAYS function in Excel 2007, yet my file was going to be widely used by people with Excel 2003 (both are Windows versions). The NETWORKDAYS function is a standard function in Excel 2007, but with 2003 you have to install the Analysis ToolPak, otherwise you get an error.

The only way for me to avert an error with users who don’t have the Analysis TookPak installed in Excel 2003 is to use some VBA code that runs when the file is opened. I want to see if they are using a version of Excel older than 2007, check if they have the Analysis ToolPak installed, and then tell them, with a pop-up message, to install the Analysis ToolPak, so all the formulas will work correctly.

Create a Routine to Check for the Analysis ToolPak

Open the Visual Basic Editor (Alt+F11) then choose Insert > Module and enter the following VBA code.


Sub CheckToolPak()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Checks to see if the Excel version comes before Excel 2007,
' if so then checks to see if the Analysis ToolPak is installed.
' If not, then notify the user and end the program.
'
' This is required because of the NETWORKDAYS formula
' that is used in the charts.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If CInt(Application.Version) < 12 Then If AddIns("Analysis ToolPak").Installed <> True Then
MsgBox "Please install the Analysis ToolPak." & vbCr & vbCr & _
"Choose Tools > Add-Ins... " & vbCr & _
"then check the box for Analyiss ToolPak, and click OK."
End
End If
End If
End Sub

The code is somewhat self-explainatory except that Application.Version returns a text value, hence you see the CInt function that converts that text value to an integer so that we can tell if the Excel version is less than 12, which is the version number of Excel 2007.

Create a Workbook Open Event

This will do the trick, but needs a trigger to call the routine. So double-click ThisWorkbook in the Project Explorer of the VBA Editor, then select the drop-down at the top-left of the window – where you see (General) – and select Workbook. The Private Sub Workbook_Open() subroutine will appear with no code.

Type in Call CheckToolPak and then save the file.


Private Sub Workbook_Open()
Call CheckToolPak
End Sub

Now when you open the file with Excel 2003, and don’t happen to have the Analysis TookPak installed, you get the following message.

Analysis ToolPak PopUp

If users have the Analysis ToolPak Add-In installed in Excel 2003 there is no message, nor if the user is using Excel 2007, 2010 or 2011.

Select a Column of Non-Sequential Data

Non-Sequential Data

In one of my posts on using VBA to select a column of data, I received a comment asking if the macro could be modified to select data like the screen shot you see to the left.

This data is non-sequential and consequently the CurrentRegion property won’t work. The way around this is to select the very last row in the same column, then shoot up (Ctrl + Up Arrow) to find the last data cell.

Once you know the where the column heading and last data cell is in the current column, the range can then be selected.

The following macro will select the column of data if you start with the active cell at the column heading.


Sub SelectOneColumnData()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This routine will select a non-continuous column of data
' when active cell is located in the column heading.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ac As Range
Dim lRow As Long
Dim lc As Range
Dim col As Integer
Dim cr As Range

Set ac = ActiveCell
col = ac.Column
lRow = ActiveSheet.Rows.Count
Set lc = Cells(lRow, col)

' Find the bottom of the range then re-set the last cell range
Set lc = lc.End(xlUp)
lRow = lc.Row

' Set the current range from the active cell to the last row
' in the column with data
Set cr = ac.Offset(1, 0).Resize(lc.Row - ac.Row, 1)
cr.Select
End Sub

Zoom to 125 Percent – Excel 2011 Default Workbook

I want to have Excel 2011 (for Mac) open to a blank worksheet that is zoomed to 125% and it has taken a very long time to figure this out. It involves saving a template to a buried location, then manipulating the file in Finder to remove the extension. Simple, once you know the trick.

The buried location is:

    Macintosh HD:Users:username:Library:Application Support:Microsoft:Office:User Templates:My Templates:

Or you can check to see where the Excel startup files are located on your computer. Just to go Excel > Preferences then select General and click the Select… box for At startup, open all files in. This is where a modified template file should go.

Excel 2011 Preferences General

Change the Default Workbook in Excel 2011

  • Open Excel 2011
  • Choose View > Zoom, then select 125% and click OK
  • Choose File > Save As
  • In the Save As: box type in Workbook
  • Click the Format: drop-down button and choose Excel Template (.xltx)
  • Make sure you save it to the startup location (discussed above)
  • Click Save

Excel 2011 Save As Template

Note: Keep in mind that I have set Excel preferences to have my workbooks open with just one worksheet. If you have several worksheets in the Workbook Template file, you may have to set the zoom for each sheet.

The Default Workbook Secret

The trick now is to open Finder and navigate to the startup folder, select the Workbook.xltx file you just created, and delete the .xltx portion of the file name. I did this by selecting the file, clicking once to highlight the name, then selected the .xltx extension and hit the delete key.

Excel will warn you that this may be dangerous but stay the course and click Remove.

Remove .XLTX Extension

Now open Excel 2011 and you’ll be looking at a worksheet that is zoomed to 125% in the default workbook.

One More Thing – New Worksheets

We’re not done just yet because we need to do the same thing once again, but this time save the file with the name Sheet, so that when you add a new sheet to a workbook the zoom will automatically be set at 125%.

My Summary

The default files Excel uses for a new Workbook and a new Sheet are template files, with the extension removed, and located in the startup files location.

A common fallacy is that Excel for Mac uses the Normal Template. This belief took me in the wrong direction for quite some time. Hopefully this post will set the record straight.

Obviously, you can set more preferences than having a 125% zoom view of the worksheet. All you have to do is open these template files, make the changes and save (overwrite) them as template files. Then make sure the file extension is removed.

Settings and Shortcuts for Excel 2003

I recently found myself with a new, loaner laptop and a brand new copy of Excel 2003 as my default spreadsheet program. This was fine until I realized there were a number of things “missing.” Like shortcuts and settings that I’ve changed over the years to make Excel serve me, instead of the other way around.

So here’s my list of things I do to “normalize” Excel 2003.

Full Menu’s

My number one pet peeve with Excel is they “automatically customize menus and toolbars based on how often you use the commands.” But my question is, “How do new users know what menu commands are available if they’re hidden?”

I like to use “full menus” so you see ALL menu commands each time each time you click a menu. This is a standardized approach; you see the same thing each time you click a menu.

Here’s how it’s done:

    Right click the toolbar
    Click Customize…
    Click the Options tab on the Customize dialog box
    Check Always show full menus
    Click Close

Customize Dialog Box

General Options

I have only two recommendations here: maximizing the recently used file list and minimizing the number of worksheets in a new file.

General Options Settings

Recently used file list (9)

I like to set the recently used file list to the maximum number. In Excel 2003 that number is nine. This saves you time when searching for a recently used file.

Here’s how it’s done:

    Choose Tools > Options then click the General tab on the Options dialog box. Change the Recently used file list to 9 and make sure there’s a check in the check-box.

Sheets in new workbook (1)

When creating a new spreadsheet file, how many sheets do you actually use? How many times have you looked over a spreadsheet file from someone else and clicked on those bank sheets to see if they contained anything?

Remember the days when a new file had 16 worksheets as the default? Excel 2003 has just three. That’s two to many. I like to set the number of worksheets in a new file to one. If I need another, they’re easily created.

Here’s how it’s done:

    Choose Tools > Options then click the General tab on the Options dialog box. Change the Sheets in new workbookto 1.

Shortcuts on the Toolbar

These custom toolbar buttons are necessary when using Excel 2003: Paste Values, Freeze Panes, Current Region, Auto Filter, and Pivot Table. You can easily customize the toolbar to add these and more.

My Custom Toolbar Icons

For each addition to the toolbar you’ll need to access the Customize dialog box. The long way is to choose View > Toolbars > Customize… or the short way is to right-click a toolbar and select Customize… from the pop-up menu.

Once the Customize dialog box is open, select the Commands tab. Now your ready. Here’s my favorite custom toolbar commands.

Paste Values

The very first custom toolbar command icon I put up. Indispensable. Well, almost.

Here’s how it’s done:

    In the Categories pane select Edit. In the Commands pane scroll down and find Paste Values. Click and hold the left-mouse button down while dragging the icon to a toolbar.

Paste Values Command

Freeze Panes

Another indispensable toolbar command button shortcut icon for anyone who works with lots of data in proper tables.

Here’s how it’s done:

    In the Categories pane select Window and Help. Scroll down until you find Freeze Panes. Click with left mouse button, hold and drag to the toolbar.

Select Current Region

Here’s a neat button that allows you to find the shape of a data region by selecting it for you automatically. Better than the keyboard shortcut Ctrl+Shift+asterisk (*). Reminds me of the CurrentRegion property in VBA.

Here’s how it’s done:

    In the Categories pane select Edit. In the Commands pane scroll down to the bottom and find Select Current Region. Click and hold with the right-mouse button while dragging to a toolbar of your choice.

AutoFilter

A great button to save you some time when filtering tables. If the active cell is on the header row it simply turns on the filter. However, if you select a cell in the data that contains something you want to filter, clicking the AutoFilter will turn on AND filter that selection for you. A one step process that saves time.

Here’s how it’s done:

    Select Data from the Categories pane. Click the AutoFilter command with the left mouse button, hold and drag to a toolbar.

PivotTable

This command serves to initiate a PivotTable from a data table. It also brings up the PivotTable and PiotChart Wizard when you are working on an active PivotTable.

Here’s how it’s done:

Select Data from the Categories pane. Scroll down to the bottom of the Commands pane and find PivotTable and PivotChart Report icon. Left-click, hold and drag to a toolbar.

More Stuff

Find your favorite command not shown on a Toolbar and load it up. Pronto!

International Short Date Formatting for the TEXT Function

I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range.

Here’s what a US user sees:

    From: 6/6/2011 to 6/10/2011

Here is the formula:

    =”From: ” & TEXT(MIN(ExtractData!A:A),”m/d/yyyy”) & ” to ” & TEXT(MAX(ExtractData!A:A),”m/d/yyyy”)

The Min and Max dates are in column A on a worksheet named ExtractData.

The problem is trying to automatically change the date format in the second argument of the TEXT Function – “m/d/yyyy” – which is a string argument. We can use VBA to accomplish this, but first a refresher on the TEXT function syntax.

TEXT Function Syntax and Argument

The Solution

The Application.International Property solves this problem with the xlMDY argument, which is TRUE if the date order is month-day-year, and FALSE if the date order is day-month-year. This property is put into the Workbook_Open event and modifies a defined name constant that’s used for the second argument of the TEXT function.

Here’s how it’s done.

Create a Defined Name Constant for the Date Format

I created a Defined Name Constant to store a Short Date format, and gave it the name sd_format.

In Excel 2007 and 2010, choose Formulas > Define Name to bring up the New Name dialog box where you type in sd_format in the Name box, and type =”m/d/yyyy” in the Refers to box. Remember the equals sign.

New Name dialog box

In Excel 2003, 2008, and 2011 choose Insert > Name > Define to bring up the Define Name dialog box. Type sd_format in the Names in workbook box, then type =”m/d/yyyy” in the Refers to box. Be sure to use the equals sign.

Substitute the Named Constant in the TEXT Function

The sd_format defined name can now be substituted for “m/d/yyy” in the second argument of the TEXT function. Here’s the new formula:

    =”From: ” & TEXT(MIN(ExtractData!A:A),sd_format) & ” to ” & TEXT(MAX(ExtractData!A:A),sd_format)

Since sd_format is already a text string, enclosed quotes are not needed.

Create a Workbook Open Routine

In the VBA Editor, I created a Workbook_Open subroutine, which looks at the computers international setting for the US, and if TRUE changes the sd_format value to “m/d/yyy”, and otherwise changes it to “d/m/yyyy” for the UK short date format.

Private Sub Workbook_Open()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This routing updates the regional date setting format for the
' defined name sd_format.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Application.International(xlMDY) = True Then
ThisWorkbook.Names("sd_format").Value = "m/d/yyyy"
Else
ThisWorkbook.Names("sd_format").Value = "d/m/yyyy"
End If
End Sub

How it All Works

Each time the workbook is opened the Workbook_Open routine executes the IF-THEN-ELSE statement, which simply looks to see if the computers region setting is month-day-year, then sets the defined name sd_format to “m/d/yyyy” which is a US format. If the computer’s region setting for the long date format is NOT month-day-year (and presumably day-month-year) then sd_format is set to “d/m/yyyy” for the UK.

Every TEXT function using sd_format for the second argument will then have the proper short date format for that computer.

Note: Obviously if the region settings are changed on the computer while the file is open the file will have to be closed and reopened, but this would most likely be a rare occurrence.

That Damn Delete Key in Excel for Mac

Where is the delete button on Mac - ExcelI have no earthy idea why it took me so long to figure out how to delete the contents of a cell or range in Excel for Mac. Ever since I bought my MacBook Pro I’ve known the Delete key on a Mac isn’t really a Delete key.

I mean, since my background is with Windows, I have ingrained knowledge on how the Delete Key works on a computer. Ingrained, I tell you.

But all of that knowledge was shattered upon getting a Mac.

Where Is the Excel Delete Button on Mac

After some consternation, I learned where is the delete button on a Mac. To press the delete button on Mac computers you have to hold down the fn key and the Delete key at the same time when you want to delete something on a Mac. (Skip to video)

After a while, you get used to the idea that the Delete key on a Mac is really a backspace key and using fn+Delete gives you the real Delete key action. 🙂

Of course if you’re a long time Mac user you probably think I’m cuckoo. But hey, this is my blog, think what you like. I’m not the only one who’s decided to start using a Mac after a lifetime of Windows abuse use.

Excel for Mac

Anyway, when using Excel on a Mac — I’ve got versions 2008 and 2011 — you run into a learning curve with all the unusual shortcut keys, function keys (1, 2), and menu and ribbon things that are different from the Windows version of Excel. So there’s a tendency to forget about how the Delete key works on a Mac.

I mean, this is Excel we’re talking about here. Hitting the Delete key is supposed to delete the contents of the active cell, for cryin’ out loud.

In Excel for Mac it does that, but the cursor also gets stuck inside the cell in edit mode. You have to hit the enter key to finish deleting the contents, but this act also moves the active cell to the next cell down.

And if you’ve selected a range and hit the Delete key, the active cell contents are deleted and the cursor is stuck inside the cell in edit mode. You have to hit the Enter key, which does nothing but take you to the next cell. The range contents are still there, with the exception of the active cell.

Not the kind of behavior that occurs in Excel for Windows.

How to Delete Cell and Range Contents in Excel for Mac

The trick is to remember that fn+Delete is really a keyboard shortcut to the Delete key on a Mac. Then the world rights itself and the planets align. Frustration abates. You’ve finally found the magic. Your mojo is back!

Watch this 54 second video to see what I’ve been babbling about for the past 454 words.


YouTube link

Date Format Settings Excel Windows

Regional Date Formats in Excel

I recently returned from two weeks in Ireland where I installed a couple of Excel reporting systems and came across the dreaded regional date settings. My computer is set for the US region, whereas the client’s computers were set for the UK region. When I mention regional settings on my computer, I’m talking about Windows and not Excel.

Trying to get Excel to automatically switch date formats between computers using different region settings was something I experienced a few years ago while working in England. Back then I made the mistake of ustng Excel to change the formatting for my dates. Not the way to go.

Regional Date Formats

The US Short Date format is represented in the form “m/d/yyyy” which means dates are shown in a “Months/Days/Years” format. Contrast that with Ireland, the UK, and most everywhere else, where the date format is “dd/mm/yyyy” and takes the form Days/Months/Years.
Regional Date Formats

Using the Default Short Date Format in Excel

Since I live in the US my computer’s regional settings are set for the US. When formatting dates in Excel for files that may be used internationally, it’s wise to use date formats that begin with an asterisk (*) per the Format Cells Dialog box.

Date Format Settings Excel Windows

Any date formatted in this manner will display correctly in another region. I can view the date June 13, 2011 in my spreadsheet as 6/13/2011 and send it to a person using a UK regional setting on their computer and the date will automatically display as 13/6/2011.

Nice and neat. We’re all speaking the same language here.

How Not to Change a Date Format for a Different Locale (Region)

Let’s say you are in the US and are designing a spreadsheet that will be used in the UK. You want to see “what they see” and decide to change the cell formatting in a column that contains dates. You select the range, open the Format Cells dialog box (Ctrl+1) then select English (U.K.) from the Locale (Location) drop-down list, and click OK.

Excel Local Date format UK

Bad move.

First of all, look at the picture above and notice there are no date formats that have a leading asterisk (*) which means that no date format will survive a change to a different region. You are stuck with this format in every region, whether it’s relevant or not.

How to Change Region Settings in Windows

The proper way to “see” a different region’s date format is to change your computer’s Region setting. Click the Windows Start button and select Control Panel. Find and click the link for Clock, Language, and Region.

Windows Regional Settings (Navigation to)

Next click the link for Change the date, time, or number format.

Windows Control Panel Regional Format Settings

On the Region and Language dialog box click the Formats tab.

Windows Region and Language Dialog Box

In the Format drop-down box select the Region of your choice. In my case that’s English (United Kingdom).

Windows Region English UK

In the screen shot above you can see the Short Date format is dd/MM/yyyy. Click OK to set your computer’s region.

Now open Excel and all dates previously formatted with the default Short Date format for the US (*3/14/2001) will show up in the Short Date format for the UK.

To check this fact, select a cell with dates, then bring up the Format Cells dialog box (Ctrl+1) to see that the Short Date format in Excel has automatically changed to *14/3/2001.

Windows Format Cells Date UK

This is how you change the date formats for different regions.

Customize the Region Date Format in Windows

I don’t like having two digits showing for the day and month in the Short Date format. To me 4 July, 2011 should be represented as 4/7/2011 and not as 04/07/2011. However that’s what you get when you select the English (United Kingdom) region setting.

I used a custom format for the UK region short date setting to rid my spreadsheet of the unnecessary zeros. To do this you bring up the Region and Language dialog box, as we did previously, and click Additional Settings… then select the Date tab.

Customize Regional Date Format 1

Type d/M/yyy in the Short Date box and click OK.

Customize Regional Date Format 2

Now all months and days with single digits show up without a leading zero in Excel when using the short date format.

Related Posts Plugin for WordPress, Blogger...