REval

Syntax: REVAL(command,symbol01,matrix01,symbol02,matrix02,symbol03,matrix03,symbol04,matrix04,symbol05,matrix05,symbol06,matrix06,symbol07,matrix07,

symbol08,matrix08,symbol09,matrix09,symbol10,matrix10,autoFormat)

Equivalent Function Names: RE

Purpose: Calls the R Engine on the specified command using previously or in this formula specified symbols and presents the results back.

NOTE: the named symbols are global for a given Excel session.

Parameters:

1.Command - An R command.

If a command is missing, the result will be a silent execution without any output of the assignment and commands in the symbolXX, matrixXX

2.symbolXX, matrixXX - can be omitted.

If symbolXX is specified and is a range, it's assumed that the symboldXX contains a number of R commands to be executed and the matrixXX is ignored.

If symbolXX is specified and matrixXX not, it's assumed that the symboldXX contains a definition of a matrix, e.g. y<-21:23, or a command. That way you can specify in one RE function up to 11 commands!

If both symbolXX and matrixXX are specified, the Excel Range specified in matrixXX is defined as the symbol symbolXX.

Matrix can be specified as a range with empty cells, e.g. the entire column. In that case, the size of the array is determined by the last non-empty cells with empty cells playing the role of separators.

3.AutoFormat - an optional flag signifying if the result should be automatically converted to a range and if the result should be automatically formatted as Excel Data Types

Result of the function:

The result of the function is the result of the Command. If the symbolXX a command, its result is NOT displayed -> the symbolXX are to be used largely for assignments.

Examples:

1. Quickly generate a column

=RE("18:29") generates a column of all values between 18 and 29. As the function works with AutoRange, you do not need to worry about the size of the output vector!

2. Matrix addition

=RE("x+y","x",B1:C2,"y",B5:C6) adds two matrices

3. Define a matrix and multiply it with a scalar in one command

=RE("y*2","y<-21:23")

4. Declaration of global Excel R name and its reuse in another cell

In one cell you define the R Name =RE("x<-8:29") and in another you reuse it =RE("x+2")

5. Running a statistical test on two data sets

=RE("t.test(group1, group2)","group1<-c(30.02, 29.99, 30.11, 29.97, 30.01, 29.99)",,"group2 <- c(29.89, 29.93, 29.72, 29.98, 30.02, 29.98)")

6. Getting an average of a vector

=RE("mean(x)","x <-1:1000")

7. Drawing of the normal curve using Excel charts

=RE("dnorm(x, 0, 1)","x <- seq(-3, 3, 0.01)")

8. Drawing of the normal curve using R native chart plotter

=RE("plot(dnorm(x, 0, 1))","x <- seq(-3, 3, 0.01)") shows the same chart using R plotter. In general, when working with R, R plotter is faster while Excel plotter is much fancier.

9. Running of a linear regression and displaying of the full result

=RE("lm(x~x)","x<-1:23")

10. Running of a linear regression and displaying only of the effects from the regression results

=RE("lm(x~x)$effects","x<-1:23")

11. Silently assign a range to a variable matrix - no need to clutter your Excel sheet!

=RE(,"X",C1:C5) defines silently the X variable (without printing out its value)

then use it =RE("X+9")

12. Passing a to R a range with commands - one command per cell, for example.

=RE(A1:A2) shows the chart of the commands specified in the cells A1:A2:

plot(x <- sort(rnorm(47)), type = "s", main = "plot(x, type = \"s\")")

points(x, cex = .5, col = "dark red")

Copyright © 2013-2017 Jiri Pik |
Document Version: Sunday, May 7, 2017 |