Finding the average across multiple sheets
Biff wrote...
That said, if all the values were positive *and* there were only one
entry matching A1 in each other worksheets' A3:A30 range, then this
should work (at least it works in my tests).
Yes, it works under those conditions, where there is only a single instance
(or none) per sheet. I was working on a solution in which there were
multiple instances per sheet. How could that be done? Nothing I've tried
works. The difficult part is the count.
....
There are times it's a PITA that Excel really isn't a 3D spreadsheet.
The simplest solution would be using Laurent Longre's MOREFUNC.XLL
add-in, specifically, using that add-in's THREED function.
=SUMPRODUCT(--(THREED(A:Z!$A$4:$A$30)=A4),THREED(A:Z!$D$4:$D$30) )
/SUMPRODUCT((THREED(A:Z!$A$4:$A$30)=A4)*(THREED(A:Z !$D$4:$D$30)0))
Using only built-in functions, it gets very ugly, but it's possible. If
WSL, the named list of worksheets to process, were a horizontal array,
=SUMPRODUCT(
--(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4),
N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIRECT ("4:30"))-4,0,1,1))
)/SUMPRODUCT(
(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4)*
(N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIREC T("4:30"))-4,0,1,1))0)
)
|