moving average with differing bin sizes

Hello forum!

I am trying to calculate a moving average, but im having issues because its not always the average of the same number of values; this changes. So.. I want the average of 1 column (e.g. column A below) to be calculated based on whether they are in the categories 0-50, 25-75 etc (listed in columns D and E below) from another column (e.g. column B below) and ending up in a nice neat column (e.g. G below). To add an extra layer of complexity there are a handful of missing data points (denotes as '.' below), so simply sumif(...)/(countif(...) wont work because it will 'sumif' ignoring the blanks in col A, and 'countif' if there is data present in col B, e.g. in the category 50-100 below the average is 20/3 not 20/5. (or maybe this latter step can be fixed?). This little dataset is also attached as a .xlsx

(Also there are several thousand datapoints so I dont want to manually make a couple of hundred 'average' calculations!)

Any help would be gratefully appreciated!!!!

A B D E G
2 2 0 50 4.625
3 11 25 75 6
6 12 50 100 6.666
7 12
5 14
4 23
3 34
7 45
6 51
. 55
. 55
8 70
6 86

Are blank rows included in determining where your averages start and stop.
For example, if you have data in A2:A101, with a scattering of blank cells, Does 50-100 start in A51 or it depends on how many blanks and where they are?

ExcelHelp.xlsx

Not the most elegant but it works and it's fast

Didn't look at Andy's yet but this works

=SUMPRODUCT(--($B$2:$B$14>=D2),--($B$2:$B$14<=E2),$A$2:$A$14)/SUMPRODUCT(--($B$2:$B$14>=D2),--($B$2:$B$14<=E2),--($A$2:$A$14<>""))

Note to self - get a handle on Sumproduct

Originally Posted by AndyLitch

Note to self - get a handle on Sumproduct

Here's a good place to start:

http://xldynamic.com/source/xld.SUMPRODUCT.html