Google Sheets IF AND OR Logic

Question

I am making a scoring system on Google sheets and I am struggling with the logic I need for the final step.

This question might be related, but I can't seem to apply the logic.

There are a number of chemicals tested and for each an amount detected (AD) si given, and each has a benchmark amount allowed (AL). From AL and AD we calculate AD/AL= %AL.

The Total Score (TS) is calculated based on an additive and weighted formula that takes into consideration the individual %ALs, but I won't go into that formula.

The final step is for me to "calculate" the Display Score (DS), which has some rules to it, and this is where I need the logic. The rules are as follows:

• If any of the %Als are over 100 (this is will make TS>100 too) and DS should show "100+"

• If none of the %ALs are over 99, (TS may be above or below 100) then DS can NOT be over 99, so it should show TS, maxing out at 99.

  I want to do this within the sheet itself. I think the correct tool is logic operators IF, AND, OR.

I have made many attempts, these are some: (I am replacing cell references with the acronyms I used above)

=IF(TS>100,"100+",TS)

=IF(OR(AND(MAX(RANGE_OF_%ALS)<100,TS>99),(AND(MAX(RANGE_OF_%ALS)>100,TS>100)),99,"100+"))

I have also tried to think about how I would solve this in Python (just to explore it, I don't want to use Python for the solution). This was my attempt:

if Max%AL<100: if TS<100: print(TS) else: print("99") else: if TS>100: print("100+")

Those are my attempts at thinking through the problem. I would appreciate some help.

This is a link to a copy of my sheet: https://docs.google.com/spreadsheets/d/1ZBnaFUepVdduEE2GBdxf5iEsfDsFNPIYhrhblHDHEYs/edit?usp=sharing

Answer 1

Please try:

=if(max(RANGE_OF_%ALS)>1,"100+",if(max(RANGE_OF_%ALS)<=0.99,MIN(TS,0.99),"?"))