Evaluating and Scoring Multiple Metrics

[chicagolarsons]

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

[Pete_UK]

ISBLANK only looks at a single cell (the first cell of your range), so it is safer to change the formula in D9 to this:

=IF(D6&D7&D8="","",(3*D6+2*D7+D8)/6)

This can then be copied into D13, D17 etc., as well as into the corresponding cells of columns F, H, J.

You have this formula in E9:

=IF(D9="","",IF(D9<0.5,4,IF(D9<1,3,IF(D9<2,2,IF(D9<3,1,"0")))))

but you should remove the quotes from around the zero at the end, as this will make it return a text value that looks like a zero.

I would have thought that you could cope with partial data by calculating the denominator depending on the data present, rather than using the 6 in the formula in D9 (shown in red above). That formula would then become:

=IF(D6&D7&D8="","",(3*D6+2*D7+D8)/(IF(D6="",0,3)+IF(D7="",0,2)+IF(D8="",0,1)))

Hope this helps.

Pete