Format Round to Nearest Thousand

[GillianHG]

I posted this earlier but didn't get the answer I needed so will try again.

I have a user who wants a custom FORMAT to round to the nearest thousand.
(Eg if she has 104580, she wants it to round to 105000.) I tried #,##0 but
it takes off the
last three 0's which need to display. Does anyone know the syntax for this?

She does not want to use the ROUND function; she needs the actual number to
remain for calculating, she just wants to change the display.

Thanks for your help!

--
Thanks,
GillianHG

[Niek Otten]

Hi Gillian,

Use another cell with a ROUND formula instead, and hide the original.
Very misleading this, by the way. I don't know your application, but my accountant would not sign my books if he found any tricks
like this one.

--
Kind regards,

Niek Otten

"GillianHG" <[email protected]> wrote in message news:[email protected]...
>I posted this earlier but didn't get the answer I needed so will try again.
>
> I have a user who wants a custom FORMAT to round to the nearest thousand.
> (Eg if she has 104580, she wants it to round to 105000.) I tried #,##0 but
> it takes off the
> last three 0's which need to display. Does anyone know the syntax for this?
>
> She does not want to use the ROUND function; she needs the actual number to
> remain for calculating, she just wants to change the display.
>
> Thanks for your help!
>
> --
> Thanks,
> GillianHG

[RagDyeR]

Try this:

#,\0\0\0
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"GillianHG" <[email protected]> wrote in message
news:[email protected]...
I posted this earlier but didn't get the answer I needed so will try again.

I have a user who wants a custom FORMAT to round to the nearest thousand.
(Eg if she has 104580, she wants it to round to 105000.) I tried #,##0 but
it takes off the
last three 0's which need to display. Does anyone know the syntax for this?

She does not want to use the ROUND function; she needs the actual number to
remain for calculating, she just wants to change the display.

Thanks for your help!

--
Thanks,
GillianHG

[Ron Coderre]

Try something like this:

<Format><Cells><Number tab>
Category: Custom
Type: #,##0,"000"

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"GillianHG" wrote:

> I posted this earlier but didn't get the answer I needed so will try again.
>
> I have a user who wants a custom FORMAT to round to the nearest thousand.
> (Eg if she has 104580, she wants it to round to 105000.) I tried #,##0 but
> it takes off the
> last three 0's which need to display. Does anyone know the syntax for this?
>
> She does not want to use the ROUND function; she needs the actual number to
> remain for calculating, she just wants to change the display.
>
> Thanks for your help!
>
> --
> Thanks,
> GillianHG

[Bamaopoly]

When I figured this out, I wanted to slap myself.

Use negative numbers for significant digits.

=round(number, -3) will round to the nearest 1,000.

Hope that helps.

[shg]

Welcome to the forum.

That's true, but it's not what the OP asked for in this five year old thread.

> She does not want to use the ROUND function; she needs the actual number to
> remain for calculating, she just wants to change the display.

[Steve=True]

Hi,

Would this custom format do what you are looking for?

[>999999]$#,,"M";[>999]$#,"K";$#



Steve=True

[teylyn]

Steve=True,

as shg has already pointed out, this thread is several years old.

As I've mentioned in my recent communication with you, this forum is not a vehicle for your SEO, so please refrain from linking to your website unless it's relevant to the question.

Even if the question was still active, your suggestion does not do what the OP asked for, and I perceive your comment as a way to plant yet another link to your web site, which I have now deleted.

[rfite]

I know this is in response to old thread, but, by experimenting I used custom format of "$#,###," for currency rounding to the nearest thousand and it worked.

[Donskie]

I know this is very old, but i came across the similar issue - i used this format

Please Login or Register  to view this content.

[FDibbins]

Thanks for the input.