Finding average while excluding zeroes in a vintage model updating monthly

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!

A couple things...

I'm not sure why COUNTIF(... ,">0") should be counting values that are 0. When I tested it on mine it didn't seem to.

Second, I'm not sure I understand what you're doing with the denominator. Usually when people do something like that, they're aiming for MAX, not MIN. Particularly when you say you were trying to handle an issue for which the number of data points is less than 6.

As far as I can see... M3-G3 should always be 6, correct? Does this function as a maximum denominator or mininum denominator? If it's a maximum, then your use of MIN is correct. What happens then when you have zero data points? MIN (6,0) = 0 which will result in a #DIV/0 error. If you make the correction below... I don't think the M3-G3 will ever come into play ... if you only have data for each month once, the SUMPRODUCT will max out at 6, like you'd expect. You will need something to look after 0 data points. You might need to consider the MIN() portion, maybe MAX(1, SUMPRODUCT(..))

My actual advice, after all that: your COUNTIF() in the denominator is not limited like your SUMPRODUCT() is in the numerator. You should probably consider replacing the COUNTIF() with a SUMPRODUCT() similar to your numerator. ie. SUMPRODUCT(($A6:$A39<M3)*($B6:$B39>=G3)*(F6:F39>0)) or close. (You might need -- in there)

Sort of long winded, but anyway.

Scott

Ah, that first bit about COUNTIF... I misread what you said. Oops.

About your example... you said you were dividing by 6 where you should have been dividing by 4? That should just be a case of replacing the COUNTIF with a SUMPRODUCT with the criteria. (Probably the only thing useful of the previous message)

You can ignore the rest of what I said. I have very little knowledge of what you're actually trying to do with your data, and it just looked weird when i glanced at it. :-P

Scott

Scott,

This does indeed work, that is totally awesome,

I can't figure out how

=SUMPRODUCT(($A6:$A39<M3)*($B6:$B39>=G3)*(F6:F39>0))

equals 4

seems like it would just add up the percentages? (120.62%) and just leave off any zero's in the sum?

but adding the ">0" behind F6:F39 gets 400%? (Or 4)


Dataset is

54.59%
10.09%
19.29%
36.65%
0.00%
0.00%



(I'm 1 day old to the SumProduct Function)

:-)

Have you looked at the http://www.xldynamic.com/source/xld.SUMPRODUCT.html site? (I've posted it 6 times today in different areas... i should make it a hotkey or something. lol)

Anyway, the reason it works is because each of the components of the sumproduct is a statement that we are checking for TRUE/FALSE. Since each is TRUE/FALSE, these get converted to either 1 or 0. For each line, it ends up like the following:

TRUE FALSE TRUE
TRUE TRUE FALSE
TRUE TRUE TRUE
FALSE FALSE TRUE
TRUE TRUE TRUE
...

These get converted to 1's and 0's and are multipled, so for each respectively it would be:

1*0*1 = 0
1*1*0 = 0
1*1*1 = 1
0*0*1 = 0
1*1*1 = 1
...

Which then gets added as the SUM part of the SUMPRODUCT. (ie. 0+0+1+0+1+...)

So if you look at each line of your data, you'll see that either all the conditions are TRUE (in which case you get a 1 for the sum) or one of them (at least) is FALSE (in which case you get a 0 for the sum).

In your SUMPRODUCT in the numerator, you're only using the first two portions in the same fashion, and will get a true/false like the above. The difference is for the third portion, you're using the actual values instead of TRUE/FALSE.

Oh yeah... just so you know, in the denominator, you would get 4, not 400%. (Although I guess technically they are quite similar...)

Hopefully this makes sense.

Scott

Hey thx for the explanation,

I was thrown off by not using an equation in my last range the only time ive used it (this time)

thx!!7!