Evaluate Formula shows brackets {}

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

I am attempting to use the column() function to identify ranges inside the
correl function and get #N/A in the results. The formula is
=CORREL(OFFSET(A1,87,COLUMN(),3,1),B87:B90).

When it show the calculation steps, the evaluation for the column function
show {} around the result and the offset function evaluates as #VALUE!. I
can't figure out what the {} are supposed to represent. The column function
is calculated correctly but doesn't seem to get used in the offset function.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...e-71b654e57b91&dg=microsoft.public.excel.misc
 
The curly braces { } indicate an array (vector) of values as opposed
to a single value (scalar). Your OFFSET function returns an array of
three values, which is then passed to CORREL. Are you sure you want
COLUMN() and not COLUMN() -1 ? The value expected by OFFSET is
0-based, so OFFSET(A1,0,0) is the same as A1. So, it you have
OFFSET(A1,87,COLUMN()) in, say, C1, COLUMN() will return 3, so the
OFFSET returns values from column D, not column C. Similarly, the
offset 87 from A1 returns the value from row 88, not 87.

As written, it seems your formula is taking the CORREL using the same
range for both arrays, B87:B90. Note that this is 4 cells while you
are using 3 cells in the OFFSET function.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
In addition...

Try forcing the COLUMN() array to a scalar:

=CORREL(OFFSET(A1,86,MAX(COLUMN())-1,4,1),B87:B90)
 
Back
Top