Help on putting a number into a range

[tana]

Hi,
Can someone help me with this? I am trying to put a number into corresponding range. My range table is like this:

Bonus Rating Template - Master Dummy Test a.xlsx
	J	K
2	0	0-84
3	85	85-94
4	95	95-104
5	105	105-114
6	115	115-124
7	125	125-134
8	135	135+
Input

When I put each number into the range, it seems to have problems starting at 115.

Bonus Rating Template - Master Dummy Test a.xlsx
	DB	DC
2	20	0-84
3	20	0-84
4	20	0-84
5	20	0-84
6	20	0-84
7	20	0-84
8	20	0-84
9	40	0-84
10	40	0-84
11	40	0-84
12	40	0-84
13	40	0-84
14	60	0-84
15	60	0-84
16	60	0-84
17	60	0-84
18	60	0-84
19	60	0-84
20	60	0-84
21	60	0-84
22	60	0-84
23	60	0-84
24	60	0-84
25	60	0-84
26	60	0-84
27	60	0-84
28	60	0-84
29	60	0-84
30	75	0-84
31	75	0-84
32	80	0-84
33	80	0-84
34	80	0-84
35	80	0-84
36	80	0-84
37	80	0-84
38	80	0-84
39	80	0-84
40	80	0-84
41	80	0-84
42	85	85-94
43	85	85-94
44	85	85-94
45	85	85-94
46	85	85-94
47	85	85-94
48	85	85-94
49	85	85-94
50	85	85-94
51	85	85-94
52	85	85-94
53	95	95-104
54	95	95-104
55	95	95-104
56	95	95-104
57	95	95-104
58	95	95-104
59	95	95-104
60	95	95-104
61	95	95-104
62	95	95-104
63	95	95-104
64	95	95-104
65	95	95-104
66	95	95-104
67	95	95-104
68	95	95-104
69	95	95-104
70	95	95-104
71	95	95-104
72	95	95-104
73	95	95-104
74	95	95-104
75	95	95-104
76	95	95-104
77	95	95-104
78	95	95-104
79	95	95-104
80	100	95-104
81	100	95-104
82	100	95-104
83	100	95-104
84	100	95-104
85	100	95-104
86	100	95-104
87	100	95-104
88	100	95-104
89	100	95-104
90	100	95-104
91	100	95-104
92	100	95-104
93	100	95-104
94	100	95-104
95	100	95-104
96	100	95-104
97	100	95-104
98	100	95-104
99	100	95-104
100	100	95-104
101	100	95-104
102	100	95-104
103	100	95-104
104	100	95-104
105	100	95-104
106	100	95-104
107	100	95-104
108	100	95-104
109	100	95-104
110	100	95-104
111	100	95-104
112	100	95-104
113	100	95-104
114	100	95-104
115	100	95-104
116	100	95-104
117	100	95-104
118	100	95-104
119	100	95-104
120	100	95-104
121	100	95-104
122	100	95-104
123	105	105-114
124	105	105-114
125	105	105-114
126	105	105-114
127	105	105-114
128	105	105-114
129	105	105-114
130	105	105-114
131	110	105-114
132	110	105-114
133	110	105-114
134	110	105-114
135	110	105-114
136	110	105-114
137	110	105-114
138	110	105-114
139	110	105-114
140	110	105-114
141	110	105-114
142	110	105-114
143	110	105-114
144	110	105-114
145	110	105-114
146	110	105-114
147	110	105-114
148	110	105-114
149	110	105-114
150	110	105-114
151	110	105-114
152	110	105-114
153	110	105-114
154	110	105-114
155	110	105-114
156	110	105-114
157	110	105-114
158	110	105-114
159	110	105-114
160	110	105-114
161	110	105-114
162	110	105-114
163	115	105-114
164	115	105-114
165	115	105-114
166	115	105-114
167	115	105-114
168	115	105-114
169	115	105-114
170	115	105-114
171	115	105-114
172	115	105-114
173	115	105-114
174	115	105-114
175	115	105-114
176	115	105-114
177	115	105-114
178	115	105-114
179	115	105-114
180	115	105-114
181	115	105-114
182	115	105-114
183	115	105-114
184	115	105-114
185	115	105-114
186	115	105-114
187	115	105-114
188	115	105-114
189	115	105-114
190	115	105-114
191	115	105-114
192	115	105-114
193	115	105-114
194	115	105-114
195	115	105-114
196	115	105-114
197	115	105-114
198	115	105-114
199	115	105-114
200	115	105-114
201	115	105-114
202	115	105-114
203	115	105-114
204	115	105-114
205	115	105-114
206	115	105-114
207	115	105-114
208	120	115-124
209	120	115-124
210	120	115-124
211	120	115-124
212	120	115-124
213	120	115-124
214	120	115-124
215	120	115-124
216	120	115-124
217	120	115-124
218	120	115-124
219	120	115-124
220	120	115-124
221	120	115-124
222	120	115-124
223	120	115-124
224	120	115-124
225	120	115-124
226	120	115-124
227	120	115-124
228	120	115-124
229	120	115-124
230	120	115-124
231	120	115-124
232	120	115-124
233	120	115-124
234	120	115-124
235	120	115-124
236	120	115-124
237	120	115-124
238	120	115-124
239	120	115-124
240	120	115-124
241	120	115-124
242	120	115-124
243	120	115-124
244	120	115-124
245	130	125-134
246	130	125-134
247	135	135+
248	145	135+
249	145	135+
250	145	135+
251	145	135+
252	145	135+
253	145	135+
254	145	135+
255	145	135+
256	145	135+
257	145	135+
258	145	135+
259	145	135+
260	145	135+
261	145	135+
262	145	135+
263	145	135+
264	145	135+
265	145	135+
266	145	135+
267	145	135+
268	145	135+
269	145	135+
270	145	135+
271	145	135+
272	145	135+
273	145	135+
274	145	135+
SortData

Cell Formulas
Range	Formula
DB2:DB274	DB2	=DA2#*100
DC2:DC274	DC2	=XLOOKUP(DB2#,Input!$J$2:$J$8,Input!$K$2:$K$8,,-1)
Dynamic array formulas.

I expected 115 is in the range of "115-124" rather than "105-114", I have tried to use vlookup as well, but same issue. Thanks!

 

[kevin9999]

What do you get when you format the 115 in DA163 to 2 decimal points?

 

[tana]

Last thing I can suggest is to check if the a 115 in DA = Input!J6. Does it return TRUE or FALSE?

It is true.

Bonus Rating Template - Master Dummy Test.xlsx
	BU	BV	BW
52	115	105-114	TRUE
53	115	105-114	TRUE
54	115	105-114	TRUE
55	115	105-114	TRUE
56	115	105-114	TRUE
57	115	105-114	TRUE
58	115	105-114	TRUE
59	115	105-114	TRUE
60	115	105-114	TRUE
61	115	105-114	TRUE
62	115	105-114	TRUE
63	115	105-114	TRUE
64	115	105-114	TRUE
65	115	105-114	TRUE
66	115	105-114	TRUE
67	115	105-114	TRUE
68	115	105-114	TRUE
69	115	105-114	TRUE
70	115	105-114	TRUE
71	115	105-114	TRUE
72	115	105-114	TRUE
SortData

Cell Formulas
Range	Formula
BW52:BW72	BW52	=BU52=Input!$J$6

Bonus Rating Template - Master Dummy Test.xlsx
	BT	BU	BV
2	0.2	20	0-84
3	0.4	40	0-84
4	0.6	60	0-84
5	0.6	60	0-84
6	0.6	60	0-84
7	0.6	60	0-84
8	0.6	60	0-84
9	0.6	60	0-84
10	0.75	75	0-84
11	0.75	75	0-84
12	0.8	80	0-84
13	0.8	80	0-84
14	0.8	80	0-84
15	0.85	85	85-94
16	0.85	85	85-94
17	0.85	85	85-94
18	0.85	85	85-94
19	0.95	95	95-104
20	0.95	95	95-104
21	0.95	95	95-104
22	1	100	95-104
23	1	100	95-104
24	1	100	95-104
25	1	100	95-104
26	1	100	95-104
27	1	100	95-104
28	1	100	95-104
29	1	100	95-104
30	1	100	95-104
31	1	100	95-104
32	1	100	95-104
33	1	100	95-104
34	1	100	95-104
35	1	100	95-104
36	1	100	95-104
37	1	100	95-104
38	1	100	95-104
39	1	100	95-104
40	1.05	105	105-114
41	1.05	105	105-114
42	1.05	105	105-114
43	1.1	110	105-114
44	1.1	110	105-114
45	1.1	110	105-114
46	1.1	110	105-114
47	1.1	110	105-114
48	1.1	110	105-114
49	1.1	110	105-114
50	1.1	110	105-114
51	1.1	110	105-114
52	1.15	115	105-114
53	1.15	115	105-114
54	1.15	115	105-114
55	1.15	115	105-114
56	1.15	115	105-114
57	1.15	115	105-114
58	1.15	115	105-114
59	1.15	115	105-114
60	1.15	115	105-114
61	1.15	115	105-114
62	1.15	115	105-114
63	1.15	115	105-114
64	1.15	115	105-114
65	1.15	115	105-114
66	1.15	115	105-114
67	1.15	115	105-114
68	1.15	115	105-114
69	1.15	115	105-114
70	1.15	115	105-114
71	1.15	115	105-114
72	1.15	115	105-114
73	1.2	120	115-124
74	1.2	120	115-124
75	1.2	120	115-124
76	1.2	120	115-124
77	1.2	120	115-124
78	1.2	120	115-124
79	1.2	120	115-124
80	1.45	145	135+
81	1.45	145	135+
82	1.45	145	135+
83	1.45	145	135+
84	1.45	145	135+
85	1.45	145	135+
86	1.45	145	135+
87	1.45	145	135+
88	1.45	145	135+
89	1.45	145	135+
90	1.2	120	115-124
91	1.45	145	135+
SortData

Cell Formulas
Range	Formula
BT2:BT91	BT2	=IF(AND('Rpt 1 H'!B4="",'Rpt 1 H'!C4="",'Rpt 1 H'!D4="",'Rpt 1 H'!E4="",'Rpt 1 H'!F4=""), I2#, IF(AND('Rpt 1 H'!B4<>"",'Rpt 1 H'!C4="",'Rpt 1 H'!D4="",'Rpt 1 H'!E4="",'Rpt 1 H'!F4=""), DROP(CHOOSECOLS(M2#,8),1), IF(AND('Rpt 1 H'!B4<>"",'Rpt 1 H'!C4<>"",'Rpt 1 H'!D4="",'Rpt 1 H'!E4="",'Rpt 1 H'!F4=""), DROP(CHOOSECOLS(Z2#,8),1), IF(AND('Rpt 1 H'!B4<>"",'Rpt 1 H'!C4<>"",'Rpt 1 H'!D4<>"",'Rpt 1 H'!E4="",'Rpt 1 H'!F4=""), DROP(CHOOSECOLS(AL2#,8),1), IF(AND('Rpt 1 H'!B4<>"",'Rpt 1 H'!C4<>"",'Rpt 1 H'!D4<>"",'Rpt 1 H'!E4<>"",'Rpt 1 H'!F4=""), DROP(CHOOSECOLS(AX2#,8),1), IF(AND('Rpt 1 H'!B4<>"",'Rpt 1 H'!C4<>"",'Rpt 1 H'!D4<>"",'Rpt 1 H'!E4<>"",'Rpt 1 H'!F4<>""), DROP(CHOOSECOLS(BJ2#,8),1), "Not Found"))))))
BU2:BU91	BU2	=BT2#*100
BV2:BV91	BV2	=VLOOKUP(BU2#,Input!J2:K8,2)
Dynamic array formulas.

Strange... what is going on here? I am super curious the truth. Sorry due to company privacy, I can't share the file. Wish you can help me to find the truth.

Appreciate for all your help so far!

 

[Peter_SSs]

I believe that it is a floating point issue related to how Excel is feeding the DB2# spilled array into the DC2 formula.
I was already checking the same cell that @kevin999 is asking about and even when I expanded it to show 12 decimal places it showed 115.000000000000
However, trying this in DC2 returned the correct result

=VLOOKUP(ROUND(DB2#,10),Input!J2:K8,2)

So I think something to do with feeding the DB array into the formula has resulted in that 115 going in as something like 114.999999999999 & hence it was returning the 105-114 range.

 

[Holger]

Hi,

I'm certainly no expert and over the last few weeks I have updated worksheets with LET functions. Now, getting the hang of that witchcraft is not that easy. However, this question from @tana reminded me of income tax brackets, which is the same concept. so what I did is:

* create an array where upper and lower achievement are in 2 columns and then added the bonus payout in.
* unfortunately I have not made that much progress with the witchcraft project, that I could create a complete LET function. However, I simple added an entry field for achievement E10 and a Vlookup in G10, that throws out the respective bracket.

I'm sure this can be further simplified and maybe even the raw data col J can and amended to show lower and upper limits.....

Again, just a suggestion....

Book2
	A	B	C	D	E	F	G
1	0	0-84
2	85	85-94
3	95	95-104
4	105	105-114
5	115	115-124
6	125	125-134
7	135	135+
8
9
10				achievement	115	bracket	115-124
11					0	0	0
12					0	85	0-84
13					85	95	85-94
14					95	105	95-104
15					105	115	105-114
16					115	125	115-124
17					125	135	125-134
Input

Cell Formulas
Range	Formula
G10	G10	=VLOOKUP(E10,E11#,3,TRUE)
E11:G17	E11	=LET( achieve,E10, bonusbracket, B1:B7, upper,A1:A7, lower,DROP(VSTACK(0,upper),-1), HSTACK(lower, upper, DROP(VSTACK(0,bonusbracket),-1)) )
Dynamic array formulas.

 

[Peter_SSs]

So I think something to do with feeding the DB array into the formula has resulted in that 115 going in as something like 114.999999999999

Actually, that may not be quite right since if I click on DC2 and invoke the 'Evaluate Formula' tool, Evaluate one step then scroll through the values they still appear as 115, so it may be more an issue of how the VLOOKUP actually processes the array of values. Either way, it appears to be a rounding/floating point issue and would need to be handled with some form of rounding as suggested earlier to assure correct results.

 

[Peter_SSs]

may be more an issue of how the VLOOKUP actually processes the array of values.

Wrong again. It has nothing to do with the fact that VLOOKUP is being fed an array of values, it is something (I don't know what) about how the value of 1.15*100 is stored and/or retrieved by Excel. Take these three individual VLOOKUPs. In each case the VLOOKUP is looking up 115 but the 115 has been calculated different ways and it is just that particular calculation of 115 that seems to be the issue. Note that the issue also arises if we use XLOOKUP or LOOKUP instead of VLOOKUP as well.

tana.xlsm
	J	K	L	M	N	O	P	Q
1						VLOOKUP	XLOOKUP	LOOKUP
2	0	0-84		1.15	115.000000000000000000000000000000	105-114	105-114	105-114
3	85	85-94		115	115.000000000000000000000000000000	115-124	115-124	115-124
4	95	95-104		23	115.000000000000000000000000000000	115-124	115-124	115-124
5	105	105-114
6	115	115-124
7	125	125-134
8	135	135+
Input

Cell Formulas
Range	Formula
N2	N2	=100*M2
O2:O4	O2	=VLOOKUP(N2,J$2:K$8,2)
P2:P4	P2	=XLOOKUP(N2,J$2:J$8,K$2:K$8,,-1)
Q2:Q4	Q2	=LOOKUP(N2,J$2:J$8,K$2:K$8)
N3	N3	=1*M3
N4	N4	=5*M4

@Holger
If you put the formula =1.15*100 in your yellow cell you will see the same result as well.

tana.xlsm
	D	E	F	G
10	achievement	115	bracket	105-114
11		0	0	0
12		0	85	0-84
13		85	95	85-94
14		95	105	95-104
15		105	115	105-114
16		115	125	115-124
17		125	135	125-134
Sheet1

Cell Formulas
Range	Formula
G10	G10	=VLOOKUP(E10,E11#,3,TRUE)
E10	E10	=1.15*100
E11:G17	E11	=LET( achieve,E10, bonusbracket, B1:B7, upper,A1:A7, lower,DROP(VSTACK(0,upper),-1), HSTACK(lower, upper, DROP(VSTACK(0,bonusbracket),-1)) )
Dynamic array formulas.

 

[Holger]

@Peter_SSs does this suggest that if the input cell is a formula the search through an array works different as to a plain number?

 

[Peter_SSs]

@Peter_SSs does this suggest that if the input cell is a formula the search through an array works different as to a plain number?

No, if you did what I suggested in your yellow cell and now get the wrong answer, try this.
Select that yellow cell with the formula, Copy it and use Paste Special (Values) to put the value back in the same yellow cell where the formula was.
The cell now contains a number, not a formula, but the result is still incorrect.

It is about how Excel, running on a machine, does not and cannot always do everything exactly as a human would do. See here for some further reading.
Also look at (& try for yourself) the 3 calculations shown in this post that all give exactly the same result if I do them manually myself, but not when Excel does them.

 

[Cubist]

Looks like a floating decimal issue.

Book1
	J	K	L	M	N	O	P	Q	R
1						VLOOKUP	XLOOKUP	LOOKUP	MOD
2	0	0-84		1.15	115	105-114	105-114	105-114	0.99999999999998600
3	85	85-94		115	115	115-124	115-124	115-124	0.00000000000000000
4	95	95-104		23	115	115-124	115-124	115-124	0.00000000000000000
5	105	105-114
6	115	115-124
7	125	125-134
8	135	135+
Input

Cell Formulas
Range	Formula
N2	N2	=100*M2
O2:O4	O2	=VLOOKUP(N2,J$2:K$8,2)
P2:P4	P2	=XLOOKUP(N2,J$2:J$8,K$2:K$8,,-1)
Q2:Q4	Q2	=LOOKUP(N2,J$2:J$8,K$2:K$8)
R2:R4	R2	=MOD(N2:N4,1)
N3	N3	=1*M3
N4	N4	=5*M4
Dynamic array formulas.

 

[Cubist]

Further investigation reveals that, it's not unique to 1.15.
It occurs for some decimals that cannot be precisely represented in binary. Another example is 1.1.When multiplying 1.15 x 100, it magnifies the error. It just happens that the floating point error for 1.15 x100 is too close to 1.

Book1
	J	K	L	M	N	O	P	Q	R
1						VLOOKUP	XLOOKUP	LOOKUP	MOD
2	0	0-84		1.15	115	105-114	105-114	105-114	0.99999999999998600
3	85	85-94		115	115	115-124	115-124	115-124	0.00000000000000000
4	95	95-104		23	115	115-124	115-124	115-124	0.00000000000000000
5	110	105-114		1.1	110	105-114	105-114	105-114	0.00000000000001421
6	115	115-124
7	125	125-134
8	135	135+
Input

Cell Formulas
Range	Formula
N2,N5	N2	=100*M2
O2:O5	O2	=VLOOKUP(N2,J$2:K$8,2)
P2:P5	P2	=XLOOKUP(N2,J$2:J$8,K$2:K$8,,-1)
Q2:Q5	Q2	=LOOKUP(N2,J$2:J$8,K$2:K$8)
R2:R5	R2	=MOD(N2:N5,1)
N3	N3	=1*M3
N4	N4	=5*M4
Dynamic array formulas.