Microsoft Excel

I have to admit that I wouldn't mind seeing the back of Analysis ToolPak - it's use of static formulas that don't update when the underlying data changes goes against the whole idea of spreadsheets and in my view is probably one of the most important areas that contribute to spreadsheet errors. Particularly as functions such as LINEST can do the same task dynamically. The clean up too is long overdue - does the Covariance tool still use VARP for the diagonal elements and hard-coded numbers for the off-diagonal elements?

On Solver, on lots of occasions now it's painfully slow for even modest-sized problems - with the new limits, users might not have the patience to wait

One thing that causes me problems at the moment is the need to use Tools / References to enable Solver and AnalysisToolPak in VBA modules, especially as the location of the .xla files is specific to the version of Office used and/or file location chosen by the user. So when I'm distributing files I tend to leave off the reference to Solver and let users do it themselves (not always succesfully). One specific difficuly I've encountered is the inclusion of code in my VBA module sheet with the Complex function from ATP. When I change machines to one with a different Office location for the atpvbaen.xla file and open the file, the Excel jumps straight into VBA compile error on the module sheet - but clicking the OK button doesn't help as Tools / References is grayed out so that I can't add the correct reference

Every time I try to install ATP or CSW, it says that it's not available and tries to install it from the CD - which means it just hangs until I kill it.  Has anyone outside of MS actually intalled these add-ins?

Hi Dave
Still enjoy the blog.

Sorry off topic, but would be interested in hearing why. A colleague of mine (also an excel freak)used excel to do a garden plan. I thought great idea, as I need to replan my garden. Just set the cells to be square and hey presto, lovely grid to work on. Electronic graph paper.

However I discovered in the beta(and surprised myself in never seeing this before), that when you set column width and row height from the menus, the units are not the same. ie set it to 10 by 10 you don't end up with a square but a rectangle. The problem is also in excel 2003. It is also not clear what units they are using.

Personally, I think the dialog should indicate the units and ideally let you choose between pts, pixel, cms, inchs.

I installed Solver and the ATP and I'm outside MSFT.

I can't get Solver to work on XL 2003, though.

I personally have not been able to try out the ATP on beta 2--it crashes Excel.

That said, I concur with spursfan. The ATP should be integrated, live, and (as I have pled for before on this blog :-) enriched with a lot more statistical tools.

For Excel 13--I recommend taking a look at chapters 2 and 4 of "Multivariate Data Analysis" by Hair et al. If Excel can implement most of what they describe, it'll go far in displacing stat software!

May have forgotten to ask before, but will the names of the former ATP functions be internationalized? Much fun if non-English versions would have different function names than in XL11 and prior.

To answer the off-topic question on scaling in Excel

On my Excel 2000, the column height is linear - 7.5 = 10 pixels whilst 15 = 20 pixels; the column width is linear but with a constant - so 1 = 11 pixels whilst 2 = 17 pixels and 3 = 23 pixels

I'm sure JensenH would have an explanation for it!

Different topic: the DATP.

I agree with the others who've stated that the DATP sucks. Well, maybe they didn't express it quite that way, but it's the intent that counts.

Then again, why do a good job when fewer than 5% of users take advantage of the feature? That seems to be MSFT's MO for the DATP.

I disagree that merely adding features will replace stats packages, at least the ones like S (or R) or Stata that are implemented as specialized programming languages. There'll always be an advantage for programs that can do in 20 lines of code what Excel would need thousands of formulas to mimick. Excel will NEVER be useful for heavy duty simulations.

There's also the question of how many features to add. Picking an arbitrary text is one possibility, but that leads to questions of which text to pick. Francis's suggested text would seem to leave out time series and survival models.

I will harp on one DATP tool. The sampling tool REALLY SUCKS. Consider the exquisitely perverse behavior of selecting an output range. When you click in the radio button for Output Range in the Output options section of the Sapmling dialog, does the DATP then put the focus in the refedit box for the Output range? Of course not. It put the focus in the refedit box for the Input range. This leads cynics to ask whether sadists outnumber incompetents on the Excel development team.

Harlan: You are right. Excel will never replace those languages, just as Word has not replaced dedicated desktop publishing software.

However, they can make inroads. As it stands, if you need to do any statistical analysis beyond elementary math, you have to use a different program. That is one customer lost (and perhaps a reason why "5%" only use these tools--they know better! Of course, this is a vicious cycle: Poor features/supply drive away users, and low use/demand precludes to improve those features.)

I agree: it can be efficient to code in R/S, and any choice of a book will leave gaps in Excel's coverage. That said, it's often much faster to toy with an open data set and see changes reflected LIVE in an open worksheet and chart. Excel with improved statistics would blow away a lot of stat(ic) packages for this simple reason alone.

Incidentally, I no longer use DTP. Word now has 95% of the features of DTP software--and is much easier to use.

Francis & spursfan -
Column width is measured in characters. This is a hangover from the days of DOS when there was only one font, and it was monospace. The "character" unit of measure uses an average character width in the standard font. This is approximately 2/3 of an em for most proportional fonts. The number of points depends on the standard font size you have selected (or left as default).

David - It would be helpful if we could select a unit of measure on the column width dialog.

Francis, Word vs DTP is a strained analogy to Excel vs stats packages. Also, FWLIW, math and scientific journal articles are still written in LaTex, and likely will continue to be for the next decade. [Then there are the few curmudgeons like me who still fool around with [tn]roff, grap, pic, tbl and eqn.]

If you want finer control of eyewash, no doubt Office apps will continue to catch up with deducated eyewash generators. But for truly techy stuff, Office apps just don't cut it precisely because they have to be usable in dummies mode and also because the ROI just isn't there (and it's very foolish to believe Microsoft does anything that isn't expected to produce a healthy return).

I followed the instructions in your email this morning about loading addins.  When I hit the "Go" key as you suggested, I got the message that I needed to close a program with the choices of "Ignore, retry, or abort."  I chose ignore figuring what's the harm.  I had left Outlook open to follow the directions on the 2nd monitor.  Then I got the OPMAPI32.dll error for Outlook.  It has now taken me over 3 hours to overcome your "help."  Thanks.

When I follow your directions, Solver comes up in the Add-Ins menu & won't load.  Also, ATP does not show up anywhere?  What gives?

SteveA
off topic : Row height and column width.

I remember reading some where that the row heights and column widths are measured in different units.... One is somehow related to font size and another is 1/72 th of an inch or some thing crazy like that. So if you want a square grid you cant get one by adjusting the row height = coloumn width = say 10

below is a code which converts rectangles to squares - dimensions in cm - like a nice graph paper ...

Sub graph_paper()
Dim desired As Double, looper As Integer
cm = Application.InputBox("Enter Square Length in Cm", Type:=1)
If cm = False Then Exit Sub
desired = cm * (0.393700787401575) * 72
Application.ScreenUpdating = False
For looper = 1 To 10
ActiveSheet.Columns.ColumnWidth = _
desired * ActiveSheet.Columns.ColumnWidth / [A1].width
ActiveSheet.Columns.RowHeight = _
desired * ActiveSheet.Columns.RowHeight / [A1].Height
If [A1].Height = [A1].width Then Exit For
Next
Application.ScreenUpdating = True
End Sub

Harlan....

I dont use the sampling from Data analysis to get a data sample any more...

I use advance filter.

Try this

a) Take a table of data - say 1000 rows (could be neumeric or text...unlike the Sampling of data analysis which requires data to be neumeric ---dumb !)

b) In a cell type =Rand()<0.02 - This means you want a 2% sample

c) Select the table - Data-advance filter, copy to another location, criteria range = cell containing the rand formula and the cell above it...

d) In the output range select any cell outside the range... and you get a random sample....

Havent figured out why or how it works...

Credit to Jim Cihar....www.dataspectrum.cz

Sam

Sam

Thanks for your code. I shall use it.
Steve

Solver seemed to install for me but it doesn't show up on the Data ribbon.

I am missing the entire Analysis group on the Data tab.

Sam - "1/72 th of an inch or some thing crazy like that."

That crazy thing is called a point. It has been so called for centuries by typesetters.

What is crazy here is the use of "character width" as a unit of measure, since it is variable.

This is perhaps not the best place to post, but I have a question about the MSFT "Excel Addin for SSAS" and its future in relation to Office 2007.

I can see that the new Pivot Table functionality makes the addin largely redundant...but what about the ability to construct "free form" reports? Is there going to an equivalent for Excel 2007 or is the intention to rely on SSRS to provide this type of complex report construction?

Personally, I would have hoped that the Analysis Toolpak would have become automatically "included" in Excel - just like all the other functions.  I don't really understand why it exists as a separate add-on.  

I know I will run into (minor) difficulties when sharing workbooks that use the ATP with other users - and I resist using the ATP functions except when necessary...  

So why is ATP a separate add-in anyway?  

I followed the instructions for ATP and solver but there I have no "Analysis" group in the Data ribbon.  What should I do?  Aside from reinstalling the whole program.

Hi all – thanks for the comments.

To everyone that commented on problems getting solver installed/working - there were known issues with the public beta, but things should work pretty well in the beta refresh coming very soon.  My mistake for not mentioning it – please give it a whirl in the beta refresh.

SteveA – What are you gardening?  I am a big fan of heaths and heathers.  With respect to the units we use for row/col sizes … our metric is based on the average character size for the default font, so not only is it inconsistent wrt width/height, but it varies across fonts/systems.  Perhaps a better approach is to use Ctrl+A, then resize one row and one column with the mouse.  The pixel size shown in parenthesis should allow you to get things squared up pretty easily.  Sam and others have also provided suggestions.

A User – thanks for the feedback.

Francis – thanks for the feedback.  Feel free to email me with specific suggestions.

Harlan, yes the former ATP functions will now act like the rest of the function library wrt internationalization.  See the comments in this post: http://blogs.msdn.com/excel/archive/2005/10/20/483205.aspx#comments">http://blogs.msdn.com/excel/archive/2005/10/20/483205.aspx#comments

Shawn S – the formulas in the ATP have been built-in as native functions this release.  See http://blogs.msdn.com/excel/archive/2005/10/20/483205.aspx.

JP3 – Excel 2007 comes with a series of new fuctions that allow you to create free-form reports against multi-dimensional data sources.  See this post and the next four for details: http://blogs.msdn.com/excel/archive/2005/10/20/483205.aspx

Dave

Well you asked. I am completely redoing my back garden after removing an outdoor swimming pool (not such a good idea with UK weather). Plan to have some small fruit trees, raised beds for vegetables, and a mixture of shrubs and perennials. Familar problem though, requested features from the whole family exceed the limited space available.

Perhaps row/col size should be made consistant in a future release of Excel. But Sam's macro works just fine (thanks again Sam).

An easy solution to the metric question would be to allow users to enter pixel values in the Column Width/Row Height dialogs as follows:

User enters: 30 -> old metrics
User enters: 30px -> pixels!

Word lets users manually enter any unit of measurement they please*, even mixing various ones in a dialog.

* With the notable exception of parsecs and furlongs.