@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.