PatriciaMalarke
Qrew Trainee
10 months ago

# Color-coding a field based on calculated percent

Hi, All!

I am trying to color-code a calculated field.  It's a percentage field:  Count divided by Number Held.

I stole this formula and adapted it from another post, but can't seem to find it again to put the link in this post.

If(
[Count]/[Number Held] > 0.1,
"<div style=background-color:Black>" & ToText([Count]/[Number Held]) & "</div>",

[Count]/[Number Held] > 0.02,
"<div style=background-color:Red>" & ToText([Count]/[Number Held]) & "</div>",

[Count]/[Number Held] > 0,
"<div style=background-color:Yellow>" & ToText([Count]/[Number Held]) & "</div>",

[Count]/[Number Held] <= 0,
"<div style=background-color:LightGreen>" & ToText([Count]/[Number Held]) & "</div>",

ToText([Count]/[Number Held]))

This gets me most of the way there.  But I'd like to display ToText([Count]/[Number Held]) as a percent (desired) or, at the very least, multiply it by 100 and round to 2 decimal places.  Multiplying by 100 doesn't seem to change the result and rounding gets me all kinds of error messages.

Desired result attached.

------------------------------
Patricia Malarkey
------------------------------

### 4 Replies

• Hi Patricia!

I believe this is what you're looking for: `Round(([Count]/[Number Held])*100,0.01)`

Also, in an effort to save time and typing, here's the full formula leveraging variables to reduce duplicative work:

`var number value = Round(([Count]/[Number Held])*100,0.01);`
`var text display = \$value & "%";`

`If(`
`    \$value > 10,`
`        "<div style=background-color:Black>" & \$display & "</div>",`

`    \$value > 2,`
`        "<div style=background-color:Red>" & \$display & "</div>",`

`    \$value > 0,`
`        "<div style=background-color:Yellow>" & \$display & "</div>",`

`    \$value <= 0,`
`        "<div style=background-color:LightGreen>" & \$display & "</div>",`

`    \$display`
`)`

------------------------------
Ryan Pflederer
------------------------------

• Hi, Ryan!

I tried it as you suggested - I didn't know about the variables and that makes it so easy! - but on the third line of the IF statement, I get "Expecting number but found text".

I've tried it the hard way:

If(
Round(([Count]/[Number Held])*100,0.01) > 10,
"<div style=background-color:Black>" & ToText(Round(([Count]/[Number Held])*100,0.01)) & "%" & "</div>",

Round(([Count]/[Number Held])*100,0.01) > 2,
"<div style=background-color:Red>" & ToText(Round(([Count]/[Number Held])*100,0.01)) & "%" & "</div>",

Round(([Count]/[Number Held])*100,0.01) > 0,
"<div style=background-color:Yellow>" & ToText(Round(([Count]/[Number   Held])*100,0.01)) & "%" & "</div>",

Round(([Count]/[Number Held])*100,0.01) <= 0,
"<div style=background-color:LightGreen>" & ToText(Round(([Count]/[Number Held])*100,0.01)) & "%" & "</div>",

ToText(Round(([Count]/[Number Held])*100,0.01)))

And the easy way:

var number value = Round(([Count]/[Number Held])*100,0.01);
var text display = \$value & "%";

If(
\$value > 10,
"<div style=background-color:Black>" & \$display & "</div>",

\$value > 2,
"<div style=background-color:Red>" & \$display & "</div>",

\$value > 0,
"<div style=background-color:Yellow>" & \$display & "</div>",

\$value <= 0,
"<div style=background-color:LightGreen>" & \$display & "</div>",

\$display
)

No joy.  :-(

------------------------------
Patricia Malarkey
------------------------------
• Hmmm, can you verify that both `Count `and `Number Held` are numeric fields? It worked on my side when both are set as numeric fields so I'm not sure what else would be causing the error you're seeing.

------------------------------
Ryan Pflederer
------------------------------