Aggregating two KPI scores into an Overall score

[Anonymous]

Good afternoon folks!

I'm completely new to this so please forgive me if this is incredibly easy for you whizzes! Unfortunately I've tried Googling but with no success, indeed I'm probably more confused than before I started.

Essentially what I have is something like the below:

Supplier	FTMR %	EDI %	Overall %
Supplier A	-	-
Supplier B	92.59%	99.60%
Supplier C	-	0.00%
Supplier D	94.68%	0.00%
Supplier E	54.40%	-

I am trying to write an expression that would give me the overall score for each supplier based on their FTMR and EDI scores. Where "-" appears, there is measurement; whereas 0.00% indicates the supplier has failed. Each of the FTMR and EDI scores should have equal weightings.

If someone could help me with this, I would be most grateful.

Many thanks,

Gareth

[sunny_talwar]

What is the expected percentage number you would want to see for Overall % for the above data?

[Anonymous]

Sunny, I just want to see a weighted average % between FTMR and EDI. As they are both weighted 50%, I would expect this to mean a simple (FTMR + EDI)/2 (the number of scores).

So for instance, Supplier A would return "-" because the scores are null, Supplier B would return an overall score of 96.10%, Supplier C would return 0%, Supplier D would return 47.34% and Supplier E would also return 54.40%.

Thanks

[sunny_talwar · Accepted solution]

So if you do this, it doesn't work?

RangeAvg(FTMR%Expression, EDI%Expression)

[Anonymous]

Bingo, that did exactly what I was after.

Thank you very much!

[Anonymous]

Hi Sunny

Further to the above, if I had three columns and they all had different values, how would I calculate the average, for instance, with Column B making up 60% of the score and columns A and C making up 40% of the score (20% each)?

Thanks in advance for your help,

Gareth

[sunny_talwar]

May be something like this?

ColumnB*0.60 + ColumnA*0.20 + ColumnC*0.20