Saturday, January 28, 2012

yogi_Average Cells From Differents Sheets With Value Greater Than Zero


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user Squarky said:
I have spreadsheet that calculates event scores for multiple meets. I want to average these scores on a separate sheet. I know how to average all of the events, but how do you only average the events that have been completed. Meaning I want to average cells across multiple sheets that have a value greater than 0.
Current Equation:
=AVERAGE('Pineapple Classic'!E2,'Windy City'!E2,'Swiss Turner'!E2,'Iowa Boys Invite'!E2,'Great Western Invite'!E2,'Joe Giallombardo'!E2,'State Championships'!E2,Regional!E2)
-----
BTW I did try this formula without any success:
=AVERAGE(IF(N(INDIRECT("'"&{"Pineapple Classic";"Windy City";"Swiss Turner";"Iowa Boys Invite";"Great Western Invite";"Joe Giallombardo";"State Championships";"Regional"}&"'!E2"))>0,N(INDIRECT("'"&{"Pineapple Classic";"Windy City";"Swiss Turner";"Iowa Boys Invite";"Great Western Invite";"Joe Giallombardo";"State Championships";"Regional"}&"'!E2"))))
----------------------------------------
following is a solution to the problem