J-Walk & Associates, Inc.

Cloud Stats

Interesting article for statisticians:  Spreadsheets in the Cloud - Not Ready Yet.

Cloud computing is a relatively new technology that facilitates collaborative creation and modification of documents over the internet in real time. Here we provide an introductory assessment of the available statistical functions in three leading cloud spreadsheets namely Google Spreadsheet, Microsoft Excel Web App, and Zoho Sheet.

Our results show that the developers of cloud-based spreadsheets are not performing basic quality control, resulting in statistical computations that are misleading and erroneous. Moreover, the developers do not provide sufficient information regarding the software and the hardware, which can change at any time without notice. Indeed, rerunning the tests after several months we obtained different and sometimes worsened results.

New Tip Posted

Today I posted a new tip that describes how to use a simple macro to play MP3 files without getting an annoying security warning: Playing MP3 Files From Excel.

PUP v7.2 is Available

This is a minor upgrade for Excel 2013 users.  It fixes some problems related to the new single-document interface introduced in Excel 2013. The only significant feature added is the ability to insert a live array formula calendar that begins on Monday, rather than Sunday.

And one feature was removed: Currency Converter.

Most PUP users will not need this upgrade. It's primarily for those who experience problems when running Excel 2013.

To request an upgrade, click here.

Updated Book List

I updated this site's book section with my new Excel 2013 books. 

They aren't actually available yet, but all of the links to Amazon are functional.

No Google Stock Info

There's a feature in Excel that lets you insert refreshable stock information into a worksheet. Just enter the ticker symbol in a cell, right-click, and choose Additional Cell Actions. It works for every major stock symbol I've tried -- except for GOOG.

Intentional or just an oversight?

A Functional IMAGINARY Tale

I've been sleeping on a COT for a MONTH NOW because I don't have any CLEAN SHEETS LEFT. So TODAY I went looking for SHEETS -- SUMPRODUCT that offers REAL VALUE for the DOLLAR. The MEDIAN cost varies in different AREAS, so I got on the Internet AND did a SEARCH to LOOKUP SHEETS. There were many to CHOOSE from: ROWS of every TYPE of SHEET you can think of, EVEN in the MID PRICE range. I thought it would take DAYS, but I got lots of INFO in a MINUTE. Excellent WEBSERVICE.

It wasn't a WORKDAY, so I decided to visit a local store. I found the ADDRESS AND saw their SIGN. Shopping isn't something I do with any FREQUENCY, but SUM times, it seems like the RIGHT thing to do.

I found some SHEETS and told the clerk, LEN FISHER, that I could FIND a LOWER price online. He looked at the CEILING, sighed, and said, "NA, I can’t MATCH it ACOS I’m NOT the manager AND don’t have the PROPER POWER OR RANK. I must CELL it at the EXACT PRICE that’s listed, not a DOLLAR LOWER -- AND that’s a TRUE FACT. Can you SUBSTITUTE something else OR must it be the EXACT PRODUCT? Perhaps some rat POISSON? OR a slice of PI?"

"IRR . . . that makes no sense. Are you for real?" I asked.

"Yes, sir, IMREAL. Hold on a SEC AND I’ll send a TEXT message using our secret CODE AND try to ROUNDUP the FLOOR manager."

"BAHTTEXT!" I wondered: ISTEXT the best method of contacting him?

Apparently so, because the store manager appeared within a SECOND. He was just ROMAN ROUND, checking out the store. He was a TRIM man with LARGE ABS, a dark TAN, AND a moustache on his UPPER lip. The TYPE who likes to look in the MIRR. Nothing special about his eyes, though. Just STANDARDIZE.

"My name is MAX T. PEARSON. Is there SUM way I can help you TODAY?" he asked with a smile.

I showed him the SHEETS AND said, "Hello, I’m KURT N. BESSELY. I just want to buy DPRODUCT, AND your employee won’t YIELD on the PRICE."

The manager said, "To you, that ISODD AND probably seems off-BASE, but it ISLOGICAL to me because of our store policies. Be assured, however, that you can COUNT on me. I won’t waste your TIME with FALSE promises, AND I have CONFIDENCE I can help. Buying SHEETS shouldn’t be that COMPLEX, AND I want you to be satisfied to DMAX. Wanting a discount isn’t a SIN. IF you'd like a LOWER PRICE, just ASC."

"IF you do me a SMALL favor AND LOWER the PRICE by five PERCENTILE buy the SHEETS," I offered.

"No PROB," he replied.

Net EFFECT? I give MAXA lot of credit for a better than AVERAGE shopping experience. He FIXED the problem, AND the store has a CONVERT. I spent less than an HOUR shopping, RECEIVED good service, AND I RATE them highly. I'll be back during their end-of-year sale that runs from OCT2DEC.

Best of all, I was able to REPLACE my SHEETS, AND I don’t use ACOT. When the FORECAST calls for a drop of 30 DEGREES, I like to be under COVAR with a LOG burning in the fireplace for the DURATION.

PUP Compatibility With Excel 2013

PUP v7.1 works with Excel 2013, but it has some problems because Excel 2013 uses a single document interface. 

Specifically, the PUP utilities that use a "stay on top" dialog box don't stay on top if you switch to a different workbook window. These are: PUP InfoBox, Text Tools, Interactive Hide/Unhide, Date Picker, Worksheet Map, and Insert Sequence Number. Also, the PUP Enhanced shortcut menus may not appear in all windows.

I plan on releasing a PUP update in 3-4 weeks that will fix these problems.

30 Years Of 1-2-3

Saturday was the 30th anniversary of the release of Lotus 1-2-3.  My interest in spreadsheets began with VisiCalc, but it really took off when I saw 1-2-3 running on an IBM-PC.

So, I've been messing around with spreadsheets for half of my life.

Dan Bricklin shares his thoughts.

There were many spreadsheet programs on the market when Lotus 1-2-3 came out, including VisiCalc, SuperCalc, Microsoft's Multiplan, and Context MBA. Mitch compared 1-2-3 in the demo he showed me to Context MBA. Context MBA was programmed in a slow, high-level, byte-coded language, as I recall, while 1-2-3 was in assembler for the IBM PC. He told me that Lotus tried to meet similar goals to what we had originally targeted with VisiCalc, such as keeping up with fast horizontal and vertical scrolling. It felt at least as good with big sheets and the full screen of the IBM PC as the original VisiCalc felt on small sheets on an Apple II with much fewer characters on the screen.

The image is from the Lotus Museum.

Twelves

Here are some 12s. The image file is 12 Mb.

Announcing: PUP/Mac

If you use Excel 2011 for Mac, please check out the brand new PUP/Mac add-in.

You can't beat the price: It's free. No strings attached.

The add-in was adapted by Ron de Bruin, an Excel MVP in The Netherlands. If you find this add-in helpful, please make a donation to Ron. He put a lot of work into this project. He's also responsible for tech support, so if you have a problem contact him (not me).

Office 2013 RIBBON TABS

During the Office 2013 preview, one of the complaints was about the UPPER CASE text in the tabs. For example, see this thread.

Apparently, Microsoft employees read their forums. In the final release, you can change the case of the Ribbon tab. Just go to the Customize Ribbon section of the Excel Options dialog box. Select a tab name and click Rename. Type your new name, using any mix of upper and lower case character. But… If the name is the same as the original tab name, it will revert to uppercase.

The trick: Just add a space character to the end of the name.  Here's an example. The HOME tab has been renamed Home<space>.

You're welcome.

Excel 2010 vs 2013 Speed Benchmark

In a recent comment, someone noted:

If you try to experiment your previous macros, you will find that they will take longer to run.

I haven't noticed any significant speed differences, so I did a quick search for a speed test and found this: Excel Benchmark 2011- An Excel VBA Speed Test. I ran the benchmark on Excel 2010 and got this:

On Excel 2013, I got this:

According to this, Excel 2013 is a bit faster. Not much faster, but it's certainly not slower.

I Think It’s Finished

I turned in a bunch of last-minute edits today, and that might be it.  I'll have one more opportunity to fix minor stuff when I see the page proofs.

This will certainly be the best edition ever.  Supposedly, it will be available in March. You can even pre-order it at Amazon: Excel 2013 Bible. Right now, the sales rank is #1,181,592, but I think that number will get lower. 

Three more to go -- and those are all mostly finished.

Excel 2013: Finalized

I installed the RTM version of Office 2013 today. I've only looked at Excel, because that's the only component that really counts.The Preview was amazingly complete. Based on an hour or so of digging around, Excel 2013 RTM differs from Excel 2013 Preview in these ways:

1. The interface animations are gone. The preview had lots of visual stuff going one when you moved the cell cursor and selected things. Now it's all back to normal (unless I've overlooked a setting). Charts still animate when the data changes.
2. If you hate the all-white look, you now have a choice of two other themes: light gray and dark gray.
3. There is now a way to create a workbook from a custom template. That feature was omitted from the preview.
4. The title bar has a dropdown: Ribbon Display Options. That will mean a few screenshot revisions for my books.
5. Touch mode spreads the Ribbon controls much further apart. But it still has no effect on the other UI elements (which probably account for about 90% of actual use).

There are probably more changes that I haven't found yet.

I was surprised to see that the task pane (which is a primary way to edit objects), is still not accessible from the keyboard (mouse and touch only).

Still missing in action is the XML schema for RibbonX code.

New Units For the CONVERT Function

I've been reading all I can about Excel 2013, but I haven't seen this mentioned. The CONVERT function has been greatly enhanced -- 47 new units, by my count. Now, calculating the number of acres in a square light-year will be a piece o' cake.

Here's the Help page: Help Page