Formula required for different criterias to obtain Marks

[rush2rajen]

Hi Excel Gurus,

I had attached a excel file.
Sheet 1 contains Criteria on which points will be given and another sheet contains the tgt vs achievement data.

I had feed the data manually but unable to arrive at a formula which will give the desired result on different parameters.

Kindly help...

Thanks
Raj

 

[Chihiro]

Something like attached?

Created named ranges for Criteria and Weight
Criteria
lstKRA1
lstKRA2

Weight
wKRA1
wKRA2

For KRA1 categories.
In B7: =INDEX(wKRA1,MATCH(LARGE(IF(lstKRA1<=B5,lstKRA1,""),1),lstKRA1,0))*50
Enter as array (CSE) and drag across to I7

For KRA2 category just swap KRA1 with KRA2 in the formula. and change *50 to *40

 

[rush2rajen]

Hi Chihiro,

Can it be more simple rather than CSE..

Thanks
Raj

 

[Chihiro]

Sure, you just have to hard code Criteria & Weight in nested IF statement.

For KRA1:
=IF(B5>=1,1,IF(B5>=0.9,0.75,IF(B5>=0.8,0.6,IF(B5>=0.7,0.5,0))))*50

For KRA2
=IF(J5>=1,1,IF(J5>=0.9,0.75,0))*40

Another method: Create list like I did in original, but in increments of 0.1 from 1~0.

For KRA1
=IFERROR(INDEX(wKRA1,MATCH(ROUNDDOWN(B5,1),lstKRA1,0)),1)

I tend to use lists for these type of formulas, as it's easier to maintain lists rather than adjusting formulas, if Weight distribution changes.

 

[rush2rajen]

Thanks Chihiro