Decimal ending in zero for currencies

  • Resolved Larute

    (@jeremynivisongmailcom)


    10 years, 9 months ago

    Howdy. I have a spreadsheet of prices that need to be rounded to the nearest nickel. I have the formula:

    =ROUND((A1*.66)*20,0)/20

    Where A1 = 21.95

    The result is 14.5 which is correct, but I’d like to have zero on the end because it is a currency. Is there a way to do this. If so can you provide the correct function. I found a similar problem in the forums, but it didn’t look resolved.

    Thanks,

    Best,

    j

    https://www.ads-software.com/plugins/tablepress/

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    thanks for your post, and sorry for the trouble.

    For that, you can use the NUMBER_FORMAT() function (see https://tablepress.org/tablepress-features-formulas/ for more):

    =NUMBER_FORMAT( A1 * .66, 2 )

    The 2 indicates that you want to decimals after the period.
    I also removed the multiplication and division by 20, as that’s not necessary mathematically, at least in this example.

    Regards,
    Tobias

    Thread Starter Larute

    (@jeremynivisongmailcom)

    Thanks man. I used the multiplication and division to round it up to the nearest nickel. So for anyone else looking, this works:

    =NUMBER_FORMAT (ROUND((A1*.66)*20,0)/20,2)

    Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    ah, yes, totally misread that rounding towards the “nickel”. Then the multiplication and division makes sense of course. ??
    Good to hear that this helped!

    Best wishes,
    Tobias

    P.S.: In case you haven’t, please rate TablePress here in the plugin directory. Thanks!

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Decimal ending in zero for currencies’ is closed to new replies.

Tags