Formula to get highest value between two numbers

[aliaslamy2k]

Dear Experts,

I want to formula to display the highest number in between two numbers.

Example :
1. In Cell AQ8 display highest between 30 to 45 else blank
2. In Cell AQ9 Display highest number between 10 to 30 else blank
3. In Cell AQ10 Display highest number between 0 to 10 else blank

Book1
	AK	AL	AM	AN	AO	AP	AQ	AR
5	Licence Expiration	LIC Days Remaining
6	18-Dec-96	LIC EXPIRED
7	23-Apr-15	LIC EXPIRED			Greater Than	Less than	Result	Remarks
8	13-Apr-17	LIC EXPIRED			30	45	44	To check in column AL and get the result in AQ8
9	19-Jul-17	LIC EXPIRED			10	30	29	To check in column AL and get the result in AQ9
10	25-Jan-18	LIC EXPIRED			0	10	9	To check in column AL and get the result in AQ10
11	14-Mar-18	LIC EXPIRED
12	16-Oct-18	LIC EXPIRED
13	4-Nov-18	LIC EXPIRED
14	4-Nov-18	LIC EXPIRED
15	9-Jan-19	LIC EXPIRED
16	2-Apr-19	LIC EXPIRED
17	9-Jun-19	LIC EXPIRED
18	20-Jul-19	LIC EXPIRED
19	25-Jul-19	LIC EXPIRED
20	28-Jul-19	LIC EXPIRED
21	28-Jul-19	LIC EXPIRED
22	28-Jul-19	LIC EXPIRED
23	28-Jul-19	LIC EXPIRED
24	13-Oct-19	LIC EXPIRED
25	20-Oct-19	LIC EXPIRED
26	20-Oct-19	LIC EXPIRED
27	14-Nov-19	LIC EXPIRED
28	14-Nov-19	LIC EXPIRED
29	5-Feb-20	LIC EXPIRED
30	16-Feb-20	LIC EXPIRED
31	19-Feb-20	LIC EXPIRED
32	2-Mar-20	LIC EXPIRED
33	9-Mar-20	LIC EXPIRED
34	18-Mar-20	LIC EXPIRED
35	23-Mar-20	LIC EXPIRED
36	23-Mar-20	LIC EXPIRED
37	25-Mar-20	1
38	26-Mar-20	2
39	26-Mar-20	2
40	26-Mar-20	2
41	26-Mar-20	2
42	26-Mar-20	2
43	26-Mar-20	2
44	26-Mar-20	2
45	26-Mar-20	2
46	26-Mar-20	2
47	26-Mar-20	2
48	26-Mar-20	2
49	26-Mar-20	2
50	26-Mar-20	2
51	26-Mar-20	2
52	26-Mar-20	2
53	26-Mar-20	2
54	26-Mar-20	2
55	26-Mar-20	2
56	26-Mar-20	2
57	2-Apr-20	9
58	7-Apr-20	14
59	13-Apr-20	20
60	13-Apr-20	20
61	14-Apr-20	21
62	23-Apr-20	30
63	23-Apr-20	30
64	23-Apr-20	30
65	23-Apr-20	30
66	24-Apr-20	31
67	27-Apr-20	34
68	27-Apr-20	34
69	27-Apr-20	34
70	4-May-20	41
71	5-May-20	42
72	13-Apr-20	20
73	15-May-20	52
74	17-May-20	54
75	18-May-20	55
76	20-May-20	57
77	21-May-20	58
78	21-May-20	58
79	28-May-20	65
80	9-Jun-20	77
81	9-Jun-20	77
82	10-Jun-20	78
83	11-Jun-20	79
84	14-Jun-20	82
85	15-Jun-20	83
86	15-Jun-20	83
87	18-Jun-20	86
88	30-Jun-20	98
89	30-Jun-20	98
90	3-Jul-20	101
91	7-Jul-20	105
92	13-Jul-20	111
93	14-Jul-20	112
94	21-Jul-20	119
95	23-Jul-20	121
96	24-Jul-20	122
97	24-Jul-20	122
98	24-Jul-20	122
99	31-Jul-20	129
100	7-Aug-20	136
101	7-Aug-20	136
102	17-Aug-20	146
103	21-Aug-20	150
104	24-Aug-20	153
105	27-Aug-20	156
106	28-Aug-20	157
107	31-Aug-20	160
108	1-Sep-20	161
109	8-Sep-20	168
110	8-Sep-20	168
111	8-Sep-20	168
112	8-Sep-20	168
113	9-Sep-20	169
114	11-Sep-20	171
115	19-Sep-20	179
116	30-Sep-20	190
117	1-Oct-20	191
118	1-Oct-20	191
119	1-Oct-20	191
120	1-Oct-20	191
121	1-Oct-20	191
122	8-Oct-20	198
123	9-Oct-20	199
124	16-Oct-20	206
125	21-Oct-20	211
126	21-Oct-20	211
127	26-Oct-20	216
128	26-Oct-20	216
129	27-Oct-20	217
130	3-Nov-20	224
131	3-Nov-20	224
132	6-Nov-20	227
133	9-Nov-20	230
134	13-Nov-20	234
135	19-Nov-20	240
136	20-Nov-20	241
137	22-Nov-20	243
138	26-Nov-20	247
139	26-Nov-20	247
140	26-Nov-20	247
141	30-Nov-20	251
142	12-Dec-20	263
143	22-Dec-20	273
144	25-Dec-20	276
145	25-Dec-20	276
146	29-Dec-20	280
147	30-Dec-20	281
148	8-Jan-21	290
149	15-Jan-21	297
150	15-Jan-21	297
151	15-Jan-21	297
152	22-Jan-21	304
153	26-Jan-21	308
154	26-Jan-21	308
155	9-Feb-21	322
156	9-Feb-21	322
157	11-Feb-21	324
158	11-Feb-21	324
159	12-Feb-21	325
160	16-Feb-21	329
161	16-Feb-21	329
162	17-Feb-21	330
163	18-Feb-21	331
164	18-Feb-21	331
165	23-Feb-21	336
166	23-Feb-21	336
167	23-Feb-21	336
168	2-Mar-21	343
169	9-Mar-21	350
170	29-Dec-2020
171	Available
172	Available
173	Available
174	Available
175	Available
176	Available
177	Available
178	Available
179	Available
180	Available
181	Available
182	Available
183	Available
184	Available
185	Available
186	Available
187	Available
188	Available
189	Available
190	Available
191	Available
192	Available
193	Available
194	Available
195	Available
196	Available
197	Available
198	Available
199	Available
200	Available
201	Available
202	Available
203	Available
204	Available
205	Available
206	Available
207	Available
208	Available
209	Available
210	Available
211	Available
212	Available
213	Available
214	Available
215	Available
216	Available
217	Available
218	Available
219	Available
220	Available
221	NO
222	NO
223	NO
224	NO
225	NO
226	NO
227	NO
228	NO
229	NO
230	NO
231	NO
232	NO
233	NO
234	NO
235	NO
236	NO
Sheet1

 

[Peter_SSs]

How do get 44 and 29 as results from that sample data?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[aliaslamy2k]

How do get 44 and 29 as results from that sample data?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

Hi Peter,

I just entered a random number as 44, 29 and 9.

Thank you for your suggestion, i have updated my profile

 

[Peter_SSs]

i have updated my profile

Thanks.

In AQ8, copied down, try

=AGGREGATE(14,6,AL$6:AL$300/((AL$6:AL$300>AO8)*(AL$6:AL$300<AP8)),1)


Also, just checking: The data has the value 30 in column AL but the above formula does not return it in cell AQ9. That is because your description says "between" and your AP7 heading is also "Less Than" not "Less Than or Equal to".

 

[aliaslamy2k]

Thanks.

In AQ8, copied down, try

=AGGREGATE(14,6,AL$6:AL$300/((AL$6:AL$300>AO8)*(AL$6:AL$300<AP8)),1)


Also, just checking: The data has the value 30 in column AL but the above formula does not return it in cell AQ9. That is because your description says "between" and your AP7 heading is also "Less Than" not "Less Than or Equal to".

Hi Peter,

Thank you so much. This formula works perfectly fine. I have added IFERROR in this formula to show as blank instead of #NUM

Book1
	AO	AP	AQ
7	Greater Than	Less than	Result
8	30	45	41
9	10	30	21
10	0	10	9
Sheet1

Thank you once again

 

[Peter_SSs]

Thank you so much. This formula works perfectly fine. I have added IFERROR in this formula to show as blank instead of #NUM

Thank you once again

Good plan.
You're welcome.

 

[aliaslamy2k]

Hello Peter,

Is there any formula to get the list of vehicle numbers whiich falls under those criteria. Vehicle numbers are in column E6:E300

 

[Peter_SSs]

Could you clarify that by constructing a small set of dummy data and expected results and posting with XL2BB? (you can hide irrelevant columns)

 

[aliaslamy2k]

I am unable to copy whole table due to character limitations in XL2BB, so i have to delete few columns to reduce the size

Excel Formulas.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P
1						Greater than	Less than	Results
2						30	45		1st Reminder
3						10	30			2nd Reminder
4						0	10				Last Reminder
5	ID	Vehicle Number	LIC Days Remaining
6	1	7823				30 - 45			10 - 30			0 - 10
7	2	54705				Vehicle Number	LIC Days Remaining		Vehicle Number	LIC Days Remaining		Vehicle Number	LIC Days Remaining
8	3	34346				240767	30		264881	14		320055	1
9	4	116609				283597	30		104516	20		455101	2
10	5	570242				314852	30		305660	20		216830	2
11	6	633595				312209	30		127539	21		215797	2
12	7	15305				253943	31		718771	20		214218	2
13	8	140236				46242	34					306796	2
14	9	15257				320296	34					305114	2
15	10	49148				320295	34					556492	2
16	11	232690				168467	41					307242	2
17	12	23298				34163	42					306924	2
18	13	41204										307524	2
19	14	42990										306558	2
20	15	186324										305093	2
21	16	41205										305194	2
22	17	41207										305455	2
23	18	41208										305712	2
24	19	29555										305196	2
25	20	46963										307119	2
26	21	46962										206756	2
27	22	283779										306415	2
28	23	283780										263443	9
29	24	254273
30	25	739050
31	26	668344
32	27	594941
33	28	460470
34	29	264925
35	30	283761
36	31	283760
37	32	320055
38	33	455101
39	34	216830
40	35	215797
41	36	214218
42	37	306796
43	38	305114
44	39	556492
45	40	307242
46	41	306924
47	42	307524
48	43	306558
49	44	305093
50	45	305194
51	46	305455
52	47	305712
53	48	305196
54	49	307119
55	50	206756
56	51	306415
57	52	263443
58	53	264881
59	54	104516
60	55	305660
61	56	127539
62	57	240767
63	58	283597
64	59	314852
65	60	312209
66	61	253943
67	62	46242
68	63	320296
69	64	320295
70	65	168467
71	66	34163
72	67	718771
73	68	266580
74	69	211949
75	70	159021
Sheet1 (2)

 

[Peter_SSs]

So what are the items on that screen shot that you are looking for help with and how are they arrived at manually?