Is it IF, SWITCH, or Count?

[Mary_Williams]

I apologize ahead of time if I’ve made this way too hard; I’m a newb. I tried to search for this question already being asked, but I don’t think my search terms are right. Feel free to put this under another topic if it fits there.

I’m trying to create a database that automatically ranks the nutritional quality of commercial pet food. I have 4 columns indicating the nutrient levels in each product (protein, carb, fat, & phos), with a number hand-entered as a percentage of calories (doesn’t need to display the percent sign). I created 4 IF function columns to categorize various number ranges into “Good”, “Bad”, and “Borderline”. So now each row contains 4 values in any combination.
(eg: Good, Bad, Good, Borderline)

So here’s where I’m lost; now I’d like to evaluate something like the following:
If there are 3-4 “Good” returns, and 0-1 “Borderline” returns, I want it to display “EXCELLENT”
3 “Good” & 1 “Bad” = “GOOD”
2 “Good” & 2 “Bad” = “FAIR”
3-4 “Bad” & 0-1 “Good” or “Borderline” = “NOT RECOMMENDED”
…etc…

I can make the function code as many lines as I need to (eg: every possible configuration), but if there are ways to incorporate numerical ranges within the function, that is preferable. I could also apply a numerical value to good/bad/borderline rather than a word category value, then use a SWITCH function to display as a word category output instead. But I don’t know how to do that, either. I can look up a simple SWITCH, but I’m not sure how to convert the current output to a numerical score sum. I thought COUNT might be my answer, but it wanted me to refer to numerical values in the equation, not text function values.

ORRR is there a far easier way of doing this, that isn’t so dang complicated? Desired result: automatically calculate if a product is recommended, purely by entering in the nutritional content.

[Vivid-Squid]

Hi @Mary_Williams ,
I can try to help you. I have a few questions though.

Mary_Williams:

I created 4 IF function columns to categorize various number ranges into “Good”, “Bad”, and “Borderline”

What is the 4th option?
What happens if there are 2 Good and 1 Borderline? Do you have a full matrix of scores?

[Mary_Williams]

Thanks for your response! The rest of the output options I had only given a few examples since I could work those details out afterward.

The 4 columns are based on the ingredients of: meat protein, carbs, fat, and phosphate. I only need 3 rankings for each ingredient: Good (within recommendations), Bad (outside recommendations), and Borderline (close to recommendations). So every food will have 4 rankings of good/bad/borderline, for each of the 4 ingredients. But those aren’t the important final rankings; I don’t even need them to show up in the table.

I need to somehow rank each cat food by how many of the ingredients are at recommended levels. I wouldn’t even mind if they are overall numerically weighted and ranked rather than word descriptions EXCELLENT, etc. But I do need something to indicate that a food should not be considered at some trigger in the ranking system. I intend to Group By Recommendation.

I’m using the free version. I don’t think I have color change if/then options, so it would probably need to be displayed as a text return instead.

Let me know if I can answer any more questions! Thank you so much for helping me.

[kuovonne]

How about converting the level of each nutrient to a numeric value and getting the sum as a total score

SUM(
    SWITCH( {protein},
        "Good", 3,
        "Borderline", 1,
        "Bad", 0
    ),
    SWITCH( {carb},
        "Good", 3,
        "Borderline", 1,
        "Bad", 0
    ),
    SWITCH( {fat},
        "Good", 3,
        "Borderline", 1,
        "Bad", 0,
    ),
    SWITCH( {phos},
        "Good", 3,
        "Borderline", 1,
        "Bad", 0
    )
)

Then in a new formula field, convert the total score into categories.

IF(
  {total score} > 10,
  "EXCELLENT",
IF(
  {total score} > 8,
  "GOOD",
IF(
  {total score} > 6,
  "FAIR",
  "NOT RECOMMENDED"
)))

You can adjust the values for each rating, and adjust the ranges for total score as needed.