Averaging across worksheets

[dernspiker]

I am calculating an average for a teams quality assurance numbers. They are
not all audited on each item every week. But all items are included on the
worksheets.

I have about 10 worksheets I need to draw averages from and have them
calculate on a "TOTALS" worksheet. These worksheets have "updated" info on
them once a week. Sometimes the data is blank or "0". How can I get an
average without the blanks and/or zeros counting against the team member when
certain items weren't audited?

Thans for your help in advanced.

Dana

 

[Mike H]

Hi,

Conditional averages across multiple worksheets can be tricky and you dont
say where the data are you want to average and this may or may not work
depensing on how you worksheets are named. This assumes you have 10 sheets
called Sheet1 to Sheet 10 and it will then average A1 in those sheets
ignoring blanks and zero

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:10"))&"'!A1"),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'sheet"&ROW(INDIRECT("1:10"))&"'!A1"),">0"))

Mike