multi worksheet average formula excluding zeros

[ofwhisenant]

I need a formula that will average numbers from five different worksheets. For example cell x11 on sheets 1-5. Also I need it to not include values of zero when it takes the average. Please help I cant seem to figure it out.

 

[delaneyjm]

If you are using the same cell across all sheets, try something like this:

=AVERAGE(Sheet1:Sheet5!X11)

 

[ofwhisenant]

=AVERAGE(Sheet1:Sheet5!X11) dosn't seem to work. I tired that and it gives me #VALUE!.

 

[Aladin Akyurek]

I need a formula that will average numbers from five different worksheets. For example cell x11 on sheets 1-5. Also I need it to not include values of zero when it takes the average. Please help I cant seem to figure it out.

Two ways...

1)

=SUM(Sheet1:Sheet5!X11)/INDEX(FREQUENCY(Sheet1:Sheet5!X11,0),2)

2)

Create a range housing the names of the relevant sheets and name this range SheetList, then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!X11"),">0"))/MAX(1,SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!X11"),">0")))

The first option is faster...

 

[Dryver14]

the formula should work are your sheets called sheet1 sheet2 etc?

Also what version of excel are you using as you may be able to use averageif if you have 2007 or later

To ensure you get the sheet names right when placing the formula on sheet1 press the shift button and click on the last sheet before selecting X11 this will get the sheet names right