Discussions

Expand all | Collapse all

Formula to show status for inbtween number range

  • 1.  Formula to show status for inbtween number range

    Posted 02-04-2019 23:02
    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?



  • 2.  RE: Formula to show status for inbtween number range

    Posted 02-05-2019 15:51
    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>" )



  • 3.  RE: Formula to show status for inbtween number range

    Posted 02-05-2019 16:32
    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>" )))


  • 4.  RE: Formula to show status for inbtween number range

    Posted 02-05-2019 18:02
    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>" )


  • 5.  RE: Formula to show status for inbtween number range

    Posted 02-05-2019 20:26
    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>" )


  • 6.  RE: Formula to show status for inbtween number range

    Posted 02-05-2019 20:37
    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.


  • 7.  RE: Formula to show status for inbtween number range

    Posted 02-05-2019 22:23
    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>")


  • 8.  RE: Formula to show status for inbtween number range

    Posted 02-05-2019 22:43
    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>")


  • 9.  RE: Formula to show status for inbtween number range

    Posted 02-06-2019 06:02
    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>")




  • 10.  RE: Formula to show status for inbtween number range

    Posted 02-06-2019 18:23
    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?