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

------------------------------

Original Message:

Sent: 11-27-2019 12:55

From: Evan Westbrook

Subject: Large Number Rounding Up Past 10 Million

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.

- QuickBase Function documentation does mention there are some issues inherent with rounding and floating point numbers
- 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

Original Message:

Sent: 11-27-2019 11:28

From: Curtis Middleton

Subject: 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

------------------------------