Formating instead of rounding 12,345.67 formatted as 12,300 | General Excel Questions & Answers

Formating instead of rounding 12,345.67 formatted as 12,300

Henric

Member

Members

Forum Posts: 3

Member Since:
June 26, 2016

Offline

July 22, 2017 - 9:38 pm

I have several situations where I would like format a number instead of rounding it with ROUND(...). But I cannot find the correct solution. If I have a four or a five digit number, I would like to round it to whole hundred.

For example 12,345.67 should be 12,300 and 1,234.56 should be 1.200. Is this possible?

Hope someone out there can help me.

See Answer

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

July 22, 2017 - 11:26 pm

Hi Henric,

A simple ROUND function will work.

Just make sure you use positive values in the num_digits argument for decimal places, and negative numbers for integers, tens, hundreds.

=ROUND(A1,-2) will do what you need.

ROUNDUP or ROUNDDOWN will also work, depending on what you want.

Henric

Member

Members

Forum Posts: 3

Member Since:
June 26, 2016

Offline

July 23, 2017 - 5:03 pm

Catalin Bombea said
Hi Henric,

A simple ROUND function will work.

Just make sure you use positive values in the num_digits argument for decimal places, and negative numbers for integers, tens, hundreds.

=ROUND(A1,-2) will do what you need.

ROUNDUP or ROUNDDOWN will also work, depending on what you want.

Hi Catalin

I know the ROUND-function which I use a lot, but in my particular case I would prefer to format my numbers instead of rounding them.

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

July 23, 2017 - 6:21 pm

Not possible to hundreds level, only to thousands or millions:

#,###,"000"

#,###,,"000000"