Hi All,
A couple of years ago I developed a procurement scorecard for a client, and since that time, with each new bid event, it has evolved in it's complexity of evaluating bidder information. In the most current evolution, I've hit a head scratcher, and could really use some assistance. Here is the situation:
One of the metrics we measure are OSHA safety data: EMR, TRIR, LWA, and DART. Each of these metrics are to be provided for the past 3 years, and an average of those three years worth of data is then scored. The scoring is similar, but slightly different for each metric. The problem is that there is at least one category in which a metric of "0" earns the maximimum points, while there are times where data isn't provided at all, which by default is "0". Here is a snap of the metric scoring criteria:
1Capture.JPG
We used to just manually do the data entry, they I added some formulas to do some basic calculations, but I needed to "trick" the scoring in cases where the desired metric is "0", and a "0" is what is provided, by entering a .001 in the cell for the metric, thereby telling the formula that the cell value greater than 0, but less than the next value it is measuring for. Example:
Here there was no data provided. The data entry cells are all 0, so the score is 0.
2Capture.JPG
But HERE, the data was provided, and the bidder has 3 years of the highest-scoring metric, which is zero OSHA reportable incidents. I have to "trick" the formula here by entering at least one score of .001 so that the scoring formula registers the proper score, "4".
3Capture.JPG
That works, but is difficult, because if the person entering the data doesn't know the "trick," the item will score improperly.
My next (current) effort is to leave data entry cells empty if no metrics are provided, and then tell the formula in the averaging cell that if there is no data, leave the cell blank, otherwise, perform the weighted averaging. Example:
4Capture.JPG
As you can see, the formula IS identifying that the three cells are blank (TRUE, TRUE, TRUE), but it is not leaving the blank space I'm asking it to put in for a true result. Instead it is calculating the formula for the false result, which produces a "0", which then causes the same problem I had to start with---a high score of "4" is assigned, since that is the most desirable metric. I can't really tell that from the formula bulder box - that to me looks like it is returning a blank. But the cell actually contains a 0 when I hit return, and the way you can tell in this example is to look at the score in the adjacent cell (lower left corner of the image). So what is going wrong? How can I (a) fix it, or (b) change my approach to get the results I'm looking for.
The goal is that it is clear from one glance at the sheet whether data was provided or not, and that if data is provided, it is scored correctly.
Oh, and one more challenge....what would you do if partial data is provided---for example, they provide one or two years of data, but not all three?
If you got this far, and you haven't gotton lost, or fallen asleep, than you can probably help me out! I'm going to attach the worksheet here, and welcome any advice on how to accomplish this!
Thanks,
Kirstin
Bookmarks