[R] Excel

[Erich Neuwirth]

Greg Snow wrote:
> >
> > Or do you trust all of your clients to know to use R(D)COM as well as
> > how to install and use it?

Do you trust your clients to be fluent enough in R to use it?
For most of my clients, that is not true.
For this kind of users, the following strategy works.
They have their data in Excel, and I write some macros in Excel which
use RExcel to compute results and put them in the sheet.
The clients press buttons similar to what they would do
with the Analysis toolpack in Excel, but they get the full power of
R.

> >
>> >> The formula
>> >> =RApply("var",A1:A1000) in an Excel cell for example will use
>> >> R to compute the variance of the data in column A in Excel.
>> >> If you change any of the values in the range A1:A1000 will
>> >> automatically recompute the variance.
> >
> > And what happens when you enter a value in cell A1001?

If you use the following formula
=RApply("var",DownFrom(A1))

where DownFrom ist he following function defined in VBA

Function DownFrom(startcell As Range) As Range
    Application.Volatile
    Set DownFrom = Range(startcell, startcell.End(xlDown))
End Function

Then writing a value in A1001 will a extend the range to which the
function "var" is applied.

Arguments of worksheet function can be dynamic ranges, but sadly this is
a relatively unknown fact.


> >
> > And what happens if you set cell B1 to =A1 and do the magic copy so that
> > b2=a2, b3=a3, ... Then put
> > =Rapply("var",B1:B1000) in a cell, does the answer match with
> > =Rapply("var",A1:A1000)?

Of course it does, what else would you expect to happen?
and if you copy the the formula in the cell containing
=Rapply("var",A1:A1000) into the cell to its right,
this cell it will automatically contain the formula
=Rapply("var",B1:B1000)

> >
> > Yes, the auto-recompute could be considered a nice feature, but does it
> > really save that much work compared to running a script in R after
> > updating the data? (a couple of clicks in Rgui, a couple of keystrokes
> > in ESS, one line of code in an R terminal)

Auto-recomputing can do more.
My favorite demo is the display of a kernel density estimator.
You move a slider controlling the window width. The slider controls
the value in a cell. When the cell contents change, R recomputes the
kernel density estimator in some Excel cells and Excel then
updates the graph. So you have an animated display in Excel
which allows you to instantly see the changes in the graph.
This is user controlled animation. It can be done using for example by
Tcl/Tk also, but it is much more work.


> >
>> >> There is one feature in Excel which is extremely convenient,
>> >> Pivot tables. Anybody doing any work as statistical
>> >> consultant really ought to know about Pivot tables, and I am
>> >> still surprised how many statisticians do not know about it.
>> >> Neither Gnumeric nor OpenOffice Calc offer comparably
>> >> convenient ways working with multidimensional tables.
> >
> > I will admit that I have not learned the power of the pivot table, but
> > the main reason for that is everytime someone demonstrates the power of
> > the pivot table to me it is by creating a table, then showing that if
> > that is not what you want you can click here, here, here, then here and
> > it is a different table, then you click here and here, ....  At which
> > point I am completely lost as to which clicks did which.  There are
> > tools in R (the reshape package has been mentioned) than give you the
> > power to create the tables, but with a history rather than having to
> > remember which clicks are needed.  My experience with pivot tables so
> > far are that they are a post-hoc kludge to a poorly designed original
> > table.  I prefer working with a script where if the result is not what I
> > want, I can fix the source rather than admire how pretty the band-aid
> > is.  (Maybe there are ways to script a pivot table without the clicking,
> > but everyone who has tried to market them to me have been more impressed
> > with the band-aid than in preventing the original injury).
> >
> > What happens when you make a nice pivot table for a client, then they
> > come back 2 months latter with a new spreadsheet and want a similar
> > table for this data?  I don't think I could remember the set of clicks
> > needed, but with an R script I can pull up the set of commands I used
> > before, change the variable and table names, and have the results in a
> > couple of minutes.

One you are used to the mechanics of creating pivot tables, it will be
extremely easy to create a similar table just by having a quick look at
the original table.

Of course it is possible to design terrible spreadsheets.
But it is also quite possible to write terrible R code.

What makes spreadsheets very special is autorecalculation and
autoupdating of charts, but also the point-and-click method of creating
formulas (using relative and absolute references).

And of course, knowing one tool always is dangerous, but this is also
true in the case where the single known tool is R.


-- Erich Neuwirth, University of Vienna Faculty of Computer Science
Computer Supported Didactics Working Group Visit our SunSITE at
http://sunsite.univie.ac.at Phone: +43-1-4277-39464 Fax: +43-1-4277-39459
-- 
Erich Neuwirth, University of Vienna
Faculty of Computer Science
Computer Supported Didactics Working Group
Visit our SunSITE at http://sunsite.univie.ac.at
Phone: +43-1-4277-39464 Fax: +43-1-4277-39459