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!
Thanks for your reply.
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. :-(
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.
Hi, Ryan!
Count and Number Held are both numeric. But your post got me to thinking - how had I defined the calculated field? It was defined as numeric from when I was just dividing Count by Number Held and I must have changed it back when I couldn't get the formula to work. I re-defined it as Rich Text and now it works!
Thank you so much! :-)
