Rounding up to a set of specific numbers?

[bradles]

Does anyone know how I can round a number up to a specific set of numbers.

I need to round a number to the nearest of the following numbers:
250
350
450
550
650
750
850
950
1050
1150
1250
...
...
etc

The numbers above are in a range B3:AC3.

Eg 1: 260 becomes 350
Eg 2: 700 becomes 750

Any ideas?

Bradles

Hi

Try something like this, with your value in A2
=HLOOKUP(A2+90,B3:AC3,1,TRUE)

Hope this helps.
Andy.

"bradles" <[email protected]> wrote in
message news:[email protected]...
>
> Does anyone know how I can round a number up to a specific set of
> numbers.
>
> I need to round a number to the nearest of the following numbers:
> 250
> 350
> 450
> 550
> 650
> 750
> 850
> 950
> 1050
> 1150
> 1250
> ..
> ..
> etc
>
> The numbers above are in a range B3:AC3.
>
> Eg 1: 260 becomes 350
> Eg 2: 700 becomes 750
>
> Any ideas?
>
> Bradles
>
>
> --
> bradles
> ------------------------------------------------------------------------
> bradles's Profile:
> http://www.excelforum.com/member.php...nfo&userid=998
> View this thread: http://www.excelforum.com/showthread...hreadid=569322
>

[bradles]

Hi

Try something like this, with your value in A2
=HLOOKUP(A2+90,B3:AC3,1,TRUE)

Hope this helps.
Andy.

That almost had it Andy. Only problem is: what if the number is 250.1. I need that to be rounded up to 350 because it is greater than 250.

Your formular in that case would be looking up 250.1+90 = 349.1. So it would still drop down to 250.

Any more ideas?

Brad

Hi

Change the A2+90 to A2+99.9999 or similar.
Sorry, I didn't realise you'd be using decimals.

Andy.

"bradles" <[email protected]> wrote in
message news:[email protected]...
>
>> Hi
>>
>> Try something like this, with your value in A2
>> =HLOOKUP(A2+90,B3:AC3,1,TRUE)
>>
>> Hope this helps.
>> Andy.
> That almost had it Andy. Only problem is: what if the number is 250.1.
> I need that to be rounded up to 350 because it is greater than 250.
>
> Your formular in that case would be looking up 250.1+90 = 349.1. So it
> would still drop down to 250.
>
> Any more ideas?
>
> Brad
>
>
> --
> bradles
> ------------------------------------------------------------------------
> bradles's Profile:
> http://www.excelforum.com/member.php...nfo&userid=998
> View this thread: http://www.excelforum.com/showthread...hreadid=569322
>

[Niek Otten]

With 1250,1150...250 in A1:A11 (sorted descending) and your number in B1:

=INDEX(A1:A11,MATCH(B1,A1:A11,-1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"bradles" <[email protected]> wrote in message
news:[email protected]...
|
| Does anyone know how I can round a number up to a specific set of
| numbers.
|
| I need to round a number to the nearest of the following numbers:
| 250
| 350
| 450
| 550
| 650
| 750
| 850
| 950
| 1050
| 1150
| 1250
| ..
| ..
| etc
|
| The numbers above are in a range B3:AC3.
|
| Eg 1: 260 becomes 350
| Eg 2: 700 becomes 750
|
| Any ideas?
|
| Bradles
|
|
| --
| bradles
| ------------------------------------------------------------------------
| bradles's Profile: http://www.excelforum.com/member.php...nfo&userid=998
| View this thread: http://www.excelforum.com/showthread...hreadid=569322
|

[bradles]

Change the A2+90 to A2+99.9999 or similar.
Sorry, I didn't realise you'd be using decimals.

Andy.

Thanks Andy,

I think that's done it. Much appreciated.

Brad.

[Sandy Mann]

Possibly too late now but does:

=CEILING(A1-50,100)+50

do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"bradles" <[email protected]> wrote in
message news:[email protected]...
>
> Does anyone know how I can round a number up to a specific set of
> numbers.
>
> I need to round a number to the nearest of the following numbers:
> 250
> 350
> 450
> 550
> 650
> 750
> 850
> 950
> 1050
> 1150
> 1250
> ..
> ..
> etc
>
> The numbers above are in a range B3:AC3.
>
> Eg 1: 260 becomes 350
> Eg 2: 700 becomes 750
>
> Any ideas?
>
> Bradles
>
>
> --
> bradles
> ------------------------------------------------------------------------
> bradles's Profile:
> http://www.excelforum.com/member.php...nfo&userid=998
> View this thread: http://www.excelforum.com/showthread...hreadid=569322
>