Finding an average across multiple sheets but excluding 0

[alexonfire]

Hi everyone,

Please can someone help me. I'm basically trying to find the average across multiple sheets but removing any zeros from the average calculation. I've tried a few things but can't seem to get it to work.

Here's one of my many attempts:

=AVERAGEIF('WK1 - 23 10 TO 29 10'!L18,'WK2 - 30 10 TO 05 11 '!L17,'WK3 - 06 11 TO 12 11 '!L17,'WK4 - 13 11 TO 19 11'!L17,'WK5 - 20 11 TO 26 11'!L17,"<>,0.00")

Excel tells me there are too many arguments for this to work. Can anyone help?

Thank you,
A

[Pepe Le Mokko]

Perhaps remove the comma from "<>,0.00" to "<>0.00"

[alexonfire]

Thank you for the reply. I just tried that and unfortunately the same message pops up telling me it's too many arguments...

[Pepe Le Mokko]

I think you need the AVRERAGIFS function

[alexonfire]

Hi there,
Thanks again! I really appreciate you trying to help. I've been trying to solve this for hours.

Just tried the following:

=AVERAGEIFS('WK1 - 23 10 TO 29 10'!L18,'WK2 - 30 10 TO 05 11 '!L17,'WK3 - 06 11 TO 12 11 '!L17,'WK4 - 13 11 TO 19 11'!L17,'WK5 - 20 11 TO 26 11'!L17,"<>,0.00")

=AVERAGEIFS('WK1 - 23 10 TO 29 10'!L18,'WK2 - 30 10 TO 05 11 '!L17,'WK3 - 06 11 TO 12 11 '!L17,'WK4 - 13 11 TO 19 11'!L17,'WK5 - 20 11 TO 26 11'!L17,"<>0.00")

Still the same dreaded too many arguments message.

[Pepe Le Mokko]

Which XL version are you using?

[alexonfire]

Excel version - I'm not actually sure, sorry, not the most savvy. How do I find out?

[alexonfire]

Here's the other things i've tried to no avail also:

=AVERAGEIFS('WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18,'WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18,">0",'WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18,"<>""")

=SUM('WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18)/COUNTIF('WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18,"<>0")


=AVERAGE(IF('WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18<>0,'WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18))

=SUM('WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18)/(COUNTIF('WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18,"<>0")-COUNTBLANK('WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18)-
(COUNTA('WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18)-COUNT('WK1 - 23 10 TO 29 10:WK5 - 20 11 TO 26 11'!L18)))

I feel like I may go mad with this

[MrShorty]

The functionIFS() functions seem to not support 3D references. When I see this kind of question, I tend to see two possible approaches.
1) Do the condition part in a helper cell on each tab. =IF(L18<>0,L18,"") in any suitable helper cell. Then, on the summary tab, I can use =AVERAGE(sheet1:sheet10!helpercell).

or
2) compile all of the data into a single, good database. Then the summary functions like AVERAGEIFS() (or even pivot tables and similar tools) become much more effective.

Since this sort of thing really often boils down to good database management. As such, the 2nd option tends to be the better choice if you are willing to invest the time upfront. Make a good database, and future work with the data will be much easier.

[alexonfire]

The functionIFS() functions seem to not support 3D references. When I see this kind of question, I tend to see two possible approaches.
1) Do the condition part in a helper cell on each tab. =IF(L18<>0,L18,"") in any suitable helper cell. Then, on the summary tab, I can use =AVERAGE(sheet1:sheet10!helpercell).

or
2) compile all of the data into a single, good database. Then the summary functions like AVERAGEIFS() (or even pivot tables and similar tools) become much more effective.

Since this sort of thing really often boils down to good database management. As such, the 2nd option tends to be the better choice if you are willing to invest the time upfront. Make a good database, and future work with the data will be much easier.

Thank you for this. I've created an extra sheet which pulls the data together and this appears to have worked. Thanks again!