Scoring Formula

S

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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%

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


Sanket
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Hey try these:

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

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

Format Columns B & D as percentages.
 
Last edited:
Upvote 0
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 ..



[TABLE="width: 531"]
<colgroup><col width="177" span="3" style="width:133pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 177"] =C1[/TD]
[TD="class: xl65, width: 177"]Answer in D1[/TD]
[TD="class: xl65, width: 177"]Dif in each point[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 177"]<0.21%[/TD]
[TD="class: xl66, width: 177"]5.00%[/TD]
[TD="class: xl66, width: 177"]0.25%[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.21%[/TD]
[TD="class: xl66, width: 177"]5.00%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.22%[/TD]
[TD="class: xl66, width: 177"]4.75%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.23%[/TD]
[TD="class: xl66, width: 177"]4.50%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.24%[/TD]
[TD="class: xl66, width: 177"]4.25%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.25%[/TD]
[TD="class: xl66, width: 177"]4.00%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.26%[/TD]
[TD="class: xl66, width: 177"]3.75%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.27%[/TD]
[TD="class: xl66, width: 177"]3.50%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.28%[/TD]
[TD="class: xl66, width: 177"]3.25%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.29%[/TD]
[TD="class: xl66, width: 177"]3.00%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.30%[/TD]
[TD="class: xl66, width: 177"]2.75%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.31%[/TD]
[TD="class: xl66, width: 177"]2.50%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.32%[/TD]
[TD="class: xl66, width: 177"]2.25%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.33%[/TD]
[TD="class: xl66, width: 177"]2.00%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.34%[/TD]
[TD="class: xl66, width: 177"]1.75%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.35%[/TD]
[TD="class: xl66, width: 177"]1.50%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.36%[/TD]
[TD="class: xl66, width: 177"]1.25%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.37%[/TD]
[TD="class: xl66, width: 177"]1.00%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.38%[/TD]
[TD="class: xl66, width: 177"]0.75%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.39%[/TD]
[TD="class: xl66, width: 177"]0.50%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 177"]0.40%[/TD]
[TD="class: xl66, width: 177"]0.25%[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 177"]>0.4%[/TD]
[TD="class: xl65, width: 177"]0[/TD]
[TD="class: xl65, width: 177"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi, for the reverse formula give this a go:

Code: 
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
 
Upvote 0
Hi,

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

I tried with following figures ..

[TABLE="width: 455"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Score [/TD]
[TD] Expected Result[/TD]
[TD] Actual result[/TD]
[/TR]
[TR]
[TD="align: right"]0.23%[/TD]
[TD="align: right"]4.50%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]0.24%[/TD]
[TD="align: right"]4.25%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]0.25%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]0.26%[/TD]
[TD="align: right"]3.75%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]0.27%[/TD]
[TD="align: right"]3.50%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]0.28%[/TD]
[TD="align: right"]3.25%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]0.29%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]0.30%[/TD]
[TD="align: right"]2.75%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hey, that looks like its because formula picks up 0.21 and not 0.21%,

Try this revised formula instead:

Code: 
IF(C1<=0.21%,5%,MAX(0,5%-(C1-0.21%)*25))
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top