Rounding question

[Hayley]

I'm sure this is an easy answer but I'm just not getting it. How do you round a number to the nearest 100 or 1000? For example, if the number is 5798.69 but I want it to say 5700 or 6000, what's the formula? I tried =ROUND(A2,3) but that returned 5798.69 instead of 5700 like I was hoping.


Thank you!

 

[mercatog]

Hi

If toy have in A2 5798.69

=ROUND(A2/1000,0)*1000

will return 6000

Or

=INT(A2/100)*100

will return 5700


regards

 

[Hayley]

Yup that did it! Thanks so much as always!

 

[kuldeepjainesl]

Hi Haylay,


Assuming your data in A1,


Try =FLOOR(A1,1000) for roundup

or

Try =CEILING(A1,1000) for rounddown


Where you can define the rounding precision by changing 1000 to 10, 100 etc.


Regards,

 

[Hayley]

KPJ, nice! That works too! I'm using "round" for this particular spreadsheet though because I want it to round to the nearest (up or down, whatever's appropriate for each individual number).


Thanks!

 

[Luke M]

You can also use negative arguements in the ROUND function.

This formula will evaluate to 6000

=ROUND(5798.69,-3)


This will evaluate to 5800

=ROUND(5798.69,-2)

 

[Hui]

Following on from Luke:

From Excel Help


=ROUND(A2,3-LEN(INT(A2))) Rounds the top number to 3 significant digits (5490000)

=ROUNDDOWN(A3,3-LEN(INT(A3))) Rounds the bottom number down to 3 significant digits (22200)

=ROUNDUP(A2,5-LEN(INT(A2))) Rounds the top number up to 5 significant digits (5492900)

 

[Luke M]

Neat trick Hui, I had not realized you could make a dynamic significant digit rounding!