Remember Me?

#1
January 3rd 05, 05:15 PM
 pugsly8422 Posts: n/a
How do I pick a number from a list in Excel?

I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

#2
January 3rd 05, 05:25 PM
 Bob Phillips Posts: n/a

Sort the data descending, then assuming the test value is in B1, use

=MATCH(B1+1,A:A,-1)

--
HTH

-------

Bob Phillips
"pugsly8422" wrote in message
...
I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that

is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

#3
January 3rd 05, 05:59 PM
 Peo Sjoblom Posts: n/a

One way,

with the numbers in A1:A10 and the inout cell in B1

=INDEX(A1:A10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"< ="&B1)+1),A1:A10,0))

Regards,

Peo Sjoblom

"pugsly8422" wrote:

I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

#4
January 3rd 05, 06:06 PM
 Sandy Mann Posts: n/a

Pugsly,

If your data is not always going to be integers then try:

=MIN(IF(A1:A99B1,A1:A99))

This is an array formula so enter ir with Control + Shift + Enter instead of
just Enter

HTH

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

"pugsly8422" wrote in message
...
I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that

is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

#5
January 3rd 05, 07:07 PM
 pugsly8422 Posts: n/a

Thank you for your help, this is exactly what I needed.

Thanks also for everyone else's help as well, I really appreciate your help
and time.

"Peo Sjoblom" wrote:

One way,

with the numbers in A1:A10 and the inout cell in B1

=INDEX(A1:A10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"< ="&B1)+1),A1:A10,0))

Regards,

Peo Sjoblom

"pugsly8422" wrote:

I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Richard Excel Discussion (Misc queries) 1 January 5th 05 09:36 AM Victor_alb Excel Discussion (Misc queries) 2 December 21st 04 05:21 AM GI Excel Discussion (Misc queries) 0 December 6th 04 07:45 PM Austrian Hannes Setting up and Configuration of Excel 2 December 6th 04 06:54 PM Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM

All times are GMT +1. The time now is 03:19 AM.