voluntaryXchange

Excel Tip # 32: Pushing the Memory Limits of Excel

How much can Excel do? I hit a limit on a project over the weekend.

I’m not sure what imposed the limit. But, I know I was able to copy 800K cells without much trouble, but that copying around 1,700K cells … didn’t work.*

Also note that the previous post about how to clear your Windows Clipboard is essential. If you do one huge copy, you’ll probably need to do this before doing another one, or even before saving your file.

I was running Excel 2010 on a Windows 7 machine (vintage about 18 months ago). I’m not sure how much RAM I have (does anyone keep track of that any more?).

In my spreadsheet, I had an array that was 16,931 rows long. A lot of trial and error showed that it wouldn’t work to copy 110 or more columns that size.

But, gladly, some trial and error also showed that I could go up to 52 columns with no problem (some blue circling from Windows, but the whole copy was done in well under a minute.

One sign that you’re pushing the limits is that the animated dashed line that surrounds a selected range in Excel stops moving. But, this doesn’t mean your stuck yet. You may also get the blue circle to appear and reappear a few times. Again, this is not a signal that you’ve killed Excel.

Unfortunately, I observed no symptoms when I crossed the threshold from possible to not possible. All I know is that the time to complete a big copy goes up steadily until that threshold is reached. After that, the time goes up very rapidly. In short, lack of responsiveness for more than 5 minutes means you should probably close Excel in Windows Task Manager, and start over.

* Why was I copying so much? I have a big data set in a single worksheet, and I had to create a large number of variations on the data series using various Excel functions. But, what a stats program really wants is just the numbers, and not the functions. So I was copying the finished array full of functions, and doing a paste special to values in a clean workbook. The paste special itself didn’t seem to bother Excel at all. If it can get the chunk that needs copying into memory, it can paste it.

Technorati Tags: Excel,memory,copy,limit

Windows Tip # 11: Clearing the Clipboard

You can clear out the clipboard if you run into memory limitations:

When needed, simply run the following command in the Command Prompt window, or from Win+R Run window:

cmd /c “echo off | clip”

This came in handy over the weekend, as I was copying huge chunks of a big Excel worksheet.

Technorati Tags: windows,tip,11,clear,empty,clipboard

An Excel Project for Intermediate Users

This is a fully-fledged explanation, but I think this is an appropriate project for an intermediate spreadsheet class: designing an interactive timesheet.

This project includes:

• Labeling
• Automated dates
• Automated names for the days of the week
• Separate regular from overtime hours and/or any types of hours for which billing is different
• Date and time formatting for input
• Default values
• Totals for days and weeks
• Error traps
• Data Validation
• Worksheet protection

Via Susan Harkins at TechRepublic.

Technorati Tags: excel,timesheet,project

No Peer Review?

Kevin Grier, writing at Cherokee Gothic brings up a huge point: none of the the three Rogoff/Reinhart papers that have been in the news the last month were peer-reviewed.

By now, the whole sad Rogoff & Reinhart story is well-known and well-hashed. But there’s one important point that I don’t see many people making.

There are three RR papers on debt and growth. Their NBER working paper, their American Economic Review Papers & Proceedings paper of the same name, and their Journal of Economic Perspectives paper that adds Vince Reinhart as a third author.

What do all these have in common that has been neglected?

NONE OF THEM ARE PEER-REVIEWED!

I am not making this up.

While the American Economic Review is the flagship journal of our profession, the annual papers and proceedings issue, is made up of very short pieces that do not undergo peer review. Rogoff has this piece listed on his webpage simply as AER, which is very un-needed bit of resume padding.

Similarly, the Journal of Economic Perspectives, is a publication designed to provide popular presentations of research questions. On its webpage it states, “Articles appearing in the journal are normally solicited by the editors and associate editors.”

And, NBER working papers are not peer reviewed at all.

Everyone inside the economics profession knows this. Either R&R did not seek to publish their results in a peer reviewed journal, or no peer reviewed journal would accept them.

I think there’s one poorly written sentence here: “Everyone inside the economics profession knows this.” When I read that, I think Kevin is referring to everyone know Rogoff and Reinhart pulled a fast one. I think what he means is that everyone knows those 3 outlets are not peer-reviewed. But, for my part, I never put it together that they went 3 for 3.

Meanwhile, out here in the hinterlands, I don’t count anything I do as serious unless it’s peer-reviewed.

Technorati Tags: rogoff,reinhart,peer,review,debt,growth

Excel Tip # 31: Hotkey Quirks

Here’s a little hotkey quirkiness. If you access the options menu (ALT+F+T), the tabs on the left do not show hotkeys. But, the first letters work. For example, you can do ALT+F+T+F to get to formulas.

Here’s the thing: what about “Advanced” and “Add-Ins”? It turns out that ALT+F+T+A gets you to “Advanced”. But both ALT+F+T+F+A+A and ALT+F+T+A+D will both get you to “Add-Ins”.

Not sure how widely applicable this is, but it’s good enough to go in my bag of tricks.

Hat tip to ECON 6100 student TK for this one.

Technorati Tags: excel,tip,30,hotkey,add-ins

Excel Tip # 30: Line Up Charts

I didn’t know this:

If you hold down the Alt key (next to the space bar) when you move the chart around you will see that it snaps to the cell grid and makes it easy to line up with others on that worksheet.

From Access Analytic’s The Barrow.

Technorati Tags: excel,tip,29,chart,align

Excel Tip # 29: Excel’s Unusual Order of Operations

I’ve always thought this kind of funny: even people who are “bad at math” tend to have a good handle on order of operations. You know: do the exponents first, then multiplication and division, and then addition and subtraction.

It turns out that Excel violates that standard in one way. Like all things with computers though, our lack of clarity about what we’re doing is part of the problem: GIGO.

So, consider the following:

3-5=-2

You and I look at this and say: “three minus five equals minus two”. That’s correct, but it misses an important subtlety: the two occurrences of minus in that phrase are homophones — they sound the same but have different meanings!

What?

Both of those minuses are (algebraic) operators. But the minus on the left operates on two numbers (i.e., it’s binary), while the operator on the right operates on one number (i.e., it’s unary).

A better way to state that equation would be “three subtract five equals negative two”. That’s correct because subtraction requires you to know both numbers, while negation only requires you to use one.

So, what does Excel do? In order of operations, most people lump negation together with subtraction. Excel does not: it does negation before even exponents.

This can very easily make a difference in your Excel calculations. Consider this

-3^2

If you evaluated this by hand, you’d do the exponent before the minus, like so:

-3^2 = -(3^2) = –9

But, not Excel. Excel says that it makes a difference whether that minus is used for subtraction or negation: subtraction would go after exponentiation, but negation would go before exponentiation. So, Excel does it this way:

-3^2 = (-3)^2 = 9

Wow. There’s got to be a world of hurt in the business world from that one.

Technorati Tags: excel,tip,29,order,operations,minus,subtraction,negation,negative

Excel Tip # 27: Some Spreadsheet Advice

I got this from an IT site, but it applies to business as well

Even if most people don’t regard using Excel as “coding”, they probably should.

Technorati Tags: excel,coding,tip

Excel Tip # 26: Selecting a Huge Range

I frequently need to select a large range that goes off one or both sides of the screen.

This is an easy thing to do if the range is continuous: just hold down SHIFT and use your arrows, END and HOME keys.

But, it’s a pain if your range isn’t continuously filled with data. Then Excel will stop at each blank.

Susan Harkins at TechRepublic offers a solution to this, provided that you know the address of the bottom right cell of your range:

• Click in the top left of your range.
• Click F5.
• Enter the cell at the bottom right.
• Hold down SHIFT and press OK.

Technorati Tags: excel,tip,26,select,range

Excel Tip #25: An Abbreviated Custom Format for Millions, Billions and Trillions

Internally, Excel keeps track of all your digits. But, if you’re dealing with a large number, you’re limited to 3 display choices.

• Showing all the digits
• Using scientific notation
• Relabeling the column as, say thousands, and then reducing the number of digits shown.

For example, you might have GDP in a spreadsheet. You can show this as:

• $15,320,800,000,000
• $1.53208E+13
• 15.3208 (with a notation many readers won’t notice that this is in trillions)

Susan Harkins at TechRepublic suggests creating a custom format that matches what you’d write by hand.

Specifically,

• Select your cell
• Open a “Format Cells” dialog window (click ALT then H then F then M)
• Click the “Number” tab (or click N)
• Choose “Custom” (sorry, gotta’ click that one)
• Choose the “Type” box (ALT+T)
• Enter one of the the following (for millions, billions, or trillions)
  • $#,,” M”
  • $#,,,” B”
  • $#,,,,” T”

There’s one more exceptionally cool thing about this format: it rounds automatically.

Technorati Tags: excel,tip,25,custom,format,millions,billions,trillions