averages across multiple sheets with non numerical names

[obeo2k2]

hi

i am working on a sheet that will enable accurate tracking of a teams daily output but am struggling with the averages side of things.

i have attached the file below and left the formulas i already have in place in the hope someone can help me out.

working the weekly average is easy enough but i cant figure out how to calculate a daily average if the value in the specified cells is greater then 0.

the first formula i tried was

[HTML]=SUM('Team Member 1:Team Member 10'!B6)/COUNTIF('Team Member 1:Team Member 10'!B6,">0")[HTML]

but this just gives me a value error.

the other issue i have is that the sheet names will change from team member 1 etc to the actual persons name so therfore team member 1:team member 10 formulas will not work.

any help would be greatly appreciated

Ian

[Bob Phillips]

Can't see any formulae or any sheets to average, but just use

=AVERAGE('Team Member 1:Team Member 10'!B6)

[obeo2k2]

appologies for being a total moron!! i attached the wrong file.

correct file is now in the original post

[Bob Phillips]

My reply still holds.

[obeo2k2]

My reply still holds.

i have tried this formula but i need the formula to only count cells that have a value greater then 0 otherwise it will not give a true average.

[Bob Phillips]

You could use this

=SUM('Team Member 1:Team Member 10'!B6)/SUMPRODUCT(COUNTIF(INDIRECT("'Team Member "&ROW(INDIRECT("1:10"))&"'!B6"),">0"))

but you would need to be consistent with the sheet names, yor 6 has a trailing space.

[obeo2k2]

thanks for your reply, sorry for the delay in getting back to you with thanks.

the formula u gave me worked a treat