Tricky calculation, help needed

[harrysdad]

Hi,

I am trying to find a way to "score" a set of metrics, I have five columns which are all formatted to percentages. I need to achieve a percentage number at the end which shows an average of the five, however 2 of the five metrics should be as high a number as possible and three should be as low as possible, each has a target figure.

example

A Score B Score C Score D Score E Score
Target 100% <7% >4% >80% <5%

1 98% 8.5% 3.25% 79% 10%
2 99% 4.5% 2.1% 86% 2.5%
3 100% 3.6% 6% 82% 4.5%


I cannot simply sum the figures as the low percentages should be as low as poss and this gives an inaccurate answer.

Any help would be much appreciated.

 

[harrysdad]

I apologise for not stating every nuance of the question posted, I am not an excel guru which is why I felt I had to ask for help, I thank you for your time and the fact you took time to respond but I shall find a solution elsewhere,

Thank You

 

[jasonb75]

We don't expect every minor detail, just enough to be able to know what you need. For information I'm not an excel guru, for me this is just something to do that is more interesting than daytime tv.

When you compare a target percentage to an actual there are 7 possible results for the difference, all of which could be considered correct in one way or another.

The problem we face is determining which is correct for what you need, then coming up with a formula consistant to that.

Problem 2, is that some need to be greater than target, while some need to be less than, which can skew the result if done incorrectly.

I have something possible in mind, but will take a while to figure out correctly, so I'll have to do it when I get home later if you haven't already found a solution or given up on this.