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”
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.
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.