Hi,

I have been grappling with this question for some time now wondering if anyone can help...

I am attempting to calculate an average percentage whos range will be moving one cell up every month upon update.

If you look at the attached spreadsheet, columns A and B start counting up from 0 as soon as a valid month is entered. (formula for this excluded)

I am using columns A and B, and row 3, in a SumProduct function in order to get the numerator of a calculation that will arrive at the average.

The sumproduct function is entered in rows 43 and 44.

The function is as follows for cell F43:

=(SUMPRODUCT(($A6:$A39<M3)*($B6:$B39>=G3)*(F6:F39)))/
MIN((M3-G3),(COUNTIF(F6:F39,">0")))


The numerator of this formula uses the adjusting numbers in columns A and B to always arrive at the sum of the last 6 months of data points. So for column F, I am receiving in the numerator the sum for data points for F12:F17, which is the range between the numbers 3 and 8.

My issue revolves around the denominator. To get an accurate average, I need to divide the numerator by 4. I am instead dividing it by 6. (MIN((M3-G3),(COUNTIF(F6:F39,">0"))). = MIN(6,10) (using Min and CountIf for a later issue where there are less than 6 datapoints available)

I am restricted to leaving the zeroes in the blank fields below the data points, as I cannot have a non-numeric field in a sumproduct range. (I have tried replacing the "0"'s with ""'s and " "'s in the formula that results in "0"'s being placed where there is a null)

So for cell F43 the average states 20.10%, what I need is the average to be 30.15% which is the last 6 data points, exluding the 2 zeroes in July and August...

Any suggestions on this would be great!