Team Scorecard using If Function

[anthoncl]

I am trying to create a scorecard for the team with weightage based on the parameters set.
E.g. if CellB12 score is >=12% it should populate 1 point, if 11%-11.9% it should give 2 point , if 10%-10.9% then 3 points and so on

Points	1​	2​	3​	4​	5​
Para 1	>=12%	11%-11.9%	10%-10.9%	9.99% - 9%	<9%
Para 2	>=9%	8.1%-8.9%	7%-7.9%	6.1%-6.9%	<6%

Once I got all the points then it should multiply by the weightage percentage.

Score	Weightage	Emp 2	Emp 3	Emp 4	Emp 5	Emp 6
0-30 delinquency bucket to be within 10 %	10%​	0.5​	4​	0.1​	0.1​	0.3​
31-60 delinquency bucket to be within 7%	15%​	0.75​	0.75​	3​	0.75​	0.75​

 

[Anthony47]

List your data, from B2 to F2, as 99%, 11.99999%, 10.99999%, 9.99999%, 8.99999%
Then, if in B12 is the score then you can get your information using the formula

=MATCH(B9,B2:F2,-1)

This is dimonstrated in Row 2 and cell C12 of the attached XL2BB minisheet

However I this it is more intuitive lista the data in increasing order; ie, in B4:F4: 0.00%, 9.00%, 10.00%, 11.00%, 12.00%
With this serie in B4:F4 and the scrore in B12 you can get your information using the formula

=COLUMNS(B4:F4)-MATCH(B12,B4:F4)+1

I used the following "Customized formatting type" to show the serie in a more meaningful way:
-for B2:F2, <#.##0,0%;-#.##0%;<#0%
-for B4:F4, >#.##0,0%;-#.##0%;>#0%
The result is shown in the attached image

To create the correct Type for your language, see

Number format codes - Microsoft Support

You can use the built-in number formats in Excel as is, or you can create your own custom number formats to change the appearance of numbers, dates, and times.

support.microsoft.com

MULTI_C21107.xlsm
	A	B	C	D	E	F	G	H
1	Points	1	2	3	4	5
2	Para 1	<99,0%	<12,0%	<11,0%	<10,0%	<9,0%
3		5	4	3	2	1
4	Para 1	>0%	>9,0%	>10,0%	>11,0%	>12,0%
5	Para 1	>=12%	11%-11.9%	10%-10.9%	9.99% - 9%	<9%
6
7
8
9
10
11
12		8,9%	5	=MATCH(B9,B2:F2,-1)
13			5	=COLUMNS(B4:F4)-MATCH(B12,B4:F4)+1
14
15
Foglio10

Cell Formulas
Range	Formula
C12	C12	=MATCH(B12,B2:F2,-1)
C13	C13	=COLUMNS(B4:F4)-MATCH(B12,B4:F4)+1