Forum Discussion

CeceliaMartin's avatar
CeceliaMartin
Qrew Cadet
7 years ago

Formula to show status for inbtween number range

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?

  • DavidBrogdon's avatar
    DavidBrogdon
    Qrew Assistant Captain
    7 years ago
    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>" )

  • CeceliaMartin's avatar
    CeceliaMartin
    Qrew Cadet
    7 years ago
    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>" )))
    • DavidBrogdon's avatar
      DavidBrogdon
      Qrew Assistant Captain
      7 years ago
      Yes, rich text formula field. You don't need to repeat the If statement after the first time. I forgot the " before the span on the second and third. 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>" )
    • CeceliaMartin's avatar
      CeceliaMartin
      Qrew Cadet
      7 years ago
      Formula syntax error

      Expected a valid expression after the "&"


      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>" )
    • EvanMartinez's avatar
      EvanMartinez
      Quickbase Staff
      7 years ago
      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.