finding 10 smallest numbers from last 20 input

I can find the smallest 10 numbers from a range of 20 cells by using "Small".
But when the 21st. number is input I want to move the range down one to
account for the new number input. 2-21 instead of 1-20. So in the formula it
would still read the last 20 numbers. This could change every week so the
cell range would need to move to the latest input and drop off the oldest
(what would then be the 21st. oldest). As I understand it a dynamic range
would add a new cell to the range but I need to keep the range at 20 cells -
just change the range by one each time a number is input. The range is
column AB and the 10 smallest formula is in column AD4:AD13.

I hope this is clear and thanks for any help

Hi,

Try this for the smallest of the last 20 and drag down for the second
smallest etc

=SMALL(OFFSET($A$1,COUNTA(A:A)-20,):OFFSET($A$1,COUNTA(A:A),),ROW(A1))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"handicapper" wrote:

I can find the smallest 10 numbers from a range of 20 cells by using "Small".
But when the 21st. number is input I want to move the range down one to
account for the new number input. 2-21 instead of 1-20. So in the formula it
would still read the last 20 numbers. This could change every week so the
cell range would need to move to the latest input and drop off the oldest
(what would then be the 21st. oldest). As I understand it a dynamic range
would add a new cell to the range but I need to keep the range at 20 cells -
just change the range by one each time a number is input. The range is
column AB and the 10 smallest formula is in column AD4:AD13.

I hope this is clear and thanks for any help

modified for column AB as in your post, put this in AD4 and drag down

=SMALL(OFFSET($AB$1,COUNTA(AB:AB)-20,):OFFSET($AB$1,COUNTA(AB:AB),),ROW(AB1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try this for the smallest of the last 20 and drag down for the second
smallest etc

=SMALL(OFFSET($A$1,COUNTA(A:A)-20,):OFFSET($A$1,COUNTA(A:A),),ROW(A1))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"handicapper" wrote:

I can find the smallest 10 numbers from a range of 20 cells by using "Small".
But when the 21st. number is input I want to move the range down one to
account for the new number input. 2-21 instead of 1-20. So in the formula it
would still read the last 20 numbers. This could change every week so the
cell range would need to move to the latest input and drop off the oldest
(what would then be the 21st. oldest). As I understand it a dynamic range
would add a new cell to the range but I need to keep the range at 20 cells -
just change the range by one each time a number is input. The range is
column AB and the 10 smallest formula is in column AD4:AD13.

I hope this is clear and thanks for any help

in AD4:

=SMALL(OFFSET($A$B1,COUNT($A:$A)-20,,20),
ROW()-ROW(AD$4)+1)

Copied down to AD13.

Assumes no blank cells, and no other data in column AB.


"handicapper" wrote in message
...
I can find the smallest 10 numbers from a range of 20 cells by using
"Small".
But when the 21st. number is input I want to move the range down one to
account for the new number input. 2-21 instead of 1-20. So in the formula
it
would still read the last 20 numbers. This could change every week so the
cell range would need to move to the latest input and drop off the oldest
(what would then be the 21st. oldest). As I understand it a dynamic range
would add a new cell to the range but I need to keep the range at 20
cells -
just change the range by one each time a number is input. The range is
column AB and the 10 smallest formula is in column AD4:AD13.

I hope this is clear and thanks for any help

Here's another one...

Assuming the range of scores is contiguous and there are at least 20 scores
available.

Entered in AD4 and copied down to AD13:

=SMALL(INDEX(AB$4:AB$100,MATCH(500,AB$4:AB$100)):I NDEX(AB$4:AB$100,COUNT(AB$4:AB$100)-20),ROWS(AD$4:AD4))

--
Biff
Microsoft Excel MVP


"handicapper" wrote in message
...
I can find the smallest 10 numbers from a range of 20 cells by using
"Small".
But when the 21st. number is input I want to move the range down one to
account for the new number input. 2-21 instead of 1-20. So in the formula
it
would still read the last 20 numbers. This could change every week so the
cell range would need to move to the latest input and drop off the oldest
(what would then be the 21st. oldest). As I understand it a dynamic range
would add a new cell to the range but I need to keep the range at 20
cells -
just change the range by one each time a number is input. The range is
column AB and the 10 smallest formula is in column AD4:AD13.

I hope this is clear and thanks for any help

That does it exactly. I would never have worked it out on my own. Thanks
Mike and thanks also to the other contributors.

"Mike H" wrote:

modified for column AB as in your post, put this in AD4 and drag down

=SMALL(OFFSET($AB$1,COUNTA(AB:AB)-20,):OFFSET($AB$1,COUNTA(AB:AB),),ROW(AB1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try this for the smallest of the last 20 and drag down for the second
smallest etc

=SMALL(OFFSET($A$1,COUNTA(A:A)-20,):OFFSET($A$1,COUNTA(A:A),),ROW(A1))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"handicapper" wrote:

I can find the smallest 10 numbers from a range of 20 cells by using "Small".
But when the 21st. number is input I want to move the range down one to
account for the new number input. 2-21 instead of 1-20. So in the formula it
would still read the last 20 numbers. This could change every week so the
cell range would need to move to the latest input and drop off the oldest
(what would then be the 21st. oldest). As I understand it a dynamic range
would add a new cell to the range but I need to keep the range at 20 cells -
just change the range by one each time a number is input. The range is
column AB and the 10 smallest formula is in column AD4:AD13.

I hope this is clear and thanks for any help

HiMike,

Further to my last reply I now realise that there will be zeros in column AB
which I need to ignore. How would I do that please?

Thanks.

"Mike H" wrote:

modified for column AB as in your post, put this in AD4 and drag down

=SMALL(OFFSET($AB$1,COUNTA(AB:AB)-20,):OFFSET($AB$1,COUNTA(AB:AB),),ROW(AB1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try this for the smallest of the last 20 and drag down for the second
smallest etc

=SMALL(OFFSET($A$1,COUNTA(A:A)-20,):OFFSET($A$1,COUNTA(A:A),),ROW(A1))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"handicapper" wrote:

I can find the smallest 10 numbers from a range of 20 cells by using "Small".
But when the 21st. number is input I want to move the range down one to
account for the new number input. 2-21 instead of 1-20. So in the formula it
would still read the last 20 numbers. This could change every week so the
cell range would need to move to the latest input and drop off the oldest
(what would then be the 21st. oldest). As I understand it a dynamic range
would add a new cell to the range but I need to keep the range at 20 cells -
just change the range by one each time a number is input. The range is
column AB and the 10 smallest formula is in column AD4:AD13.

I hope this is clear and thanks for any help

Excel 2007 PivotTable
Find 10 lowest amounts in last 20 days.
Ignores zero amounts.
No formulas used.
With macro option.
Dynamic and animated.
http://c0718892.cdn.cloudfiles.racks.../05_02_10.xlsm
Pdf preview:
http://www.mediafire.com/file/gwmdid5jwnh/05_02_10.pdf

Hello,

Select cells AD4:AD13 and array-enter:
=SMALL(IF(INDEX(AB:AB,MAX(1,COUNT(AB:AB)-19)):INDEX(AB:AB,COUNT(AB:AB))0,INDEX(AB:AB,MAX( 1,COUNT(AB:AB)-19)):INDEX(AB:AB,COUNT(AB:AB))),ROW(INDIRECT("1:10 ")))

Personally I would favor a small UDF for this, but you posted this
question in Excel.Worksheet.Functions...

Regards,
Bernd