average forumula omitting 0

[CommonCents]

i am trying to figure a formula to average numbers from 5 sheets in cell a1 but not count a value of 0.
if anyone could help it would be greatly appreciated. thank you.

[brokenbiscuits]

Presumably you have something along the lines of:

=SUM(Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1,Sheet5!A1)/5

If you change that to

=SUM(Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1,Sheet5!A1)/(SUM(IF(Sheet1!A1=0,0,1),IF(Sheet2!A1=0,0,1),IF(Sheet3!A1=0,0,1),IF(Sheet4!A1=0,0,1),IF(Sheet5!A1=0,0,1)))

that should work?

[zbor]

Name first sheet as START and last sheet as END and then use this formula: =AVERAGE(START:END!A1)

This won't calculate EMPTY cell but will calculate 0 in a cell.
That seems reasonable.

If you getting 0 and want to exclude it you can use IF statement to avoid 0 instead of blank cell in your sheets.

Check example:

[CommonCents]

=AVERAGE(IF('0103:END'!N6<>1,'0103:END'!N6,"")) that is the formula i figured but I am getting a REF error so I don't know what is wrong.

[CommonCents]

can you put that in a sheet and see if i am reffing something wrong? 0103 is my "START" sheet, and I want to omit all cells that return 1 and nothing else...seems like that should work but i'm gettinga big FAIL on that.

[zbor]

You using wrong formula...

YXou must avoid 0's at the way I've told you.

[DonkeyOte]

=AVERAGE(IF('0103:END'!N6<>1,'0103:END'!N6,"")) that is the formula i figured but I am getting a REF error so I don't know what is wrong.

You can't conduct conditional calcs along the lines of the above without introducing SUMPRODUCT, INDIRECT etc... you would need to list all sheet names for inclusion in a range for reference.
Needless to say this approach is cumbersome, inefficient & volatile

Given the above I would suggest you use an additional cell (identical) on all sheets, eg: O6 - and implement a basic formula along the lines of:

Please Login or Register  to view this content.

Then use standard 3D Average as outlined by zbor

Please Login or Register  to view this content.

[CommonCents]

now that is some good stuff..awesome thank you.