Averaging across multiple sheets

[JAMun169]

Hey everyone!
I'm just starting out with excel and I'm working on formulas. What I'm trying to do is obtain the average of one cell across multiple sheets. Someone suggested a formula (which I failed to ask what it does) and was hoping that someone here could explain it for me, if you would please.

=SUM('Day1:Day31'!D3)/INDEX(FREQUENCY('Day1:Day31'!D3,0),2)


Would it be easier to use this instead: =AVERAGE(Day1:Day31!D3)


Thanks in advance for the input

 

[Jonmo1]

Re: Question about averaging across multiple sheets

Welcome to the board.

Would it be easier to use this instead: =AVERAGE(Day1:Day31!D3)
Thanks in advance for the input

Yes, that would certainly be easier. But unfortunately it's not possible.
Day1:Day31 is a special range reference, called a 3D range.
Only certain basic functions like SUM and COUNT work with that type of reference.
Frankly I'm suprused Frequency works with it, and it's quite awesome.

Basically what it's doing is SUM/COUNT

Average = Sum / Count

So the SUM function is pretty clear..

This part is returning a count of Non 0 numbers.
INDEX(FREQUENCY('Day1:Day31'!D3,0),2)

Hope that helps.