Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Is it IF, SWITCH, or Count?

Topic Labels: Formulas
1405 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Mary_Williams
6 - Interface Innovator
6 - Interface Innovator

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.

3 Replies 3

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.