1

I have a currency formula field called USD Total where the Amount is multiplied by GBP exchange rate.

USD TOTAL = Amount * 1.22

I'm trying to create a formula where:

  1. I can add "USD" prefix AND
  2. The value is round-up to the nearest 10 dollars or nearest 100 dollars

For example:

  1. USD 3,475.28 should be displayed as USD 3,500.00

  2. USD 475.28 should be displayed as USD 500.00

I've tried using ROUND and CEILING functions but I'm not getting the result that I want in rounding-up.

2
  • Do you have a restriction for some reason to have the USD Total to be a currency field? If not convert it into text formula and use "USD " + TEXT(Amount * 1.22) – Rao Oct 12 '16 at 17:10
  • Nope there's no restriction. If I use the "USD " + TEXT(Amount * 1.22) formula, what I get is : USD 3674.28499999999972569497685981332324445. If use "USD" &" "& TEXT(CEILING(Amount * 1.22)), it will be USD 3675. Is it possible to round up to nearest 100 dollars so it will become USD 4000.00? – Sam Oct 12 '16 at 17:30
2

To get the nearest $100 value first take your total multiplied by the exchange amount and divide by 100. Then use the ceiling function to get the nearest integer going towards positive infinity. Then multiple by 100. This gets you the number rounded up to the nearest 100. Then use the TEXT() function to turn it into text and finally concatenate with "USD "

To round to the nearest 10 dollars replace 100 with 10.

example

3475.28 / 100 = 34.7528

CEILING(34.7528) = 35.0000

35.0000 * 100 = 3500.00

TEXT(3500.00) = "3500.00"

"USD " + "3500.00" = "USD 3500.00"

"USD "+TEXT(CEILING((usd_total * 1.22)/100)*100)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.