On this webpage, we show how to extend the Real Statistics Data Table capability from one to two variables. This is similar to Excel’s two-variable Data Table facility, except that the Real Statistics version is accomplished using worksheet functions.
Example
Example 1: Repeat Example 1 of Data Table with Two Variables using the Real Statistics EVALXY worksheet function, the two-variable version of the EVALX function.
This time, we need to create a two-variable Data Table for f(x,α) = the pdf of the gamma distribution when β = 2 for the x values in F3:F19 and alpha values in G2:L2 in Figure 1.
Figure 1 – Data Table with two variables
This is done by inserting the array formula =EVALXY(B5,B3,B6) in range G3:L19.
Worksheet Function
Real Statistics Function: The Real Statistics Resource Pack provides the following array function where Rx is a cell address that serves as the placeholder for the variable x, Ry is a cell address that serves as the placeholder for the variable y, and Rf is a cell that contains a numeric formula f(x,y) in two variables.
Ri is an optional argument that is the address of a cell containing an intermediate formula. Note that the formula f(x,y) in Rf can make reference to Rx and R1 (and any cells that don’t make reference to Rx, R1, or Rf, directly or indirectly). The formula g(x) in R1 can make reference to Rx (and any cells that don’t make reference to Rx, R1, or Rf, directly or indirectly).
EVALXY(Rx, Ry, Rf, R1): fills the highlighted range with the values f(a,b) for all the values a in the column range to the immediate left of the highlighted range and all the values b in the row range immediately above the highlighted range.
Non-array version
There is also the following non-array version of the EVALXY function, called EVAL2.
Real Statistics Function: The Real Statistics Resource Pack provides the following array function where Rx, Ry, Rf, and R1 are as for EVALXY.
EVAL2(x, y, Rx, Ry, Rf, R1): returns f(x,y) for the specified values of x and y.
For example, for the spreadsheet in Figure 1, the formula =EVAL2(5,3,B5,B3,B6) returns the value 0.128258. This is the value of cell L13.
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
Reference
Cheusheva, S. (2023) How to make and use a data table in Excel.
https://www.ablebits.com/office-addins-blog/data-table-excel-one-variable-two-variable/