Scoring Formula

[sanket_sk]

Hi,


I am looking for two different formulas which will calculate score in B & C column respectively, logic given in below table …
Please help me build the formula …

Formula 1. >1.25 = 0 Increase on each step 0.132% Max 10% at 2

Formula 2. <0.21% = 0 Increase on each step 0.25% Max 5% at .40%

=A1	Answer in B1	Increase in each point			=C1	Answer in D1	Increase in each point
<1.25	0.000	0.132%			<0.21%	0	0.25%
1.25	0.132%				0.21%	0.25%
1.26	0.263%				0.22%	0.50%
1.27	0.395%				0.23%	0.75%
1.28	0.526%				0.24%	1.00%
1.29	0.658%				0.25%	1.25%
1.3	0.789%				0.26%	1.50%
1.31	0.921%				0.27%	1.75%
1.32	1.053%				0.28%	2.00%
1.33	1.184%				0.29%	2.25%
1.34	1.316%				0.30%	2.50%
1.35	1.447%				0.31%	2.75%
1.36	1.579%				0.32%	3.00%
1.37	1.711%				0.33%	3.25%
1.38	1.842%				0.34%	3.50%
1.39	1.974%				0.35%	3.75%
1.4	2.105%				0.36%	4.00%
1.41	2.237%				0.37%	4.25%
1.42	2.368%				0.38%	4.50%
1.43	2.500%				0.39%	4.75%
1.44	2.632%				0.40%	5.00%
1.45	2.763%				>0.4%	5.00%
1.46	2.895%
1.47	3.026%
1.48	3.158%
1.49	3.289%
1.5	3.421%
1.51	3.553%
1.52	3.684%
1.53	3.816%
1.54	3.947%
1.55	4.079%
1.56	4.211%
1.57	4.342%
1.58	4.474%
1.59	4.605%
1.6	4.737%
1.61	4.868%
1.62	5.000%
1.63	5.132%
1.64	5.263%
1.65	5.395%
1.66	5.526%
1.67	5.658%
1.68	5.789%
1.69	5.921%
1.7	6.053%
1.71	6.184%
1.72	6.316%
1.73	6.447%
1.74	6.579%
1.75	6.711%
1.76	6.842%
1.77	6.974%
1.78	7.105%
1.79	7.237%
1.8	7.368%
1.81	7.500%
1.82	7.632%
1.83	7.763%
1.84	7.895%
1.85	8.026%
1.86	8.158%
1.87	8.289%
1.88	8.421%
1.89	8.553%
1.9	8.684%
1.91	8.816%
1.92	8.947%
1.93	9.079%
1.94	9.211%
1.95	9.342%
1.96	9.474%
1.97	9.605%
1.98	9.737%
1.99	9.868%
2.00	10.000%
>2.00	10.000%

<tbody>
</tbody>

Sanket

 

[tyija1995]

Hey try these:

B1:

IF(A1<1.25,0,MIN(10%,(A1-1.24)*0.132))

D1:

IF(C1<0.21,0,MIN(5%,(C1-0.2)*0.25))

Format Columns B & D as percentages.

 

[sanket_sk]

Thanks Tyija1995 for quick response !!!

The formula is working perfectly, only one small doubt what is we want to calculate 2nd formula in reverse ( given below logic ), please help me in understanding ..

=C1	Answer in D1	Dif in each point
<0.21%	5.00%	0.25%
0.21%	5.00%
0.22%	4.75%
0.23%	4.50%
0.24%	4.25%
0.25%	4.00%
0.26%	3.75%
0.27%	3.50%
0.28%	3.25%
0.29%	3.00%
0.30%	2.75%
0.31%	2.50%
0.32%	2.25%
0.33%	2.00%
0.34%	1.75%
0.35%	1.50%
0.36%	1.25%
0.37%	1.00%
0.38%	0.75%
0.39%	0.50%
0.40%	0.25%
>0.4%	0

<colgroup><col width="177" span="3" style="width:133pt"> </colgroup><tbody>
</tbody>

 

[tyija1995]

Hi, for the reverse formula give this a go:

IF(C1<=0.21,5%,MAX(0,5%-(C1-0.21)*0.25))

This says to give 5% if cell is less or equal to 0.21 otherwise return the max of 0 against 5% less the difference of the cell next to the boundary value multiplied by the step size of 0.25

 

[sanket_sk]

Hi,

I tried reverse calculation formula, but it is returning 5% in all scenarios ..

I tried with following figures ..

Score	Expected Result	Actual result
0.23%	4.50%	5%
0.24%	4.25%	5%
0.25%	4.00%	5%
0.26%	3.75%	5%
0.27%	3.50%	5%
0.28%	3.25%	5%
0.29%	3.00%	5%
0.30%	2.75%	5%

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[tyija1995]

Hey, that looks like its because formula picks up 0.21 and not 0.21%,

Try this revised formula instead:

IF(C1<=0.21%,5%,MAX(0,5%-(C1-0.21%)*25))