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.

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.

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.

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.

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

Dynamic Range Names?

Does anyone use dynamic range names anymore? In the past, this technique was the only way to create a chart that expands as you enter more data (here's an example) . But now you can easily create a self-expanding chart by using a table.

There must be other good uses for this technique, but I can't think of any.

Creative Charts

Check these out:  Creative and advanced chart design in Excel.

You can download the workbooks to see how these charts are made. If you're stumped, use Excel's Name Manger to examine the names. Then you'll be even more stumped.

Try This DatePicker Task Pane App

To get a feel for how Office 2013 apps work, I created a simple one: A DatePicker Task Pane app. This works with both Excel 2013 and Word 2013.

If you have a copy of the Office 2013 Preview, please give it a try. 

Here's what you need to do:

1. First you must specify a trusted app catalog. In this case, it's a Sharepoint app catalog where the app's XML manifest is stored.  In Excel,  choose File - Options - Trust Center, then click the Trust Center Settings button.
2. In the Trust Center dialog box, select Trusted App Catalogs.
3. In the Catalog Url field, type this: https://jwalk.sharepoint.com/sites/jw, and then click Add Catalog.
4. Make sure the Show in Menu option is checked, and then click OK to exit.
5. In Excel 2013 Preview, start with an empty workbook.
6. Choose Insert - Apps for Office.
7. In the Insert App box, click the SHARED FOLDER option.
8. You should see an item named DatePicker Task Pane App. Select that item, and then click Insert.

The DatePicker Task Pane app should appear. Note that the inserted app is stored with the workbook. In other words, if you activate another workbook, the Task Pane will not be available (unless you also insert it into that workbook).

At least that's how I think it works. Plus, Sharepoint has always been a confusing mystery to me, and I really don't understand the concept of trusted app catalogs. If anyone (besides me) can get this to work, I'll be satisfied (and a bit surprised).

By the way, only the XML manifest file is at my Sharepoint site, and that file points to the app's location on my server. The app is just a plain old HTML document, with lots of Javascript (the bulk of which is Unobtrusive Date-Picker Widget V5, from frequency-decoder). In fact, you can run this app in your browser rather than as an Office App: http://j-walk.com/datepicker/datepicker.htm. You won't be able to insert any dates when it's running in your browser, but you can see how it works and use your imagination.

Time will tell if Office Apps catch on. At some point, developers will be able to submit apps to Microsoft, and (if they meet their standards) they will be available at the Microsoft Office App Store. If the apps are sold, rather than given away for free, Microsoft will keep (I think) 20% of the proceeds.

To me, the biggest problem is the limited interaction with the Office programs. For example, an Office app can read data from a range and write data to a range -- but that's about it. In this DatePicker app, it would be nice to provide a list of date formats to apply to the date cell, but that's not possible.

My problem is that I'm still thinking in terms of add-ins. Office Apps are nothing like add-ins, and are intended to serve a much different purpose.

I Made A Simple Task Pane App

If you've read about Office 2013, you may have seen the terms Content App and Task Pane App. I tried my hand at creating a task pane app,  just to get a feel for how it works.

This task pane can be inserted into a worksheet. It displays my Excel Blogs list, which links to the most recent headlines at each blog. It looks like this:

It's really nothing more than a browser that displays this page. When you click one of the links, the Task Pane displays the recent headlines for that blog as links.  To read an article, click and it opens the article in a browser window. Actually, it's an Internet Explorer browser window. Apparently, task pane apps that have outside links ignore your default browser and always use IE.

This app, of course, offers no advantage over just viewing the site with a browser. Next, I think I'll try to make a flashy date picker app, using HTML5 and Javascript. That one might actually be useful. Documentation is pretty poor at this point, but from what I'm reading, a task pane app can read cells in a worksheet, and write data to cells in a worksheet. That's about it.

Contrary to what's implied in this headline, apps will not replace add-ins: Microsoft Office 2013- Goodbye to adds-in, bring on the apps.

The Research Behind Flash Fill

I predict that one of the most controversial new features in Excel 2013 will be Flash Fill. Here's a video that demonstrates how it works. It's an AI alternative to extracting specific  parts of text strings. For example, if you have  a column of names, you can type a few last names and Flash Fill will figure out what you're doing, and fill in the empty cells.

It's a great concept, but it can also lead to lots of bad data. I think many users will look at a few "flash filled" cells, and just assume that it worked. But my preliminary tests leads me to this conclusion: Be very careful.

Flash Fill works in two ways: (1) It can extract data by example, and it can (2) create data by example. Creating data seems to be much more reliable. But use caution when extracting data. For example, most of the extracted data will be fine. But there might be exceptions that you don't notice unless you examine the results very carefully.

For the technically-minded, here's a Microsoft Research report (PDF) that seems to be the basis for this feature:  Automating String Processing in Spreadsheets Using Input-Output Examples.

Excel 2013 Charting Notes

Today I discovered a new feature in Excel 2013 that I hadn't seen mentioned elsewhere: When you create a chart using Excel 2013, you can specify an arbitrary range of data to be used as data labels! Users have been requesting this feature for at least 10 years -- probably longer. Currently, you need to use a utility program (such as PUP or the utility from AppsPro).

Charting in Excel 2013 is vastly improved. The Format dialog box has been replaced with a task pane. Once you get over the initial shock of not being able to find the commands you need, you'll probably find that it's a significant improvement. In addition, when you activate a chart, you see three icons on the right. These icons are the express route to making common changes to your chart.

The third icon has some new filtering options. For example, you can easily hide (not delete) a series, or hide specific data points in a series.

Another improvement is creating combination charts. In the past, creating a combination chart required several manual changes to a normal chart. Now it's all done at the time you create the chart. And you even get a live preview.

Yet another new feature is "recommended charts." When you create a chart, you can choose to let Excel give you some recommendations. I can't say that I always agree with the recommended charts, but new users will appreciate the suggestions.

All in all, I'm impressed. Excel 2013 has come a long way in making charting easier to use for the masses.