Skip to main content

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.

Hi @Mary_Williams ,

I can try to help you. I have a few questions though.



What is the 4th option?

What happens if there are 2 Good and 1 Borderline? Do you have a full matrix of scores?

Hi @Mary_Williams ,

I can try to help you. I have a few questions though.



What is the 4th option?

What happens if there are 2 Good and 1 Borderline? Do you have a full matrix of scores?


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.

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.

Reply


Terms & Conditions