At Main2.xlsx worksheet I have three formulas which are referencing another worksheet.
Two of them which are highlighted in yellow (AVERAGE(LARGE(IF)) and SUM are working fine with the reference worksheet (Book2.xlsx) closed.
The AVERAGEIFS formula in red is NOT working with the reference worksheet closed, it is working only when the reference worksheet is opened at the same time with the Main2.xlsx. I get the error #VALUE.
Does anybody know what might be happening?
Thanks!
Averageifs and similar functions don't work with closed workbooks.. you will need to use SUMPRODUCT:
add paths between the apostrophe (') and the [Book2.xlsx]
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
great. Thanks NBVC. Also the =MAX formula does not work.... is there any other formula that works as the MAX with closed workbooks?
the MAX function does work with closed workbooks, but if you have any of the __IF functions, like Countif, Sumif, Averageif, Countifs, Sumifs, Averageifs, nested in the formula then it won't work.
I am having CPU issue using =SUMPRODUCT instead of AVERAGEIFS. It is taking much more time to calculate it and sometimes it gets stuck when saving due to the high number of this formula in my spreadsheet. Is there another alternative besides =SUMPRODUCT?
You can do the Averageifs in the "closed" workbook, then simply reference that cell in your Open book.
ok tks NBVC! Closing the thread.
Hi, how to insert the sumproduct in this formula? =IFERROR(ROUND(AVERAGEIFS('[Book1.xlsx]Sheet1'!$F:$F,'[Book1.xlsx]Sheet1'!$K:$K,D27,'[Book1.xlsx]Sheet1'!$G:$G,$B$29)*60,1), " ")
Thanks.
dummy777,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ben Van Johnson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks