Gary Demos
asked on
Crystal Reports - Currency formatting for large amounts
I wish to format currency in my report to make large amounts appear as following:
$1,200,000 would appear as $1.2M
$400,000 would appear as $400K
$10,000 would appear as 10K
My numbers do not get into billions :)
$1,200,000 would appear as $1.2M
$400,000 would appear as $400K
$10,000 would appear as 10K
My numbers do not get into billions :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How would I add the $ sign?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
I know that you've already accepted a solution, and I'm not looking for any points, but I wanted to ask a few questions (to check for potential problems), and offer an alternative solution.
Do you only want to see decimal places on the millions? For example, if you had 10,200, do you want to see 10K or 10.2K ?
Do you ever want to see more than one decimal place? For example, if you had 1,230,000, do you want to see 1.2M or 1.23M?
Is rounding an issue? For example, if you had something like 1,290,000 and only wanted one decimal place, do you want to see 1.2M or 1.3M?
If the value is less than 1,000, do you need to see any decimal places?
Assuming that you:
Only want a decimal place on the millions
Only want one decimal place
Don't need to round the decimals
Don't want any decimals on the values below 1,000
you could also handle it with a formula like the following:
Oh, I assumed that you also wanted to see the thousands below 10,000 as nK (1K, 2K, etc.). If not, the formula could easily be changed.
James
Do you only want to see decimal places on the millions? For example, if you had 10,200, do you want to see 10K or 10.2K ?
Do you ever want to see more than one decimal place? For example, if you had 1,230,000, do you want to see 1.2M or 1.23M?
Is rounding an issue? For example, if you had something like 1,290,000 and only wanted one decimal place, do you want to see 1.2M or 1.3M?
If the value is less than 1,000, do you need to see any decimal places?
Assuming that you:
Only want a decimal place on the millions
Only want one decimal place
Don't need to round the decimals
Don't want any decimals on the values below 1,000
you could also handle it with a formula like the following:
if {your_field} >= 1000000 then
"$" + CStr ({your_field} / 1000000, "#.#") + "M"
else
if {your_field} >= 1000 then
"$" + CStr ({your_field} / 1000, "#") + "K"
else
"$" + CStr ({your_field}, "#")
Oh, I assumed that you also wanted to see the thousands below 10,000 as nK (1K, 2K, etc.). If not, the formula could easily be changed.
James
ASKER
Thanks James! - that seems to be a very effective way to handle this issue. I tried it and it works perfectly. I did have to make a couple edits to the previous answer as shown below, but it works also.
if len(toText({yourfield},'#' ))=7 then "$" & left(toText({yourfield},'# '),1)&'.'& mid(toText ({yourfiel d},'#'),2, 1)&'M'
else if len(toText({yourfield},'#' ))=6 then "$" &left(toText({yourfield},' #'),3) & 'K'
else if len(toText({yourfield},'#' ))=5 then"$" & left(toText({yourfield},'# '),2) & 'K'
else "$" & toText({yourfield},'#')
if len(toText({yourfield},'#'
else if len(toText({yourfield},'#'
else if len(toText({yourfield},'#'
else "$" & toText({yourfield},'#')
Oh, yeah, Raghavendra's solution should work. I just thought I'd post an alternative. And, since you didn't post any details about your data, I was also concerned that there might be some issues that needed to be addressed, like the decimal places.
James
James
ASKER