# Change the Text Color of Numeric Value

• 0
• Question
• Updated 2 years ago
I have a Numeric-Formula field, a simple subtraction formula. I want to make the number red if it's a negative number. I cant seem to find the specific way to do this... Is it possible?
• 140 Points

Posted 2 years ago

• 0
• 70 Points

Neal

NealPatil @ gmx..com
• 140 Points
Doesn't help at all, looked at that and tried and tried, couldn't get it to work. If no one else has any comments, I'll just make a support ticket for it.

QuickBaseCoach App Dev./Training, Champion

• 65,806 Points
Try this one.  There is not a way to simply format a cell like in Excel with Conditional formatting.

I think that this code works, I just tested it. (revised to correct a problem with an extra negative sign)

number Value = Round([currency field],0.01);

var text Decimals = "." & Right(ToText(Int(Abs(\$value) * 100)),2);
var text Thousands = If(Abs(\$Value)>=1000,ToText(Int(Abs(\$Value)/1000)));
var text Hundreds=Right(ToText(Int(Abs(\$Value))),3);

If(\$Value=0,"\$0.00",
If(\$Value<0, "<font color=red>- ")
&
"\$" & List(",",\$Thousands,\$Hundreds) & \$Decimals)
• 140 Points
I think the code works, no errors but wondering where I would put it since I cant put it into the formula field that is calculating the value.

QuickBaseCoach App Dev./Training, Champion

• 65,806 Points
You need to make a 2nd field of type formula text.
• 140 Points
Got it... Did that and now it displays this in the field when it's a negative number. <font color=red>-\$-1,000.00

QuickBaseCoach App Dev./Training, Champion

• 65,806 Points
You need to check the checkbox to allow HTML
• 140 Points
got it, works. I did need to remove a - sign from the end of the html. Appreciate the help.
• 140 Points
ok so something weird... lol. if it's under 1000, with your formula as above the negative is inserted before the \$ like this -\$755.19. If it's over 1000 and it's a negative number it's like this, -\$-2,602.44. I was looking at the code and see where it inserts the negative sign after the html code for RED, wondering why it's different.

QuickBaseCoach App Dev./Training, Champion

• 65,806 Points
I will look at it later today when I get a chance.
• 140 Points
ok thanks, appreciate the help.

QuickBaseCoach App Dev./Training, Champion

• 65,806 Points
OK, here is the revised formula to correct the extra negative sign problem

var number Value = Round([currency field],0.01);
var text Decimals = "." & Right(ToText(Int(Abs(\$value) * 100)),2);
var text Thousands = If(Abs(\$Value)>=1000,ToText(Int(Abs(\$Value)/1000)));
var text Hundreds=Right(ToText(Int(Abs(\$Value))),3);

If(\$Value=0,"\$0.00",
If(\$Value<0, "<font color=red>- ")
&
"\$" & List(",",\$Thousands,\$Hundreds) & \$Decimals)
• 140 Points
You sir, are a Quickbase genius. Appreciate the help.

QuickBaseCoach App Dev./Training, Champion

• 65,806 Points
Thx, Now I have a debugged formula too for when I need it.
• 140 Points
So I dont know if it's the formula or something else but some of the values are off by a penny. I would say 90% of them are correct, the RED functionality is correct too. A few of them are off and any of them that were .01 are listed as .10 with the formula. Just thought you would want to know in case you wanted another crack at it.

QuickBaseCoach App Dev./Training, Champion

• 65,806 Points
Can you give me an example of a source number that is not calculating correctly?  can you also tell me if your number that you are formatting is a rounded number in a formula or just formatted to 2 decimal places but not rounded.  If it is a currency calculation you should be rounding your result to the nearest cent.

for example
Round (my formula calculation here), 0.01)
• 140 Points
This is the formula I am using, it's a Formula-Numeric field. [Schedule Value]-[Invoice Total]
2 decimal places
separator after 3 places
no rounding that I know of
The calculation field listed above for example says \$39655.38 and the html field I created says \$39655.37, off by just one penny.

The other issue is anytime is calculates to .01, it displays in the text field with the html as .10

QuickBaseCoach App Dev./Training, Champion

• 65,806 Points