I have a formula that currently works pretty well for the most part. It originally was a scoring factor of 90%-100= 5, and 85% to 89.99% = 4 and 80% to 84.99% = 3 and so on. This is represented using the formula below:
=IF($B10>=90%,5,IF($B10>=85%,4,IF($B10>=80%,3,IF($B10>=75%,2,IF($B10<=74.99%,1)))))
So the new requirement came down as follows:
95 to 100% = 5
85 to 89.99% or 101.1 to 105% = 4
80 to 89.99% or 105.1 to 110% = 3
75 to 79.99% or 110.1 to 115% = 2
Less than 74.9 = 1
So the dilemma I'm in is I have not been able to figure out how to create a range for values 2, 3 and 4 to be either or side for the scoring value to become true. So score value 4 should be the ranges of 85% through 89.99% OR 101.1% through 105% to be true.
Does anyone have any ideas on how to restructure this to include the ranges so that if either range is true it would show the correct scoring values? Thanks in advance for your help.
Aucun commentaire:
Enregistrer un commentaire