# Round up to nearest number within a list

#### Thebatfink

##### Active Member
Hi,

I guess I have an odd rounding up query. I want to know if its possible to make excel round up a number to the nearest number from within a list of numbers.

For instance my list could be

10
12
15
17
19
22
25
30
35
42

The numbers arent odds or evens or multiples of anything so I'm guessing there won't be an inbuilt function to do this but is there anyway I could get to the answer in a more round about way with helper calculations??

I guess I could use the following -

Code:
``=IF(A1>42,"Error",IF(A1<10,10,IF(AND(A1>10,A1<12),12,IF(AND(A1>12,A1<15),15, ..........``

but it seems excessive and I would like to be able to add numbers to the list occasionally without having to redo all the formulae.

Anyone have any ideas?

Thanks!
Batfink

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Put your numbers in a list of cells, say B1:B10 and sort them DESCENDING

42
35
30
25
etc...

=INDEX(B1:B10,Match(A1,B1:B10,-1))

Or with an ascending list you could try

=INDEX(B1:B10,MATCH(A1,B1:B10,1)+1)

Or with an ascending list you could try

=INDEX(B1:B10,MATCH(A1,B1:B10,1)+1)
That will give incorrect results for exact matches..

If A1 = 10, formula will return 12.

Good point Jon, think I fell foul of your sig

With no helper lists, here a direct formula approach:

Code:
``=IF(A1>42,"Error",VALUE(MID("42353025221917151210",MATCH(A1,{42,35,30,25,22,19,17,15,12,10},-1)*2-1,2)))``

Thanks for that. Works nicely

Hello,

here an additional proposal with the list in column B:
Excel Workbook
ABC
1671184
2*31*
3*35*
4*36*
5*46*
6*53*
7*64*
8*84*
9*85*
10*91*
Sheet

Or with an ascending list you could try

=INDEX(B1:B10,MATCH(A1,B1:B10,1)+1)

You can fix the "exact match" issue by changing to

=INDEX(B1:B10,MATCH(A1,B1:B10,1)+(LOOKUP(A1,B1:B10)<>A1))

but you'd have to do a further adjustment to accommodate values of A1 < B1.....

..or use an array formula

=MIN(IF(B1:B10>=A1,B1:B10))

confirmed with CTRL+SHIFT+ENTER

B1:B10 can then be in any order

..or use an array formula

=MIN(IF(B1:B10>=A1,B1:B10))

confirmed with CTRL+SHIFT+ENTER

B1:B10 can then be in any order

Nice and elegant!

Replies
10
Views
864
Replies
7
Views
125
Replies
4
Views
776
Replies
13
Views
657
Replies
1
Views
159

1,203,752
Messages
6,057,151
Members
444,908
Latest member
Jayrey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back