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 1...
Forum Discussion
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.
------------------------------
Evan Westbrook
PRIME Developer
Harder Mechanical Contractors Inc.
Portland OR
------------------------------
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
------------------------------
- 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
------------------------------
