Rounding formula

[sharks17]

Hello All,
If I have a number, such as 4421, is there anyway to round up to the nearest 1000's? In other words you would say round Cell B1 and the result would be 5000. For that matter I guess I really would like to round to 10, 100, 1000, so on and so forth. Note I'd really prefer a formula to pull this data out because I would like to preserve the original number in an adjacent cell. Simply using a cell formatting isn't helpful.

I appreciate any help!
Thanks
Dave

 

[circledchicken]

Hi,

The ROUNDUP function does this (see http://www.techonthenet.com/excel/formulas/roundup.php).

e.g. =ROUNDUP(4421,-3) would return 5000 (where you would replace the bit in bold with the relevant cell reference). To round up to the nearest 100 you would use -2 as the second argument instead of -3 etc.

 

[barry houdini]

If you want to round to 10, 100, 1000 based on the size of the number try modifying that formula like this

=ROUNDUP(A1,1-LEN(A1))

 

[Matty]

Another option would be CEILING, like:

=CEILING(A1,1000)

Matty

 

[Matty]

=ROUNDUP(A1,1-LEN(A1))

Nice trick, barry houdini!

Matty

 

[sharks17]

Brilliant!!! And that is why MrExcel is the best!!!

Thank you all for taking the time to help me out.