last 20 items in a list

I

Ian G

Hi

I have a column of data which shows the result of a quality check (possible
outcomes are "pass" or "fail"). I have set up functions so far that mean
"pass" is given a value of 1 and "fail" is given a value of 0. This means I
can then work out the average score (e.g. 15 passes out of 20 gives 75%).

Ok - fine so far.

My query is, I want to set up a function that will give me the score for the
last 20 cases, but always the last 20 cases (the list will be added to each
month, but not by a set amount - so could be 5 cases one month, 7 the next,
etc.).

Looking at other posts I think it may have something to do with the "offset"
function, but I can't work out how to set up a function which uses a rolling
cell range from a continually expanding range.

Hope someone out there can help! Thanks!

p.s. I am using Excel XP
 
M

Max

One possible way ..

Assuming the numeric formula returns of 1 and 0
are in col B, from B1 down continuously,
we could put in say, C1 (normal ENTER):

=IF(COUNT(B:B)<20,"",
AVERAGE(OFFSET(INDIRECT("B"&COUNT(B:B)),,,-20)))
 
I

Ian G

Thanks!

That really helps. I actually wanted the total (not the average) but
substituted 'sum' for 'average' and it works perfectly.

Also, thanks for coming back so quickly - my boss will be really impressed.
I'll let him know I had a bit of help though. ; )
 
Top