Formula to show status for inbtween number range

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
I would like to have a yellow status for anything between 98 & 93

If([On Time Shipping]<99,"<img src=\"https://www.quickbase.com/up/bcgnn27bx/g/rdd/eh/va/222-point_green.png\">";,
If([On Time Shipping]>92,"<img src=\"https://www.quickbase.com/up/bcgnn27bx/g/rdc/eh/va/221-point_red.png\">";,
If([On Time Shipping]>=98 & <=93,"<img src=\"https://www.quickbase.com/up/bcgnn27bx/g/rde/eh/va/223-point_yellow.png\">")))

Really ideally I would like the actual number to change color instead of just the ball showing up but I will take what I can get.

Any help on the last piece there?

Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Photo of David Brogdon

David Brogdon

  • 1,222 Points 1k badge 2x thumb
Try this -->

If([On Time Shipping]<99,"<span style='font-size: 15pt; text-align: left; color: green'>" & ToText([On Time Shipping]) & "</span>",

[On Time Shipping]>92,<span style='font-size: 15pt; text-align: left; color: red'>" & ToText([On Time Shipping]) & "</span>",

[On Time Shipping]>=98 & <=93,<span style='font-size: 15pt; text-align: left; color: yellow'>" & ToText([On Time Shipping]) & "</span>" )

(Edited)
Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb
Close, but I am getting an error on the last line.  And when entering any other type of number the color does not change.

On Time Shipping % is a numeric field.
What I am creating is a rich text formula.  Is that correct?

If([On Time Shipping %]<99,"<span style='font-size: 15pt; text-align: left; color: green'>" & ToText([On Time Shipping %]) & "</span>",

If([On Time Shipping %]>92,"<span style='font-size: 15pt; text-align: left; color: red'>" & ToText([On Time Shipping %]) & "</span>",

If([On Time Shipping %]>=98&<=93,"<span style='font-size: 15pt; text-align: left; color: yellow'>" & ToText([On Time Shipping %]) & "</span>" )))
(Edited)
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 11,998 Points 10k badge 2x thumb
When you are writing out two different conditions to be evaluated in a formula you don't want to use the ampersand & you would instead want to use the word and. For example 

[On Time Shipping %]<=98 and [On Time Shipping %]>=93, . . .
Also for a formula it will always evaluate running form the top to the bottom and stop evaluating as soon as the conditions are met. Since the condition of greater then 92% would also cover being less then 98% and more then 92% your formula would stop at the second condition so you would want to have that line before the line covering >92.
Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb
Thanks.  However the coloring is still not working.  It is always showing green.  Do you know what I am doing wrong?

If([On Time Shipping %]<99,"<span style='font-size: 15pt; text-align: left; color: green'>" & ToText([On Time Shipping %]) & "</span>",
[On Time Shipping %]>=98 and [On Time Shipping %]<=93," <span style='font-size: 15pt; text-align: left; color: yellow'>" & ToText([On Time Shipping %]) & "</span>",
[On Time Shipping %]>92,"<span style='font-size: 15pt; text-align: left; color: red'>" & ToText([On Time Shipping %]) & "</span>")
Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb
I figured it out!!!  See below of what is now working.  Thank you both for your help!

If([On Time Shipping %]<=0.98 and [On Time Shipping %]>=0.93," <span style='font-size: 15pt; text-align: left; color: orange'>" & ToText(Round([On Time Shipping %],0.01)*100) & "</span>",
[On Time Shipping %]<0.99,"<span style='font-size: 15pt; text-align: left; color: red'>" & ToText(Round([On Time Shipping %],0.01)*100) & "</span>",

[On Time Shipping %]>0.92,"<span style='font-size: 15pt; text-align: left; color: green'>" & ToText(Round([On Time Shipping %],0.01)*100) & "</span>")
Similar to Excel, Percentages are stores as numbers between 0 and 1, for the range 0 to 100%.  Also the sign was reversed for the 99% check.

Try this



If([On Time Shipping %]>0.99,"<span style='font-size: 15pt; text-align: left; color: green'>" & ToText([On Time Shipping %]) & "</span>",

[On Time Shipping %]>=0.98 and [On Time Shipping %]<=93," <span style='font-size: 15pt; text-align: left; color: yellow'>" & ToText([On Time Shipping %]) & "</span>",

[On Time Shipping %]>0.92,"<span style='font-size: 15pt; text-align: left; color: red'>" & ToText([On Time Shipping %]) & "</span>")


Photo of David Brogdon

David Brogdon

  • 1,222 Points 1k badge 2x thumb
Shouldn't this really be (pseudo) --->

If value > 0.99, red

else if value <=0.99 and value >=0.93, yellow,

else, green

Reason being that 0.99 is going to be greater than 0.98 and also it will be impossible for the value to be greater than 0.98 but less than 0.93? Also, all values above will be greater than 0.92?