Need help with formulas and number formatting

  • Resolved suntower

    (@suntower)


    1 year, 3 months ago

    We want to use the =SUM() function on columns where the numbers were entered with commas. Unfortunately, using commas seems to prevent the =SUM() function from working. However adding individual cells -does- work.

    Can this function be made to work with commas?

    Or if not, is there a way to enter the numbers without commas, but then style them to display -with- commas?

    The page I need help with: [log in to see the link]

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

    (@tobiasbg)

    Hi,

    thanks for your post, and sorry for the trouble.

    Indeed, using formulas on cells with commas (as a thousand separator) is currently not supported.

    One workaround for this is to separate calculation and formatting, by using a hidden column that contains the numbers for calculation and a visible column that takes these numbers and formats them:
    For that, enter all numbers without the comma as thousand separator and use those cells in your formulas. Mark that column as hidden (via the button below the table or the right-click context menu). Insert a new column directly on the right of that column and fill each cell with e.g. this formula:

    =NUMBER_FORMAT( INDIRECT( "A"&ROW() ) )

    The NUMBER_FORMAT() function will add the thousand separator again. The INDIRECT() function is used to dynamically create the cell reference, by (in this example) using A as the column and the row being the same row that the formula has. (You could also use

    =NUMBER_FORMAT( A4 )

    in cell B4 but would then have to adjust each of these formulas.)

    So, by having columns for the calculations (marked as hidden from the site visitor) and additional columns used for formatting of the calculated results (which are visible to the site visitor), you can achieve the desired outcome here.

    Regards,
    Tobias

    Thread Starter suntower

    (@suntower)

    Got it. It’s not super important to us since these numbers are not routinely updated. However, I hope you’ll consider this a feature request for a future version. Cheers.

    Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    that’s great! And yes, I’m definitely always looking to improve things like data formatting and types, so that maybe in the future the formula calculation engine can also cope directly with formatted numbers!

    Best wishes,
    Tobias

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Need help with formulas and number formatting’ is closed to new replies.