Change Font color (Conditional formatting)
Hello, I have a percentage variance that appears as a formula derived from 2 other fields. I would love to see a number change color when it's higher than 100% or less than 100% in a given situa...
Forum Discussion
Hi,
Yes, Quick Base is objecting because the first part of your formula is a complete formula and it doesn't know how to interpret the second part.
My suggestion involved creating a second formula field, so you would have:
Design Variance
([Act. Design Cost] + [Structural Calc Fee])/[Est. Design Cost]
... and a second formula field that would allow you to display the result with formatting. e.g.
Design Variance for Display
If([Design Variance]>1,"<span style='background-color:red;'>" & [Design Variance] & "</span>","<span style='background-color:green;'>" & [Design Variance] & "</span>")
This means you could still use the Design Variance field in other calculations if needed, while using the second field to display the field with formatting.
There's a good chance you'll want to adjust the formula to improve the appearance (if you have some knowledge of HTML you can get very creative) and you'll probably want to multiply the value by 100 to display it as a percentage - perhaps something like this:
If([Design Variance]>1,"<span style='background-color:red; color:white; padding: 5px'>" & Round([Design Variance]* 100) & "%</span>","<span style='background-color:green;color:white; padding: 5px;'>" & Round([Design Variance] * 100) & "%</span>")
------------------------------
Jeremy Anson
------------------------------
Yes, Quick Base is objecting because the first part of your formula is a complete formula and it doesn't know how to interpret the second part.
My suggestion involved creating a second formula field, so you would have:
Design Variance
([Act. Design Cost] + [Structural Calc Fee])/[Est. Design Cost]
... and a second formula field that would allow you to display the result with formatting. e.g.
Design Variance for Display
If([Design Variance]>1,"<span style='background-color:red;'>" & [Design Variance] & "</span>","<span style='background-color:green;'>" & [Design Variance] & "</span>")
This means you could still use the Design Variance field in other calculations if needed, while using the second field to display the field with formatting.
There's a good chance you'll want to adjust the formula to improve the appearance (if you have some knowledge of HTML you can get very creative) and you'll probably want to multiply the value by 100 to display it as a percentage - perhaps something like this:
If([Design Variance]>1,"<span style='background-color:red; color:white; padding: 5px'>" & Round([Design Variance]* 100) & "%</span>","<span style='background-color:green;color:white; padding: 5px;'>" & Round([Design Variance] * 100) & "%</span>")
------------------------------
Jeremy Anson
------------------------------
Thank you for the clarification with the 2nd field. I interpreted it as a 2nd formula in the field at the time.
I attempted to use the formula you gave me in a few different ways based on your original message and received 2 different errors. Here's the copied formula:
If([Design Variance]>1,"<span style='background-color:red;'>" & [Design Variance] & "</span>","<span style='background-color:green;'>" & [Design Variance] & "</span>")
I tried it with a Formula-Numeric and received the following error:
I was curious if the term used "span" is intended as a derivative for the max/min for the color I'd like to display, but then I thought it's already referenced in the >1 scenario just before it. If I'm understanding this, span is referring to the color applied to the field.
Just to be sure after re-reading your initial segment, I attempted it as a Formula-text field and although it did save the text field simply displayed as shown below.
------------------------------
Anthony Wong
------------------------------
- The type of field you select should always be what you anticipate the output being. In this case, your output is text. But not only is it texted, but it will have formatting, so your output is Rich Text.
So the field should be a formula Rich Text field.
------------------------------
Mike Tamoush
------------------------------ - Hi,
As per Mike's note, the field type you need is 'Formula rich text'. The difference between this and 'Formula text' is that it will allow more than just 'plain' text. You can read more on the difference at https://help.quickbase.com/user-assistance/field_types.html
Here, we're using some basic HTML (a <span> tag - see https://www.w3schools.com/tags/tag_span.asp) to wrap the plain text value and format it.
I hope that makes sense.
------------------------------
Jeremy Anson
------------------------------
