Round up to nearest number within a list

[Thebatfink]

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 -

=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

 

[Jonmo1]

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

 

[jasonb75]

Or with an ascending list you could try

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

 

[Jonmo1]

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.

 

[jasonb75]

Good point Jon, think I fell foul of your sig

 

[Jubjab]

With no helper lists, here a direct formula approach:

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

 

[Thebatfink]

Thanks for that. Works nicely

 

[Beate Schmitz]

Hello,

here an additional proposal with the list in column B:

Excel Workbook
	A	B	C
1	67	11	84
2	*	31	*
3	*	35	*
4	*	36	*
5	*	46	*
6	*	53	*
7	*	64	*
8	*	84	*
9	*	85	*
10	*	91	*
Sheet

 

[barry houdini]

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

 

[Jubjab]

..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!