E
Edward
I need, in a summary book, to calculate a weighted average from a
remote book, each sheet of which contains monthly data.
Each monthly sheet contains a monthly average and number of items used
to calculate that average. I want to multiply the two numbers on each
sheet, sum the products across all sheets, and (easy part) divide by
the total number of items used to calculate all the averages. That is
to say, take a weighted average.
The following does not work, and I understand why. But, if it did, it
would do what I want to do.
=SUMPRODUCT('[XXXReport_AD through Nov.xls]Jul:Nov'!E$7*'[XXXReport_AD
through Nov.xls]Jul:Nov'!D$7)
If this weren't a remote reference, I would just list the names of the
sheets and use INDIRECT in the array formula. It has to be remote, not
only practically, but also for my own edification.
Anyone have any formula-based solutions, or failing that, some slick VB
code?
Thanks for any help.
Ed
remote book, each sheet of which contains monthly data.
Each monthly sheet contains a monthly average and number of items used
to calculate that average. I want to multiply the two numbers on each
sheet, sum the products across all sheets, and (easy part) divide by
the total number of items used to calculate all the averages. That is
to say, take a weighted average.
The following does not work, and I understand why. But, if it did, it
would do what I want to do.
=SUMPRODUCT('[XXXReport_AD through Nov.xls]Jul:Nov'!E$7*'[XXXReport_AD
through Nov.xls]Jul:Nov'!D$7)
If this weren't a remote reference, I would just list the names of the
sheets and use INDIRECT in the array formula. It has to be remote, not
only practically, but also for my own edification.
Anyone have any formula-based solutions, or failing that, some slick VB
code?
Thanks for any help.
Ed