Calculating Score

[msharma864512]

Hi,

I want to calculate a relationship score using three different metrices,

Highest Revenue will have highest score (50% weight)
Highest Revenue growth will have highest score (10% weight)
Highest Revenue contribution spread across column B to G will have highest score. (I am not sure how to calculate score using this approach). My intent here is to provide more score if the company has relationship with all the verticals across column B4 to H4).

It would be very helpful if someone can help me with this.

Regards,
Manish

 

[Hui]

Try:
L5: =IFERROR((I5-MIN(I$5:I$1099))/(MAX(I$5:I$1099)-MIN(I$5:I$1099))*L$3,0)
Copy that across to M5:N5 and
then copy L5:N5 down

O5: =SUM(L5:N5)
Copy O5 down

 

[msharma864512]

Try:
L5: =IFERROR((I5-MIN(I$5:I$1099))/(MAX(I$5:I$1099)-MIN(I$5:I$1099))*L$3,0)
Copy that across to M5:N5 and
then copy L5:N5 down

O5: =SUM(L5:N5)
Copy O5 down

Hui,

Thanks for providing this formula, and indeed its very helpful. And its working for two columns.

However calculating score through revenue concentration is a bit tricky, because here i have multiple values and columns to calculate column. If the revenue is spread across all the seven columns (Column B to Column G), and if the revenue is concentrated to only one column, then that company will have maximum score.

If one column out of Column B to Column H is 100%, then it will have lowest score, if sum of two columns constitute 100% then, they will have second lowest, if sum of three columns form 100%, then they will have third largest lowest score, similarly of the sum of column B to Column G form 100%, then they will have maximum score.