Dynamic Range

[nniedzielski]

I am running a dynamic range over the last 45 days of data in a worksheet, however some days the number entered is a Zero, for example on a Sunday. How can I use the formula below, but also have the formula remove the zeroes from the calculated average? It can consider a zero one of the 45 days, but just doesn't calculate the zero.

=AVERAGE(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)-44):INDEX(B:B,MATCH(9.99999999999999E+307,B:B)))

thank you,

 

[Jerry Sullivan]

Hi nniedzielski,

You can use the AVERAGEIF function the same way that you would if you were referencing a static range.

=AVERAGEIF(Range,"<>0")

In your example...

=AVERAGEIF(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)-44):INDEX(B:B,MATCH(9.99999999999999E+307,B:B)),"<>0")

 

[nniedzielski]

thank you