@herclau
This is meant to be a simple example that may help you interpret what is going on in the solution I offered to your problem.

The task is to calculate the geometric mean of corresponding values in two arrays A and B. There is a worksheet function for geometric means (GEOMEAN) but it will take all eight values and aggregate to give a single result.

First I used 'Formulas / Create from Selection' to generate formulas

**A** and

**B** that reference the two arrays. The column

**P** contains the formula

**{= A * B}**
Having generated the pairwise products, I introduced the name 'P' to reference the result. The product is then square-rooted to give the array of geometric means '

**√P**' using the formula

**{= SQRT(P)}**
In order to remove the helper range 'P', I can redefine the name

**P** to hold the formula

**= SQRT(P)**
by cutting and pasting it from the worksheet into the 'Refers to' box. Once that is done, the values in column P no longer have dependents and may be deleted. To evaluate the values

**√P** Excel will first invoke the formula

**P** (you can see this using 'Evaluate Formula') and 'Stepping In' allows you to

follow its definition and subsequent evaluation in terms of

**A** and

**B**.

**Why do I do this**? I use named arrays because it is at this level I am likely to be able to attribute significance to the data in terms of the application domain. The location of the data on a worksheet is arbitrary and it is an irrelevant attribute of the date when it comes to calculation.

**How common is the approach**? You can get a feel for that from a comment made by Mike (

*excelisfun*) Girvin:

"

* I have never, in almost 3 decades, seen a spreadsheet like yours with ALL the calculations entered as arrays!!! *

Now I see why your are so excited by the new Excel Calculation Engine"

**What are the limitations?** If the data is record-based, with data being constantly added, the incremental calculation offered by Tables and Pivot Tables would be far more appropriate and efficient. The methods I use are more suited to compact, calculation-heavy models where it is likely that a data change would require a complete reevaluation of the problem.