Hey, Where Did Solver Go?

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