Skip to main content
Question

Conditionally change color of formula numeric field, either text or background, without using a a Formula Text field.

  • July 29, 2016
  • 0 replies
  • 136 views
I want to change either the background or text of a formula numeric field based on conditions (e.g. over budget). I have made it work using a formula text field that mimics the number, and even got a "$" or "%" to work.

It seems hit or miss, and sometimes I get weird numbers. Is there a way to directly change the color of a numeric field? Here is an example of a formula:



If([Remaining Project Budget Funds Original]<0,"<div style=\"color:red;\">"&"$"&ToFormattedText(Round([Remaining Project Budget Funds Original],0.01),"comma_dot")&"</div>",

[Remaining Project Budget Funds Original]=0,"<div style=\"color:black;\">"&"$"&ToFormattedText(Round([Remaining Project Budget Funds Original],0.01),"comma_dot")&"</div>",

[Remaining Project Budget Funds Original]>0,"<div style=\"color:#3EC41C;\">"&"$"&ToFormattedText(Round([Remaining Project Budget Funds Original],0.01),"comma_dot")&"</div>")
This topic has been closed for replies.

  • ArchiveUser
  • Registered
  • July 29, 2016
The short answer is no. A formula numeric has no option for inserting HTML. The only way to do that would be to use the Text Formula with HTML enabled as you mentioned.
  • IanIan
  • Author
  • Registered
  • July 29, 2016
Thanks Blake. What would the formula be to conditionally change a related formula field? For example, if the project is over budget, have a [Budget Status] field change red.
Is the problem that the pennies get truncated if they are zero?
  • IanIan
  • Author
  • Registered
  • July 29, 2016
I am trying to do this with percents, remaining hours, and budget. Yes, sometimes there is a truncation issue. Sometimes I get a "1.03" instead of a "103" in the formula text field. If I add 100 to the source field, it messes up the original calculation.
  • ArchiveUser
  • Registered
  • July 29, 2016
It would be done in the same way that you outlined in your example, except that the value presented would be a Text value. To change the background, this is the example (albiet with numeric values) that I've used:

If(Round([numeric])<[numeric goal],"<div style=\"background-color:pink;width:50\">"&ToText(Round([numeric]))&"</div>",ToText(Round([numeric]))
For Percent fields, you need to multiply by 100.
  • IanIan
  • Author
  • Registered
  • July 29, 2016
If I'm converting numeric to text such as 6.0, what do I add to the formula to keep that decimal place? Right now it converts on to 6.
  • IanIan
  • Author
  • Registered
  • July 29, 2016
Right, but can I multiply by 100 in the formula text field? If I multiply by 100 in the formula numeric field it obviously screws that up.
  • IanIan
  • Author
  • Registered
  • July 29, 2016
OK, I got this to work from an earlier post from Mark.

If([Remaining Hours Original] >0,
ToText(Int([Remaining Hours Original]))
& "."
& If(Int([Remaining Hours Original])=[Remaining Hours Original],"0",
     Length(Right(ToText([Remaining Hours Original]),"."))=1,Right(ToText(Frac([Remaining Hours Original])),1) & "0",
     Right(ToText(Frac([Remaining Hours Original])),".")))

I also want to conditionally change the color. How do I add that? thanks
  • ArchiveUser
  • Registered
  • July 29, 2016
You'll actually have to break apart the value into the Integer and the Remainder and do something along these lines:

Right(ToText(Frac[Numeric]*100+100),2)
  • IanIan
  • Author
  • Registered
  • July 29, 2016
When I add this: &"<div style=\"background-color:red;\">"& [Remaining Hours Original] I get this:

6.
6
0

Vertical in the field, like that.
  • ArchiveUser
  • Registered
  • July 29, 2016
You need to close your DIV element after the field
  • IanIan
  • Author
  • Registered
  • July 29, 2016
I still get something like this:

34
34
.0
  • IanIan
  • Author
  • Registered
  • July 29, 2016
I need = to be black, over to be red, and under to be green. I don't understand why it's returning multiple values....
  • IanIan
  • Author
  • Registered
  • July 29, 2016
And returning 34.0, not this mess:

34
34
.
340
Yes,  multiply by 100 in the formula text field.
  • ArchiveUser
  • Registered
  • July 29, 2016
Ok, what's the full formula at this point?
  • ArchiveUser
  • Registered
  • July 29, 2016
If([Labor Budget Balance Original]<0,"<div style=\"color:red;\">"&"$"&ToFormattedText(Round([Labor Budget Balance Original],0.01),"comma_dot")&"</div>",

[Labor Budget Balance Original]=0,"<div style=\"color:black;\">"&"$"&ToFormattedText(Round([Labor Budget Balance Original],0.01),"comma_dot")&"</div>",

[Labor Budget Balance Original]>0,"<div style=\"color:#3EC41C;\">"&"$"&ToFormattedText(Round([Labor Budget Balance Original],0.01),"comma_dot")&"</div>")
  • ArchiveUser
  • Registered
  • July 29, 2016
Using that exact formula, but for substituting my own numeric field instead of the [Labor Budget Balance Original] that you have, I received the expected result.
  • ArchiveUser
  • Registered
  • July 29, 2016
Yes, sorry I went a different direction. I was trying to get the cell to change colors conditionally, while keeping the correct decimal spaces and $ sign. I still would like to figure that out.
  • ArchiveUser
  • Registered
  • July 29, 2016
Ok, here are the two options I came up with.

For coloring the Font Only:

var text tnum = ToText(Int(Abs([Numeric]))) & "." &Right(ToText(Frac(Abs([Numeric]))*100+100),2);

If([Numeric]<0,"<div style=\"color:red;\">"&"$ "& $tnum&"</div>",

[Numeric]=0,"<div style=\"color:black;\">"&"$ "&$tnum&"</div>",

[Numeric]>0,"<div style=\"color:#3EC41C;\">"&"$ "&$tnum&"</div>")

For coloring the Background & making the Font white:

var text tnum = ToText(Int(Abs([Numeric]))) & "." &Right(ToText(Frac(Abs([Numeric]))*100+100),2);

If([Numeric]<0,"<div style=\"background-color:red;color:white;\">"&"$ "& $tnum&"</div>",

[Numeric]=0,"<div style=\"background-color:black;color:white;\">"&"$ "&$tnum&"</div>",

[Numeric]>0,"<div style=\"background-color:#3EC41C;color:white;\">"&"$ "&$tnum&"</div>")
  • ArchiveUser
  • Registered
  • July 30, 2016
Thank you Blake!
How to right align the text?
Terms & ConditionsAccessibility statement