# Excel 2007 - Referencing another workbook in AVERAGE IF Formula gets Excel in loop

1. ## Excel 2007 - Referencing another workbook in AVERAGE IF Formula gets Excel in loop

Hello,

I have two Excel workbooks:

The first one is a workbook with 894571 rows which is the input for the second other workbook where I have three AVERAGE IFs formulas similar to the below:

=IFERROR(AVERAGE(LARGE(IF(([D2013M1_v2.xlsb]D2013M1!\$H\$2:\$H\$894751=\$C\$2)*([D2013M1_v2.xlsb]D2013M1!\$C\$2:\$C\$894751=G\$4)*([D2013M1_v2.xlsb]D2013M1!\$D\$2:\$D\$894751=G\$5)*([D2013M1_v2.xlsb]D2013M1!\$M\$2:\$M\$894751=\$B\$2)*([D2013M1_v2.xlsb]D2013M1!\$O\$2:\$O\$894751=\$B8)*([D2013M1_v2.xlsb]D2013M1!\$G\$2:\$G\$894751=\$A\$2),[D2013M1_v2.xlsb]D2013M1!\$AL\$2:\$AL\$894751),{1,2,3,4,5,6,7,8,9,10})),"N/A")

The issue is that, whenever I try to open the workbook with the formulas, it gets Excel stuck, like on a infinite loop, I have not counted how many minutes or even hours it gets to calculate the three formulas, because I did not wait so long.
Now, whenever I open the input file first and then I open the workbook with the formula it opens fine and more quickly, it takes no longer than 5 min. But, on this case, I am unable to save the workbook with the formula, as it also gets Excel stuck.

Has anybody already faced this kind of issue when referencing in the formulas other workbooks? Is there anything to fix that? I am with no patience as I tried many things, waited many minutes and I am still facing this issue.

Thanks!  Register To Reply

2. ## Re: Excel 2007 - Referencing another workbook in AVERAGE IF Formula gets Excel in loop

When I include de input data and formulas on a unique workbook, it is pretty fast... The issue is when the data is outside of the formula workbook.... it takes so long to open and calculate the formulas.  Register To Reply

3. ## Re: Excel 2007 - Referencing another workbook in AVERAGE IF Formula gets Excel in loop  Register To Reply