Averaging across worksheets

D

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top