KimG
3 years ago

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

Hi

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.

Tables:

• 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
------------------------------
• KimG