Average in Multiple Sheets

[Paiwand]

Hello Dear.

I have an workbook which is consist of 13 sheets, starts form (Jan,Feb,Mar,...Dec) plus (Annual), at the Annual Sheet i would like to getting an cell's average which is exist in all sheets and its (D17) the average should exclude and unconsidered zero and blank. i have used the below formula but it doesn't work.

=AVERAGEIFS(Jan!D17;Feb!D17;Mar!D17;Apr!D17;May!D17;Jun!D17;Jul!D17;Aug!D17;Sep!D17;Oct!D17;Nov!D17;Dec!D17;"0%")

Please your kind help.

Best regards.

 

[AlphaFrog]

Try this...

=AVERAGE(Jan:Dec!D17)

 

[Paiwand]

it seems work but its also consider 0 ! i want to exclude 0.

thanks a million for your prompt reply.

 

[Paiwand]

Still i'm waiting for reply :/ please help to exclude zero while average the above.

 

[AlphaFrog]

If there are formulas in D17, have the formulas return a null string instead of a zero.
=IF(your_formula = 0, "", your_formula)

If D17 is just a numeric value, perhaps put a formula in another cell IF(D17=0,"",D17) then average those values.

 

[Aladin Akyurek]

Try...

=SUM(Jan:Dec!D17)/INDEX(FREQUENCY(Jan:Dec!D17,0),2)

 

[shg]

=SUM(Jan:Dec!D17)/INDEX(FREQUENCY(Jan:Dec!D17,0),2)

That's very clever.

 

[dwgnome]

Hi,

I am using a similar construct

=[COLOR=#0000cd]SUM(First:Last!$C$10:$C$33)/MAX(1,INDEX(FREQUENCY(First:Last!$C$10:$C$33,0),2)[/COLOR])

It works until one of the cells has an error in it #DIV/0! Is there a way to modify this formula to ignore errors. I tried using AGGREGATE instead of SUM, but that did not work.

 

[Aladin Akyurek]

Hi,

I am using a similar construct

=[COLOR=#0000cd]SUM(First:Last!$C$10:$C$33)/MAX(1,INDEX(FREQUENCY(First:Last!$C$10:$C$33,0),2)[/COLOR])

It works until one of the cells has an error in it #DIV/0! Is there a way to modify this formula to ignore errors. I tried using AGGREGATE instead of SUM, but that did not work.

Can't you rewrite the formula which causes this error?

 

[dwgnome]

Although I generally know how to avoid such errors, unfortunately in this case, I can't do that as the sheet is password protected.