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.