Rounding up to a set of specific numbers?

[bradles]

Does anyone know how I can round a number up to a specific set o
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?

Bradle

 

[Guest]

Hi

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

Hope this helps.
Andy.

 

[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 i
would still drop down to 250.

Any more ideas?

Bra

 

[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

|
| 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?action=getinfo&userid=998
| View this thread: http://www.excelforum.com/showthread.php?threadid=569322
|

 

[Guest]

Hi

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

Andy.

 

[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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk