Approximate Value Formula

K

keejay20

New Member
Joined
Jan 26, 2015
Messages
5
Hi ALL,

I hope someone can help me with this problem. What im trying to do it to look up the approximate value of Tab 1 column 1 from Tab 2 column 2. Below are the formula that i have used so far:

=MATCH(MIN(ABS(Ratings!A:B-E9)),ABS(Ratings!A:B-E9),0)

=VLOOKUP(E10,Ratings!C:D,2,TRUE)

Both formula arent working.

See tables below as in example

Tab 1:

Actual NPS</SPAN>
SC Value</SPAN>
33.33</SPAN>
50.48</SPAN>
17.01</SPAN>
0.00</SPAN>

<TBODY>
</TBODY>


Tab 2:
57.00
5
56.945
4.99
56.89
4.98
56.835
4.97
56.78
4.96
56.725
4.95
56.67
4.94
56.615
4.93

<TBODY>
</TBODY>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
so you are looking up 33.33 in a range of numbers from 56.615 to 57

are you trying to use y= mx +c approach eg find the equation that gives y 56.615 for x value of 4.93 etc

it could be y=mx+c or y = nx^2 +mx +c or far more complicated
 
Upvote 0
oldbrewer said:
so you are looking up 33.33 in a range of numbers from 56.615 to 57

are you trying to use y= mx +c approach eg find the equation that gives y 56.615 for x value of 4.93 etc

it could be y=mx+c or y = nx^2 +mx +c or far more complicated
Click to expand...



Hi Oldbrewer,

The table is just an example but it goes down further.
 
Upvote 0
Hi Andrew Poulsom,

Thank you for the suggesttion :) i tried arranging the first column in an ascending manner but i got a value error.
 
Upvote 0
You need to avoid using entire columns. Here's an example:


Excel 2010
CDEF
156.6154.93
256.674.94
356.7254.95
456.784.96
556.8354.97
656.894.98
756.9454.99
8575
9
1056.84.96
Ratings
Cell Formulas
RangeFormula
F10=VLOOKUP(E10,Ratings!C1:D8,2,TRUE)
 
Upvote 0
Hi Andrew,

I did the sample as stated above and i got a value error again. See full table below:

2-5
2.055-4.99
2.11-4.98
2.165-4.97
2.22-4.96
2.275-4.95
2.33-4.94
2.385-4.93
2.44-4.92
2.495-4.91
2.55-4.9
2.605-4.89
2.66-4.88
2.715-4.87
2.77-4.86
2.825-4.85
2.88-4.84
2.935-4.83
2.99-4.82
3.045-4.81
3.1-4.8
3.155-4.79
3.21-4.78
3.265-4.77
3.32-4.76
3.375-4.75
3.43-4.74
3.485-4.73
3.54-4.72
3.595-4.71
3.65-4.7
3.705-4.69
3.76-4.68
3.815-4.67
3.87-4.66
3.925-4.65
3.98-4.64
4.035-4.63
4.09-4.62
4.145-4.61
4.2-4.6
4.255-4.59
4.31-4.58
4.365-4.57
4.42-4.56
4.475-4.55
4.53-4.54
4.585-4.53
4.64-4.52
4.695-4.51
4.75-4.5
4.805-4.49
4.86-4.48
4.915-4.47
4.97-4.46
5.025-4.45
5.08-4.44
5.135-4.43
5.19-4.42
5.245-4.41
5.3-4.4
5.355-4.39
5.41-4.38
5.465-4.37
5.52-4.36
5.575-4.35
5.63-4.34
5.685-4.33
5.74-4.32
5.795-4.31
5.85-4.3
5.905-4.29
5.96-4.28
6.015-4.27
6.07-4.26
6.125-4.25
6.18-4.24
6.235-4.23
6.29-4.22
6.345-4.21
6.4-4.2
6.455-4.19
6.51-4.18
6.565-4.17
6.62-4.16
6.675-4.15
6.73-4.14
6.785-4.13
6.84-4.12
6.895-4.11
6.95-4.1
7.005-4.09
7.06-4.08
7.115-4.07
7.17-4.06
7.225-4.05
7.28-4.04
7.335-4.03
7.39-4.02
7.445-4.01
7.5-4
7.555-3.99
7.61-3.98
7.665-3.97
7.72-3.96
7.775-3.95
7.83-3.94
7.885-3.93
7.94-3.92
7.995-3.91
8.05-3.9
8.105-3.89
8.16-3.88
8.215-3.87
8.27-3.86
8.325-3.85
8.38-3.84
8.435-3.83
8.49-3.82
8.545-3.81
8.6-3.8
8.655-3.79
8.71-3.78
8.765-3.77
8.82-3.76
8.875-3.75
8.93-3.74
8.985-3.73
9.04-3.72
9.095-3.71
9.15-3.7
9.205-3.69
9.26-3.68
9.315-3.67
9.37-3.66
9.425-3.65
9.48-3.64
9.535-3.63
9.59-3.62
9.645-3.61
9.7-3.6
9.755-3.59
9.81-3.58
9.865-3.57
9.92-3.56
9.975-3.55
10.03-3.54
10.085-3.53
10.14-3.52
10.195-3.51
10.25-3.5
10.305-3.49
10.36-3.48
10.415-3.47
10.47-3.46
10.525-3.45
10.58-3.44
10.635-3.43
10.69-3.42
10.745-3.41
10.8-3.4
10.855-3.39
10.91-3.38
10.965-3.37
11.02-3.36
11.075-3.35
11.13-3.34
11.185-3.33
11.24-3.32
11.295-3.31
11.35-3.3
11.405-3.29
11.46-3.28
11.515-3.27
11.57-3.26
11.625-3.25
11.68-3.24
11.735-3.23
11.79-3.22
11.845-3.21
11.9-3.2
11.955-3.19
12.01-3.18
12.065-3.17
12.12-3.16
12.175-3.15
12.23-3.14
12.285-3.13
12.34-3.12
12.395-3.11
12.45-3.1
12.505-3.09
12.56-3.08
12.615-3.07
12.67-3.06
12.725-3.05
12.78-3.04
12.835-3.03
12.89-3.02
12.945-3.01
13-3
13.055-2.99
13.11-2.98
13.165-2.97
13.22-2.96
13.275-2.95
13.33-2.94
13.385-2.93
13.44-2.92
13.495-2.91
13.55-2.9
13.605-2.89
13.66-2.88
13.715-2.87
13.77-2.86
13.825-2.85
13.88-2.84
13.935-2.83
13.99-2.82
14.045-2.81
14.1-2.8
14.155-2.79
14.21-2.78
14.265-2.77
14.32-2.76
14.375-2.75
14.43-2.74
14.485-2.73
14.54-2.72
14.595-2.71
14.65-2.7
14.705-2.69
14.76-2.68
14.815-2.67
14.87-2.66
14.925-2.65
14.98-2.64
15.035-2.63
15.09-2.62
15.145-2.61
15.2-2.6
15.255-2.59
15.31-2.58
15.365-2.57
15.42-2.56
15.475-2.55
15.53-2.54
15.585-2.53
15.64-2.52
15.695-2.51
15.75-2.5
15.805-2.49
15.86-2.48
15.915-2.47
15.97-2.46
16.025-2.45
16.08-2.44
16.135-2.43
16.19-2.42
16.245-2.41
16.3-2.4
16.355-2.39
16.41-2.38
16.465-2.37
16.52-2.36
16.575-2.35
16.63-2.34
16.685-2.33
16.74-2.32
16.795-2.31
16.85-2.3
16.905-2.29
16.96-2.28
17.015-2.27
17.07-2.26
17.125-2.25
17.18-2.24
17.235-2.23
17.29-2.22
17.345-2.21
17.4-2.2
17.455-2.19
17.51-2.18
17.565-2.17
17.62-2.16
17.675-2.15
17.73-2.14
17.785-2.13
17.84-2.12
17.895-2.11
17.95-2.1
18.005-2.09
18.06-2.08
18.115-2.07
18.17-2.06
18.225-2.05
18.28-2.04
18.335-2.03
18.39-2.02
18.445-2.01
18.5-2
18.555-1.99
18.61-1.98
18.665-1.97
18.72-1.96
18.775-1.95
18.83-1.94
18.885-1.93
18.94-1.92
18.995-1.91
19.05-1.9
19.105-1.89
19.16-1.88
19.215-1.87
19.27-1.86
19.325-1.85
19.38-1.84
19.435-1.83
19.49-1.82
19.545-1.81
19.6-1.8
19.655-1.79
19.71-1.78
19.765-1.77
19.82-1.76
19.875-1.75
19.93-1.74
19.985-1.73
20.04-1.72
20.095-1.71
20.15-1.7
20.205-1.69
20.26-1.68
20.315-1.67
20.37-1.66
20.425-1.65
20.48-1.64
20.535-1.63
20.59-1.62
20.645-1.61
20.7-1.6
20.755-1.59
20.81-1.58
20.865-1.57
20.92-1.56
20.975-1.55
21.03-1.54
21.085-1.53
21.14-1.52
21.195-1.51
21.25-1.5
21.305-1.49
21.36-1.48
21.415-1.47
21.47-1.46
21.525-1.45
21.58-1.44
21.635-1.43
21.69-1.42
21.745-1.41
21.8-1.4
21.855-1.39
21.91-1.38
21.965-1.37
22.02-1.36
22.075-1.35
22.13-1.34
22.185-1.33
22.24-1.32
22.295-1.31
22.35-1.3
22.405-1.29
22.46-1.28
22.515-1.27
22.57-1.26
22.625-1.25
22.68-1.24
22.735-1.23
22.79-1.22
22.845-1.21
22.9-1.2
22.955-1.19
23.01-1.18
23.065-1.17
23.12-1.16
23.175-1.15
23.23-1.14
23.285-1.13
23.34-1.12
23.395-1.11
23.45-1.1
23.505-1.09
23.56-1.08
23.615-1.07
23.67-1.06
23.725-1.05
23.78-1.04
23.835-1.03
23.89-1.02
23.945-1.01
24-1
24.055-0.99
24.11-0.98
24.165-0.97
24.22-0.96
24.275-0.95
24.33-0.94
24.385-0.93
24.44-0.92
24.495-0.91
24.55-0.9
24.605-0.89
24.66-0.88
24.715-0.87
24.77-0.86
24.825-0.85
24.88-0.84
24.935-0.83
24.99-0.82
25.045-0.81
25.1-0.8
25.155-0.79
25.21-0.78
25.265-0.77
25.32-0.76
25.375-0.75
25.43-0.74
25.485-0.73
25.54-0.72
25.595-0.71
25.65-0.7
25.705-0.69
25.76-0.68
25.815-0.67
25.87-0.66
25.925-0.65
25.98-0.64
26.035-0.63
26.09-0.62
26.145-0.61
26.2-0.6
26.255-0.59
26.31-0.58
26.365-0.57
26.42-0.56
26.475-0.55
26.53-0.54
26.585-0.53
26.64-0.52
26.695-0.51
26.75-0.5
26.805-0.49
26.86-0.48
26.915-0.47
26.97-0.46
27.025-0.45
27.08-0.44
27.135-0.43
27.19-0.42
27.245-0.41
27.3-0.4
27.355-0.39
27.41-0.38
27.465-0.37
27.52-0.36
27.575-0.35
27.63-0.34
27.685-0.33
27.74-0.32
27.795-0.31
27.85-0.3
27.905-0.29
27.96-0.28
28.015-0.27
28.07-0.26
28.125-0.25
28.18-0.24
28.235-0.23
28.29-0.22
28.345-0.21
28.4-0.2
28.455-0.19
28.51-0.18
28.565-0.17
28.62-0.16
28.675-0.15
28.73-0.14
28.785-0.13
28.84-0.12
28.895-0.11
28.95-0.1
29.005-0.09
29.06-0.08
29.115-0.07
29.17-0.06
29.225-0.05
29.28-0.04
29.335-0.03
29.39-0.02
29.445-0.01
29.51.1E-13
29.5550.01
29.610.02
29.6650.03
29.720.04
29.7750.05
29.830.06
29.8850.07
29.940.08
29.9950.09
30.050.1
30.1050.11
30.160.12
30.2150.13
30.270.14
30.3250.15
30.380.16
30.4350.17
30.490.18
30.5450.19
30.60.2
30.6550.21
30.710.22
30.7650.23
30.820.24
30.8750.25
30.930.26
30.9850.27
31.040.28
31.0950.29
31.150.3
31.2050.31
31.260.32
31.3150.33
31.370.34
31.4250.35
31.480.36
31.5350.37
31.590.38
31.6450.39
31.70.4
31.7550.41
31.810.42
31.8650.43
31.920.44
31.9750.45
32.030.46
32.0850.47
32.140.48
32.1950.49
32.250.5
32.3050.51
32.360.52
32.4150.53
32.470.54
32.5250.55
32.580.56
32.6350.57
32.690.58
32.7450.59
32.80.6
32.8550.61
32.910.62
32.9650.63
33.020.64
33.0750.65
33.130.66
33.1850.67
33.240.68
33.2950.69
33.350.7
33.4050.71
33.460.72
33.5150.73
33.570.74
33.6250.75
33.680.76
33.7350.77
33.790.78
33.8450.79
33.90.8
33.9550.81
34.010.82
34.0650.83
34.120.84
34.1750.85
34.230.86
34.2850.87
34.340.88
34.3950.89
34.450.9
34.5050.91
34.560.92
34.6150.93
34.670.94
34.7250.95
34.780.96
34.8350.97
34.890.98
34.9450.99
351
35.0551.01
35.111.02
35.1651.03
35.221.04
35.2751.05
35.331.06
35.3851.07
35.441.08
35.4951.09
35.551.1
35.6051.11
35.661.12
35.7151.13
35.771.14
35.8251.15
35.881.16
35.9351.17
35.991.18
36.0451.19
36.11.2
36.1551.21
36.211.22
36.2651.23
36.321.24
36.3751.25
36.431.26
36.4851.27
36.541.28
36.5951.29
36.651.3
36.7051.31
36.761.32
36.8151.33
36.871.34
36.9251.35
36.981.36
37.0351.37
37.091.38
37.1451.39
37.21.4
37.2551.41
37.311.42
37.3651.43
37.421.44
37.4751.45
37.531.46
37.5851.47
37.641.48
37.6951.49
37.751.5
37.8051.51
37.861.52
37.9151.53
37.971.54
38.0251.55
38.081.56
38.1351.57
38.191.58
38.2451.59
38.31.6
38.3551.61
38.411.62
38.4651.63
38.521.64
38.5751.65
38.631.66
38.6851.67
38.741.68
38.7951.69
38.851.7
38.9051.71
38.961.72
39.0151.73
39.071.74
39.1251.75
39.181.76
39.2351.77
39.291.78
39.3451.79
39.41.8
39.4551.81
39.511.82
39.5651.83
39.621.84
39.6751.85
39.731.86
39.7851.87
39.841.88
39.8951.89
39.951.9
40.0051.91
40.061.92
40.1151.93
40.171.94
40.2251.95
40.281.96
40.3351.97
40.391.98
40.4451.99
40.52
40.5552.01
40.612.02
40.6652.03
40.722.04
40.7752.05
40.832.06
40.8852.07
40.942.08
40.9952.09
41.052.1
41.1052.11
41.162.12
41.2152.13
41.272.14
41.3252.15
41.382.16
41.4352.17
41.492.18
41.5452.19
41.62.2
41.6552.21
41.712.22
41.7652.23
41.822.24
41.8752.25
41.932.26
41.9852.27
42.042.28
42.0952.29
42.152.3
42.2052.31
42.262.32
42.3152.33
42.372.34
42.4252.35
42.482.36
42.5352.37
42.592.38
42.6452.39
42.72.4
42.7552.41
42.812.42
42.8652.43
42.922.44
42.9752.45
43.032.46
43.0852.47
43.142.48
43.1952.49
43.252.5
43.3052.51
43.362.52
43.4152.53
43.472.54
43.5252.55
43.582.56
43.6352.57
43.692.58
43.7452.59
43.82.6
43.8552.61
43.912.62
43.9652.63
44.022.64
44.0752.65
44.132.66
44.1852.67
44.242.68
44.2952.69
44.352.7
44.4052.71
44.462.72
44.5152.73
44.572.74
44.6252.75
44.682.76
44.7352.77
44.792.78
44.8452.79
44.92.8
44.9552.81
45.012.82
45.0652.83
45.122.84
45.1752.85
45.232.86
45.2852.87
45.342.88
45.3952.89
45.452.9
45.5052.91
45.562.92
45.6152.93
45.672.94
45.7252.95
45.782.96
45.8352.97
45.892.98
45.9452.99
463
46.0553.01
46.113.02
46.1653.03
46.223.04
46.2753.05
46.333.06
46.3853.07
46.443.08
46.4953.09
46.553.1
46.6053.11
46.663.12
46.7153.13
46.773.14
46.8253.15
46.883.16
46.9353.17
46.993.18
47.0453.19
47.13.2
47.1553.21
47.213.22
47.2653.23
47.323.24
47.3753.25
47.433.26
47.4853.27
47.543.28
47.5953.29
47.653.3
47.7053.31
47.763.32
47.8153.33
47.873.34
47.9253.35
47.983.36
48.0353.37
48.093.38
48.1453.39
48.23.4
48.2553.41
48.313.42
48.3653.43
48.423.44
48.4753.45
48.533.46
48.5853.47
48.643.48
48.6953.49
48.753.5
48.8053.51
48.863.52
48.9153.53
48.973.54
49.0253.55
49.083.56
49.1353.57
49.193.58
49.2453.59
49.33.6
49.3553.61
49.413.62
49.4653.63
49.523.64
49.5753.65
49.633.66
49.6853.67
49.743.68
49.7953.69
49.853.7
49.9053.71
49.963.72
50.0153.73
50.073.74
50.1253.75
50.183.76
50.2353.77
50.293.78
50.3453.79
50.43.8
50.4553.81
50.513.82
50.5653.83
50.623.84
50.6753.85
50.733.86
50.7853.87
50.843.88
50.8953.89
50.953.9
51.0053.91
51.063.92
51.1153.93
51.173.94
51.2253.95
51.283.96
51.3353.97
51.393.98
51.4453.99
51.54
51.5554.01
51.614.02
51.6654.03
51.724.04
51.7754.05
51.834.06
51.8854.07
51.944.08
51.9954.09
52.054.1
52.1054.11
52.164.12
52.2154.13
52.274.14
52.3254.15
52.384.16
52.4354.17
52.494.18
52.5454.19
52.64.2
52.6554.21
52.714.22
52.7654.23
52.824.24
52.8754.25
52.934.26
52.9854.27
53.044.28
53.0954.29
53.154.3
53.2054.31
53.264.32
53.3154.33
53.374.34
53.4254.35
53.484.36
53.5354.37
53.594.38
53.6454.39
53.74.4
53.7554.41
53.814.42
53.8654.43
53.924.44
53.9754.45
54.034.46
54.0854.47
54.144.48
54.1954.49
54.254.5
54.3054.51
54.364.52
54.4154.53
54.474.54
54.5254.55
54.584.56
54.6354.57
54.694.58
54.7454.59
54.84.6
54.8554.61
54.914.62
54.9654.63
55.024.64
55.0754.65
55.134.66
55.1854.67
55.244.68
55.2954.69
55.354.7
55.4054.71
55.464.72
55.5154.73
55.574.74
55.6254.75
55.684.76
55.7354.77
55.794.78
55.8454.79
55.94.8
55.9554.81
56.014.82
56.0654.83
56.124.84
56.1754.85
56.234.86
56.2854.87
56.344.88
56.3954.89
56.454.9
56.5054.91
56.564.92
56.6154.93
56.674.94
56.7254.95
56.784.96
56.8354.97
56.894.98
56.9454.99
575


Im looking for the approximate valie of 33.33 in the second column
I really appreciate your responces.

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>
 
Upvote 0
first column increases by .055 second by .01
so use lookup to find 33.295 then see how far 33.33 is in the range 33.295 to 33.35
and then go the same distance in .69 to .70
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,657
Messages
6,120,776
Members
448,991
Latest member
Hanakoro

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