Trying to create a rating system based on certain criteria

[tmiller85]

Hello. I hope someone can assist me. I am trying to create a "rating score" based on 5 data points, with a percentage/weight given to each point. I want to have a rating of 1-10 (with 10 being the best). In the attached sample, I have applied 10% to one field, 15% to another, then 5%, then 70%. The TAT field represents number of days (the lower the better) and this is the most important piece of criteria. I'm not sure if a separate table would be needed for that.

Sorry if it seems confusing as I am trying to transfer what I'm thinking in my head to this message. Thank you in advance for any suggestions.

[Bernie Deitrick]

You need to come up with a scoring system for each item, where the score depends on the data, but is on a consistent 0 to 10 or 1 to 10 basis.

For example, it your have 0 declined items, you score 10 (perfect) and lose 1 point for each bad result item. OF course, your actual scoring can differ from that. Then use a sumproduct function with the scores and weights to get an overall score. Since your weights add up to 100%, that is all you need to do to stay on the same scale. If your weights were relative and did not add up to 100%, then the math would be different.

So, I have attached an example:

[tmiller85]

Hi Bernie. Thank you so much for the quick response. This helps significantly and looks great.

I did not explain very well but you have it pretty close to what I envision. Somehow I want to factor in the Accepted figure into the equation. So if there are 14 accepted and only 1 declined, I need that factored into the score. If that makes sense.

Thanks again.

[Bernie Deitrick]

Try it like this: (new version added at 3:43 EDT US, so dump the older version if you downloaded before 3:43)

[tmiller85]

Bernie, this is perfect and allows me to manipulate if needed. Thank you so much for taking the time to assist.

[tmiller85]

Actually one more question if I may. I did not clarify but the TAT (turnaround time) represents the average number of business days. I would like this to be from 1-21 days if possible (the lower, the better as far as the score). I assume I can change the forumula from =MAX(0,10-F3) to =MAX(0,21-F3) but I'm not sure if that would accomplish what I want. Please let me know if you have any suggestions for that, assuming I am making sense. Otherwise, it's really good as is. Thanks again.

[Bernie Deitrick]

I would try to keep it on a 0 to 10 or 1 to 10 basis, so try

=MAX(0,21-F3)/2

If the best is one day, that would evaluate to 10 - and to 0 for 21 days.

[tmiller85]

PERFECT! Thank you again. It's exactly what I wanted and I really did not know where to begin.

[Bernie Deitrick]

That's what I do all the time - come up with ratings systems for a certain popular magazine and website