Problem with Array formula

**andrewc** · 11-26-2008, 11:18 AM

Hi,

In Sheet1 I have a column of dates in B and values in columns F and G.

I would like to use the formula below to calculate the r-squared (using Excel's RSQ function) of the data in columns F and G between the date range starting and ending on the dates in cells L1 and L2 of Sheet2.

{=(rsq(IF('Sheet1'!$B$5:$B$500>'Sheet2'!L$1,IF('Sheet1'!$B$5:$B$500<='Sheet2'!L$2,'Sheet1'!F5:F500,'Sheet1'!G5:G500))))}

However, I get an error message, saying the I've used too few arguments. What am I doing wrong?

Thanks!

**Ron Coderre** · 11-26-2008, 11:35 AM

Since the RSQ function is looking for 2 ranges...
You may need to use this approach
(untested....we don't have your data):

=RSQ(IF((Sheet1!$B$5:$B$500>Sheet2!L$1)*(Sheet1!$B$5:$B$500<=Sheet2!L$2),
Sheet1!F5:F500),IF((Sheet1!$B$5:$B$500>Sheet2!L$1)*
(Sheet1!$B$5:$B$500<=Sheet2!L$2),Sheet1!G5:G500))

Does that help?