Round to a number in a list
 

Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214). I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!

Round to a number in a list
 

With
A1:A7 containing your posted list
and
B1: (a number to match)

If you want exact matches to use the matched item,
otherwise...use the next highest item:
C1: =INDEX(A1:A7,MATCH(1,FREQUENCY(B1,A1:A7),0))

If B1: 200
the formula returns 200

If B1: 214
the formula returns 300

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Huber57" wrote in message
...
Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214).
I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!

Round to a number in a list
 

Hi,

With your list in A1 - A7 and your number in b1 try this

=INDEX(A1:A7,MATCH(MIN(IF(A1:A7-B1=0,A1:A7,FALSE)),IF(A1:A7-B1=0,A1:A7,FALSE),0))

Array entered with Ctrl+Shift+Enter

Mike

"Huber57" wrote:

Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214). I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!

Round to a number in a list
 

One other thing...

If you want to exclude lookup values that
are below or above the range limits:

Some options, still using your posted example:
C1: =IF(AND(B1=10,B1<=775),INDEX(A1:A7,
MATCH(1,FREQUENCY(B1,A1:A7),0)),"n/a")

C1: =IF(AND(B1=MIN(A1:A7),B1<=MAX(A1:A7)),INDEX(A1:A7 ,
MATCH(1,FREQUENCY(B1,A1:A7),0)),"n/a")


--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Ron Coderre" wrote in message
...
With
A1:A7 containing your posted list
and
B1: (a number to match)

If you want exact matches to use the matched item,
otherwise...use the next highest item:
C1: =INDEX(A1:A7,MATCH(1,FREQUENCY(B1,A1:A7),0))

If B1: 200
the formula returns 200

If B1: 214
the formula returns 300

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Huber57" wrote in message
...
Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214).
I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!

Round to a number in a list
 

Mike and Ron,

Thanks to both!!

I appreciate it.


"Ron Coderre" wrote:

With
A1:A7 containing your posted list
and
B1: (a number to match)

If you want exact matches to use the matched item,
otherwise...use the next highest item:
C1: =INDEX(A1:A7,MATCH(1,FREQUENCY(B1,A1:A7),0))

If B1: 200
the formula returns 200

If B1: 214
the formula returns 300

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Huber57" wrote in message
...
Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214).
I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!