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 Ship...
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
    Moderator
    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.
  • CeceliaMartin's avatar
    CeceliaMartin
    Qrew Cadet
    7 years ago
    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>")
  • CeceliaMartin's avatar
    CeceliaMartin
    Qrew Cadet
    7 years ago
    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>")
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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>")


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