Sliding window analysis

[biologist]

First, thanks to everyone who shares their knowledge on this forum. I have been a lurker for the past couple years and have found answers to all of my questions without having to post, but this one has me stumped.

I am trying to calculate a sliding window average for a biological dataset. The dataset has 3 columns: A-chromosome, B=location (on the chromosome), C=ratio. Ratio is the dependent variable for which I would like to calculate moving averages/sliding window averages. Here is a subset of the data:

A B C
chromosome location ratio
1, 0,
1, 0, 0.01
1, 11.9,
1, 11.9, 0.23
1, 15.5,
1, 15.5, 0.13
1, 19.1,
1, 19.1, 0.22

I have set up four additional columns to produce the sliding average: D=chr, E=lower bound, F=upper bound, G=average. Here is a sample of the table:

D E F G
chr, lower, upper, average
1, 0, 9, 0.307
1, 1, 10, 0
1, 2, 11, 0.307
1, 3, 12, 0.307
1, 4, 13, 0
1, 5, 14, 0
1, 6, 15, 0
1, 7, 16, 0

So what I am trying to do is take an average for the specified bin, then move 1 unit and recalculate the average. I'm basically just trying to smooth the data for graphing. As you can see, the location units are not evenly distributed and some have multiple measurements for the same location, so I cannot use a simple moving average formula. I have looked through old threads and found a couple formulas that I thought would work. Here they are:

=AVERAGE(IF((chromosome=D2)*(location>=E2)*(location<=F2), ratio))
When I enter this formula, excel won't allow me to set it as an array. If I enter it as a non-array, it returns an average for the entire set (see above table). And I get that, its just asking whether the data in columns A-C meet the criteria listed in D-F for the same row. If the criteria are met, it returns the ratio average for the whole set.

Next I tried:
=SUMPRODUCT(--(chromosome=D2),--(location>=E2),--(location<=F2),ratio)/SUMPRODUCT(--(chromosome=D2),--(location>=E2),--(location<=F2))
I get an error message when I enter this formula, but I have no idea what I'm doing wrong.

At this point, I'm convinced that I'm making some stupid little error, but I have been at this for a few days now so I figured its time to ask for help.

Thanks in advance