Averaging every 48 cells of only positive values

1. Averaging every 48 cells of only positive values

I can easily average up the 48 rows that are positive with something simple like =AVERAGEIF(D1:D48,">0",D1:D48). However I want it to work like my just normal average formula worked which was =AVERAGE(OFFSET(D\$1, 48 * (ROW() - 1), 0, 48)) because i have 17,520 cells in the row to calculate the average of every 48 cells only positive numbers. Any ideas or formulas that would work?

2. Re: Averaging every 48 cells of only positive values

Does that mean you want the average of:

D1:D48
D49:D96
D97:D144
D145:D192
etc
etc

If so, try this...

Entered in F1 and copied down as needed:

=AVERAGEIF(OFFSET(D\$1,ROWS(F\$1:F1)*48-1,,-48),">=0")

3. Re: Averaging every 48 cells of only positive values

That worked beautifully! I couldn't figure out how to tie the codes together, thank you so much!

4. Re: Averaging every 48 cells of only positive values

You're welcome. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)