Hey Guys,
I have an excel which calculates average from multiple sheets with a criteria. Now I am trying to calculate it with person name. I have attached the excel sheet for your reference. How can I fix it?
Thanks.
Udhay
Hey Guys,
I have an excel which calculates average from multiple sheets with a criteria. Now I am trying to calculate it with person name. I have attached the excel sheet for your reference. How can I fix it?
Thanks.
Udhay
Re: 3D reference to calculate multiple sheets
Hi.
In cell C3 of the SubjectWise sheet:
=SUMPRODUCT(SUMIF(INDIRECT("'"&SHEETNAMES&"'!B9:B58"),$B3,OFFSET(INDIRECT("'"&SHEETNAMES&"'!B9:B58"),,MMULT({1,2,3,4,5,6,7,8,9,10,11,12},N(T(OFFSET(INDIRECT("'"&SHEETNAMES&"'!B7"),,{1;2;3;4;5;6;7;8;9;10;11;12}))=C$2)))))/SUMPRODUCT(COUNTIF(INDIRECT("'"&SHEETNAMES&"'!B9:B58"),$B3))
Note that the denominator in this expression is simply the number of sheets from SHEETNAMES in which the referenced name is present, and does not count the number of sheets from SHEETNAMES in which the referenced name is present and the score for that particular subject is not blank. For example, if Daniel Donaldson had, for Biology, a score of 50 in the 1st sheet and 60 in the 2nd sheet, though his score for that subject in the 3rd sheet was blank, his average would be given by (50+60)/3, and not (50+60)/2.
Also note that, due to your using Excel 2003, in which there is a limit on the amount of functions which can be "nested" within a single formula, I decided to "hard-code" some constants in this construction, which otherwise I would normally make dynamic (thus offering greater flexibility). Hence the two array constants with integers from 1 to 12 are derived from the 12 columns (C to N) over which these calculations are taken: if at some point this number of columns changes, then this will mean that these constants will themselves require amending appropriately.
However, the nesting limit in 2003 can be circumvented by simply storing some parts of the formula within Name Manager, so if you feel that the number of columns is likely to be changing quite frequently, and so you would prefer a dynamic construction which would not require you manually amending those constants every time you create an additional subject column, let me know and I will make the necessary changes.
Regards
Advanced Excel Techniques: http://excelxor.com/
Re: 3D reference to calculate multiple sheets
XOR LX,
Thank you for giving elaborated quote. I am using Excel 2007. I am going to give a try with this.
Regards,
Udhay
Re: 3D reference to calculate multiple sheets
Ah! But the sheet you attached was a 2003 sheet!
Regards
Re: 3D reference to calculate multiple sheets
XOR LX,
It works.
Thank you for your kind help for the thing which I was hitting my head against the wall for couple of hours.
Thank you so much!!!!
Re: 3D reference to calculate multiple sheets
You're welcome! Cheers!
Don’t have an account yet? Register yourself now and be a part of our community!