Forum Discussion

CurtisMiddleton's avatar
CurtisMiddleton
Qrew Trainee
5 years ago

Large Number Rounding Up Past 10 Million

Hi,

I am making a rich text formula field to display a numeric summary field.

When the number is 1,000,000.50 it converts identically.
When the number is 10,000,000.50 it rounds up the .50 to 10,000,001.

Is there a simple way to stop this from happening? or this just a Quick Base limitation?

I have tried using a solution from another post: ToText(Round([Currency], 0.01))
but this did not work unfortunately.

My only other thought would be to build an if statement to subtract extra zeros when the number exceeds an amount and use a Right() function to pull the decimal places from that number since it wouldn't round up. Not ideal.

Any help would be greatly appreciated!

Regards,

------------------------------
Curtis Middleton
------------------------------
Formulas and functions

2 Replies

Sort By
  • EvanWestbrook's avatar
    EvanWestbrook
    Qrew Cadet
    5 years ago
    Curtis,

    I don't have an answer for you, but I found some additional information. Maybe someone from the QuickBase team can help clarify because I'm curious about this, too.

    1. QuickBase Function documentation does mention there are some issues inherent with rounding and floating point numbers
    2. I replicated your problem with a hard coded number. When my number when from 1,000,000.50 to 10,000,000.50, QuickBase actually changed my formula. Check it out below:


    ------------------------------
    Evan Westbrook
    PRIME Developer
    Harder Mechanical Contractors Inc.
    Portland OR
    ------------------------------
    • CurtisMiddleton's avatar
      CurtisMiddleton
      Qrew Trainee
      5 years ago
      Thank you for the response. I ended up chopping off the decimals, reformatting it and then adding the decimals after.
      Here is my formula in case anyone runs into this in the future:
      ---
      var number RoundCost = Round([PR Total Cost],0.01);

      var text FormattedSubMil = //Variable to call when PR Total Cost is less than 1 million.
      "$" &
      ToFormattedText($RoundCost,"comma_dot",3) &
      If(
      not Contains(ToText($RoundCost),"."),
      ".00",
      Length(Right(ToText($RoundCost),"."))=1,"0"
      );

      var text FormattedMil = //Variable to call when PR Total Cost is less than 10 million and greater than 1 million.
      "$" &
      ToFormattedText(ToNumber(Left(ToFormattedText([PR Total Cost],"comma_dot",3),".")),"comma_dot",3) //This takes a number, and returns it with the decimal and change chopped off.
      & "."
      & If(Length(Right(ToText($RoundCost-1000000),"."))=2, Right(ToText($RoundCost-1000000),"."),
      If(Length(Right(ToText($RoundCost-1000000),"."))=1, Right(ToText($RoundCost-1000000),".") & "0", "00")); //This adds the decimal and change on the end.

      var text FormattedTenMil = //Variable to call when PR Total Cost is less than 100 million and greater than 10 million.
      "$" &
      ToFormattedText(ToNumber(Left(ToFormattedText($RoundCost,"comma_dot",3),".")),"comma_dot",3) //This takes a number, and returns it with the decimal and change chopped off.
      & "."
      & If(Length(Right(ToText($RoundCost-10000000),"."))=2, Right(ToText($RoundCost-10000000),"."),
      If(Length(Right(ToText($RoundCost-10000000),"."))=1, Right(ToText($RoundCost-10000000),".") & "0", "00")); //This takes a number, and returns it with the decimal and change chopped off.

      var text FormattedHunMil = //Variable to call when PR Total Cost is less than 1 billion and greater than 100 million.
      "$" &
      ToFormattedText(ToNumber(Left(ToFormattedText($RoundCost,"comma_dot",3),".")),"comma_dot",3) //This takes a number, and returns it with the decimal and change chopped off.
      & "."
      & If(Length(Right(ToText($RoundCost-100000000),"."))=2, Right(ToText($RoundCost-100000000),"."),
      If(Length(Right(ToText($RoundCost-100000000),"."))=1, Right(ToText($RoundCost-100000000),".") & "0", "00")); //This takes a number, and returns it with the decimal and change chopped off.

      //Styling ofthe text starts below here:
      "<div style=\"Color:#006eb7;font-size:250%;font-weight:bold;\"align=\"center\">"

      //The section below calls a specific variable to be style formatted based on the PR Total Cost amount.
      & If($RoundCost>=100000000, $FormattedHunMil,
      If($RoundCost>=10000000, $FormattedTenMil,
      If($RoundCost>=1000000, $FormattedMil,
      $FormattedSubMil)))

      &"</div>" &
      "<div style=\"color:#gray;font-size:150%;Font-weight:bold;\"align=\"center\">Total PO Amount</div>"
      ---
      Here is what it looks like:
      ---
      Regards

      ------------------------------
      Curtis Middleton
      ------------------------------