I am using AverageIf to link to another workbook. I've read that the "if" family do not like source workbooks to be closed, so I was going to modify formulas to somehow use SumProduct, when I noticed that half of the instances of the formula did return results, while others returned #Value. Has anyone else noticed this happening? Why would it happen? I use Excel 2010 (14.0.7135.5000) SP2 MSO (14.0.7128.5000) Part of MS Office Professional Plus 2010
is displaying last saved result upon file open:
=$D42*AVERAGEIF('G:\source path\[file.xlsx]tab'!$A$2:$A$87,$B$39,'G:\source path\[file.xlsx]tab'!D$2:D$87)
is returning #value upon file open:
=$D72*AVERAGEIF('G:\source path\[file.xlsx]tab'!$A$2:$A$87,$B$69,'G:\source path\[file.xlsx]tab'!D$2:D$87)
is displaying last saved result upon file open:
=$D42*AVERAGEIF('G:\source path\[file.xlsx]tab'!$A$2:$A$87,$B$39,'G:\source path\[file.xlsx]tab'!D$2:D$87)
is returning #value upon file open:
=$D72*AVERAGEIF('G:\source path\[file.xlsx]tab'!$A$2:$A$87,$B$69,'G:\source path\[file.xlsx]tab'!D$2:D$87)