Combining averageif function and offset help

[Sarahmaths]

Hi,

I have a set of data in which the data will gradually become less zeros. The zeros at the minute are pulling in from other tabs on my spreadsheet.
I am trying to create a rolling average of the 3 most recent tests per student. All tests are already in and the ones not done are zero, those that have been done have a number greater than zero. I need the average of the last 3 that aren't zeros.

I have tried AVERAGE(OFFSET(B3,0,COUNT(B3:K3)-3,1,3)) however this is just returning zero.

If I get rid of the zeros then this works however I need the zeros to be there to pick up the data once it has been put in.

Is there a way of combining what I have above with an averageif where the criteria is that the cell value is bigger than 0?

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Student												Rolling Average
2	1	94%	100%	89%	50%	100%	20%	0%	0%	0%	0%		56.7%
3	2	80%	79%	67%	0%	0%	0%	0%	0%	0%	0%		75.3%
4	3	74%	47%	60%	58%	0%	0%	0%	0%	0%	0%		55.0%
5	4	71%	71%	53%	0%	0%	0%	0%	0%	0%	0%		65.0%
6	5	77%	88%	71%	75%	0%	0%	0%	0%	0%	0%		78.0%
Sheet2

Cell Formulas
Range	Formula
M2:M6	M2	=SUM(MOD(LARGE(IF(B2:K2>0,B2:K2/10+COLUMN(B2:K2)*10,0),{1,2,3}),1))*10/MIN(3,COUNTIF(B2:K2,">0"))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sarahmaths]

Thank you so much, that seems to work great.

Can you maybe explain it to me so I know whats happening.

Thanks,

 

[Eric W]

Sure. This is an array formula, which means it looks at a range of data, and eventually combines everything in a single result. Newer versions of Excel have some functions that would make this easier. I resorted to some tricks to accomplish the same thing with the functions you have.

=SUM(MOD(LARGE(IF(B2:K2>0,B2:K2/10+COLUMN(B2:K2)*10,0),{1,2,3}),1))*10/MIN(3,COUNTIF(B2:K2,">0"))

OK, starting with the IF function in red, it looks at the range B2:K2 and sees if the value is >0. If it is, it will return the value divided by 10, plus the column number times 10. So B2 in the example above is 94%, or .94. The column is B, or 2. So it returns .094 + 2 * 10, or 20.094. It does that for every cell in B2:K2, so we now have an array of {20.094,30.1,40.089,50.05,60.1,70.02,0,0,0,0}.

=SUM(MOD(LARGE(IF(B2:K2>0,B2:K2/10+COLUMN(B2:K2)*10,0),{1,2,3}),1))*10/MIN(3,COUNTIF(B2:K2,">0"))

Now the LARGE function takes the largest 3 values from that array. Since we multiplied the column number by 10, it dominates those values, so the 3 largest values will be the values with the largest column numbers, which will be {50.05,60.1,70.02}.

=SUM(MOD(LARGE(IF(B2:K2>0,B2:K2/10+COLUMN(B2:K2)*10,0),{1,2,3}),1))*10/MIN(3,COUNTIF(B2:K2,">0"))

The MOD function, when used with a divisor of 1, will return the fractional part, so we get {.05,.1,.02}. We then multiply by 10 (blue) to get the original values {.5,1,.2}. We had to divide by 10 earlier so that a 100% value would become a fractional part and not get removed with the MOD. The SUM will be 1.7.

At this point, you could just divide by 3 to get the average of the rightmost 3 values, but it's possible that you might have less than 3 scores. The COUNTIF finds out how many non-zero values there are. If there are more than 3, the MIN will just take 3. If there are less than 3, the MIN will take the COUNTIF value and you'll get the average of the values it finds. If there are no non-zero values, then you'll get a #DIV/0 error.

Hope this helps!