Forum Discussion

KimG's avatar
Qrew Cadet
3 years ago

Formula help - If statement with varying inequalities - greater than, greater than or equal to


I have a Formula-rich text field that checks if data being entered is within the acceptable data range.  It shows a warning message if the data entered is out of range.  (It's quality testing - the data needs to be recorded for what is measured; we are not preventing data from being entered outside of the acceptable range.)

Here's my attempt to create a simple example. Let's say I'm testing several different types of juice for sweetness.  I have my juice table (apple, orange, grape) and I have a "desired sweetness" score for each one defined by the percentage of fructose content. 


  • Juice table: this lists the Juices 
  • Inspection Types: there are three types of inspections (data from the vendor, data from in-house testing when juice arrives, data from in-house testing when juice is processed) Sweetness is tested for each, though it may have different criteria coming from the vendor or when tested in house.  
  • Inspections - records data from each test sample (multiple samples are taken during in-house processing for both incoming and in-process inspections)

For each juice in my Juice table, the sweetness level is recorded in the field "Sweetness limit."  When a new batch of juice is delivered, we test it for quality, and want to compare the "Sweetness" recorded against what it should be.

My formula looks something like this:

If([Sweetness]<=[Inspection Types - Sweetness limit], "<div style=\"color:white; background-color:#FF0000;\"> The juice isn't sweet enough! It should be > " & ToText([Inspection Types - Sweetness limit]) &"</div>",

[Sweetness]>[Inspection Types - Sweetness limit], "The juice is sweet enough! It is > " & ToText([Inspection Types - Sweetness limit]),

"The desired sweetness is >" & ToText([Inspection Types - Sweetness limit]))

This works fine, except that when we look at the different juices or inspection types I may not always be using "greater than" as in:

So I've added a second field, a text-multi choice where I can select "greater than" or "greater than or equal to."

Question 1: How can I build the operator (greater than v. greater than or equal to) into my formula?

Question 2: I also have some things that I'm testing that sometimes are written as X > Y, but others that X = Y or Z > X > Y.  Any ideas on how to build that into my formula as well?  As in: 

We are taking test data on about 30 different variables so I'm reusing this formula a lot.  For some items it's fine as above because we always use the same inequality.  It's only a handful that have these inconsistencies that mess with my formula. Unfortunately, I can't make them change from saying "greater than or equal to 35%" to "greater than 34.99%" so that we're using the same inequality all the time.

Suggestions on how to resolve this are much appreciated.  Thanks!

Kim G

1 Reply

  • KimG's avatar
    Qrew Cadet
    Additional note: after several attempts to post this message and contacting Customer Care, I figured out that the QuickBase Community post system doesn't like the special symbols for "greater than or equal to" or "less than or equal to."  That's why I've either spelled them out or used images to show them.  It's the only way I was able to post this message.  So please note this if you have a response that would include those symbols.  Thanks!

    Kim G