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

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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ian G" <Ian wrote in message
...
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

C1 should return the desired results
(Format C1 as percentage)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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. ; )

"Max" wrote:

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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ian G" <Ian wrote in message
...
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

Glad it helped !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ian G" wrote in message
...
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. ; )