Getting Value Based On Range Number

[muhammad susanto]

hi guys...

i need formula to figure out this problem
data range given as parameter

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>

data range given
score range	value
5-9	76
10-14	77
15-19	78
score entry	expected result (as value target)
5	76
7	76
13	77
15	78
16	etc..

<tbody>
</tbody>

any help. much appreciated..

m.susanto

 

[AliGW]

Like this in B8 copied down:

=LOOKUP(A8,{5,10,15},{76,77,78})

 

[muhammad susanto]

thanks AliGW, perfect formula that's i want

 

[AliGW]

You're welcome!

 

[muhammad susanto]

hi..

how to make more shorter formula not too much typing..

score range	value
9-10,6	76
10,7-12,2	77
12,3-13,8	78
13,9-15,4	79
15,5-17	80
17,1-18,6	81
18,7-20,2	82
20,3-21,8	83
21,9-23,4	84
23,5-25	85
25,1-26,6	86
26,7-28,2	87
28,3-29,8	88
29,9-31,4	89
31,5-33	90
33,1-34,6	91
34,7-36,2	92
36,3-37,8	93
37,9-39,4	94
39,5-41	95
41,1-42,6	96
42,7-44,2	97
44,3-45,8	98
45,9-47,4	99
47,5-49	100

<tbody>
</tbody>

 

[AliGW]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	score range
2	9	76		12	77
3	10.7	77		26	86
4	12.3	78		30	89
5	13.9	79
6	15.5	80
7	17.1	81
8	18.7	82
9	20.3	83
10	21.9	84
11	23.5	85
12	25.1	86
13	26.7	87
14	28.3	88
15	29.9	89
16	31.5	90
17	33.1	91
18	34.7	92
19	36.3	93
20	37.9	94
21	39.5	95
22	41.1	96
23	42.7	97
24	44.3	98
25	45.9	99
26	47.5	100
Sheet5

Cell Formulas
Range	Formula
E2	=VLOOKUP(D2,$A$2:$B$26,2,1)

 

[muhammad susanto]

where come from result in D2 down...?

 

[AliGW]

They are not results. They are sample scores. The results are in E2 to E4. The formula goes into E2 and you copy it down. You should be able to adapt what I have shown you to fit your required layout.

 

[Tetra201]

hi..

how to make more shorter formula not too much typing.

If you put your Score entry in cell D2, the following formula will give you the corresponding Value:

=76+ROUNDDOWN((D2-9.1)/1.599,0)