Formatting and Rounding 2

[emik]

Hi,

I need to round a number to the nearest million but show a comma.

$34,450,000.00 should be $34,5 (I'm able to get it to 345)

Right now I'm doing Round(number)/1000000

Thanks.

 

[GlennUK]

Why the comma? I've done reporting for organisations in millions, and it's a simple matter of doing a custom number format followed by 2 commas, like:

#,##0,,
0.0,,

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[emik]

Just the requirements that they want the comma.

I can't seem to get it to work,

I'm trying to do this in a query, the expression is:

Formatted: Round([MarketValue])/100000

and by right clicking on the field and putting: 0.0,, Into the format value I get a return of 0. I tried using the #,##0,, but no luck with that either. I also tried changing my rounding by removing a 0.

 

[emik]

I'm still stuck on this....

The query is:

Market: Sum(Round(([MarketValue]/100000)))

Any possible way to make it $34,9 million (if the original is $34,900,294).

Thank you.

 

[AnotherHiggins]

emik,

Quick question: Is this for the US or Europe? I ask because some European countries use a comma where we here in the US use a period (AKA stop AKA full stop).

If this is to be used in the States, then I'd hasten to point (pardon the pun) out that using a comma in this situation is just plain wrong. $34[highlight][attn].[/attn][/highlight]9 Million makes sense because it is 34 and nine tenths million.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[emik]

Hi anotherhiggins,

I'm in Canada and for some reason (whether it be right or wrong) the user wants the comma. If it were me, I'd just round and be on my way

I got this from a user in another forum:

Replace(Format(34450000.00,"$#,,.0" ),".",",")

but when I replace the 34450000 with my Sum(Round(value)/1000000) it doesn't work.

Is there an easy way to do this with a period instead of a comma? Worst case I'll just tell them this is how I was able to do it (better than no seperator).

Thanks.

 

[PHV]

Replace(Format(Sum([MarketValue]),"$#,,.0" ),".",",")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[emik]

PHV,

Someone just posted the exact same thing to me. This is the greatest website ever.

Thank you to everyone who helped me on this one.

 

[AnotherHiggins]

Grrr. I hate it when end users try to make things harder than they should be for the sake of having something "pretty".

Oh well.

Looking at what you are using (Round(number)/1000000), the first thing that strikes me is that you seem to be missing an argument. I'd expect to see
Round(Number,NumberOfDecimalPlaces)

So this:
[COLOR=blue white]Round(Number / 1000000, 1)[/color]
will return 34.4.

If they really want to see a comma instead, you can use the previous code like this:
[COLOR=blue white]Replace(Round([c1] / 1000000, 1), ".", ",")[/color]

But beware: Using REPLACE will return a STRING, not a number.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Sorry - that second formula still had [C1], which I was using to verify I didn't have any typos. In keeping with the context of the first example, it should read
[COLOR=blue white]Replace(Round(Number / 1000000, 1), ".", ",")[/color]

 

[emik]

Ya I realized that it returns a string with replace because when I try to sort it by Marketvalue it takes the first character, so $33,9 is now smaller than $4,1.

Oh these horrible users..

 

[emik]

Ok, I say forget the comma!

Round(Number / 1000000, 1) - works great, I wish I knew I was missing an arguement, it would have saved me a lot of time.