Hello
I'm new to the forum and excel. I'm using excel 2007. I need help on getting an average from multiple sheets into a sheet.
I've named the multiple sheets as WA to WA(12). and i would like to get the average from these sheets to a sheet called LE15X in the column weekly assessment. i've succeded by using this formula =AVERAGE('WA:WA (12)'!I11). My problem is, how can i get the average by ommiting the value zero(0) or blank in any the WA to WA(12) sheets.
With this I attached the sample Data. Any help is very much appreciated. TQ.
Create a list of all the sheets from WA to WA(12) in a separate area somewhere, and name that range, say SheetNames, by selecting the range and typing the name in the Name Box, just to the left of the Formula Bar.
Then use formula:
copied down.=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!I"&ROW(I11)),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!I"&ROW(I11)),">0")),"")
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks