Excel Round number up to a specific number given a range

I have a set of definite number, like this:

50
100
200
500
1000

I would like to round a number up to nearest number like this:

20 => 50
55 => 100
433 => 500

Are there any specific function that i could use?

5 Answers

You can use the INDEX and MATCH functions to do your rounding.

Put the numbers you want to round to in descending order in one column (I'm going to assume they are in cells A1:A5). Put the number that you want to be rounded in another cell (I'm going to assume it is in B1).

In another cell, enter the formula

=INDEX($A$1:$A$5,MATCH(B1,$A$1:$A$5,-1))

This will return the number from B1 rounded up to the next highest number from the range A1:A5.

The way this works is that MATCH finds the position in the range A1:A5 of the lowest number that is greater than or equal to the number in B1. INDEX returns the value from the range A1:A5 at the podition returned by MATCH.

Note that numbers higher than 1000 (the highest number in the range) will return #N/A. If you want to avoid having 0 round up to 50, you can enter 0 in cell A6 and change the ranges in the formula to $A$1:$A$6 (values of zero or less will then round to zero).

Use:

=INDEX({50,100,200,500,1000},MATCH(A1,{0,51,101,201,501}))

Assuming your value is in A1

=IF(A1<=50,50,IF(A1<=100,100,IF(A1<=200,200,IF(A1<=500,500,IF(A1<=1000,1000,A1)))))

(with the above formula anything over 1000 won't get rounded)

10 > round up to the nearest whole number 100 > round up to the nearest multiple of 5 1000 > round up to the nearest multiple of 10 10000 > round up to multiple of 50 50000 > round up to multiple of 100 100000 > round up to multiple of 1000 100000 < round up to multiple of 10000

Formula:

 =IF(I6<=10,MROUND(I6,1),IF(I6<=100,MROUND(I6,5),IF(I6<=1000,MROUND(I6,10),IF(I6<=10000,MROUND(I6,50),IF(I6<=50000,MROUND(I6,100),IF(100000>I6,MROUND(I6,1000),IF(100000<=I6,MROUND(I6,10000))))

If you have a reasonably small range, you can use Excel's LOOKUP() function.

=LOOKUP(A1, {20,55,433}, {50,100,500})

Note: This approach is only useful if you know the values of all the numbers to be rounded up in advance. If you need to round up an arbitrary number whose value is not known, then you should use one of the other solutions.