+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    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!

  2. #2
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    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.

  3. #3
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

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

    Can anyone help me please?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1