Second highest with a condition

[EinarOSies]

I am having this formula "INDEX($B$4:$B$108,MATCH(MAXIFS(N4:N108,N4:N108,">=1",N4:N108,"<=100"),$N$4:$N$108,0))" which gives me only the first indentification value in column "B" which corresponds to the maximum value in column "N" with a condition of the range from 1-to-100. Just as I said I get only the first identification value in column "B" but the maximum value are more than one(1).



Please can I get a similar formula to find the other identification values in column "B" with the maximum value in column "N" with the condition above. For example 1st ID num.,2nd ID num,............nth ID num with the same maximum value in column "N" which correspondence to column "B".



Thanks.

 

[Eric W]

Try:

Book2
	A	B												N	O	P
1
2
3		ID Value														ID Value
4		1												0		5
5		2												0		9
6		3												17		12
7		4												21
8		5												97
9		6												3
10		7												-7
11		8												8
12		9												97
13		10												21
14		11												77
15		12												97
16		13												0
17		14												0
18		15												2
19		16												3
20		17												4
21		18												123
22		19												145
23		20												2
24
Sheet7

Cell Formulas
Range	Formula
P4:P10	P4	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$4:$B$108)/($N$4:$N$108=MAXIFS($N$4:$N$108,$N$4:$N$108,">=1",$N$4:$N$108,"<=100")),ROWS(P$4:P4))),"")

 

[EinarOSies]

Please thanks

 

[EinarOSies]

Please it seems the formula is giving me wrong results

 

[Eric W]

In what way? Can you show an example of your sheet with the expected results? Can you use the XL2BB tool to show your sheet? That's what I used in post 2. You can click the link in my signature to get instructions how to load it.

 

[EinarOSies]

Please this is a link to the file. it will be recommended to view it in a 70%
File

 

[Eric W]

I'm sorry, I can't download files from the internet. You'll need to find some other way of explaining your problem, or perhaps someone else would be willing to look at your file.

 

[Fluff]

It's not a valid link, the link is pointing to "http://Please this is a link to the file. it will be recommended to view it in a 70%"

 

[EinarOSies]

KUMASI ASSEMBLY HALL ECG MONITORING SHEET.xlsx

Microsoft Excel Workbook

1drv.ms

It's not a valid link, the link is pointing to "http://Please this is a link to the file. it will be recommended to view it in a 70%"

 

[Fluff]

The data in the file is like

KUMASI ASSEMBLY HALL ECG MONITORING SHEET.xlsx
	B	C	D	E									N	O	P	Q	R
1	DATE	DAY	DAY2	…									Column3	Column4	Column5	Column6	Column7
2			6AM REC.	6PM REC.
3
4	02/01/2021	Saturday	6.7	2.79									########		31/03/2021	3.91	-478.5
5	03/01/2021	Sunday	481.29	475.42									GH₵13.68			5.87	19.55
6	04/01/2021	Monday	455.87	450.01									GH₵12.71			5.86	18.57
7	05/01/2021	Tuesday	431.44	425.58									GH₵15.65			5.86	21.51
8	06/01/2021	Wednesday	404.07	389.21									GH₵0.57			14.86	15.44
9	07/01/2021	Thursday	373.77	368.89									GH₵14.67			4.88	19.55
10	08/01/2021	Friday	349.34	352.5									GH₵32.71			-3.16	29.55
11	09/01/2021	Saturday	322.95	318.06									GH₵13.68		TRUE	4.89	18.57
12	10/01/2021	Sunday	299.49	293.63									GH₵13.69			5.86	19.55
13	11/01/2021	Monday	274.08	268.21									GH₵10.74		02/03/2021	5.87	16.61
14	12/01/2021	Tuesday	251.6	246.65									GH₵15.85			4.95	20.8
15	13/01/2021	Wednesday	225.85	217.53									GH₵12.49			8.32	20.81
16	14/01/2021	Thursday	196.72	189.44									GH₵13.52			7.28	20.8
17	15/01/2021	Friday	168.64	162.4									GH₵7.56			6.24	13.8
18	16/01/2021	Saturday	148.6	136.4									GH₵8.60			12.2	20.8
19	17/01/2021	Sunday	115.6	109.35									GH₵15.59		TRUE	6.25	21.84
20	18/01/2021	Monday	87.51	85.43									GH₵19.76			2.08	21.84
21	19/01/2021	Tuesday	63.59	57.35									GH₵11.44		25/01/2021	6.24	17.68
22	20/01/2021	Wednesday	39.67	34.47									GH₵14.56			5.2	19.76
23	21/01/2021	Thursday	14.71	5.34									########			9.37	-494.8
24	22/01/2021	Friday	500.14	498.06									GH₵18.72			2.08	20.8
25	23/01/2021	Saturday	477.26	469.98									GH₵11.44			7.28	18.72
26	24/01/2021	Sunday	451.26	445.02									GH₵19.53			6.24	25.77
27	25/01/2021	Monday	419.25	409.4									GH₵22.96			9.85	32.81
28	26/01/2021	Tuesday	376.59	366.72									GH₵22.95		TRUE	9.87	32.82
29	27/01/2021	Wednesday	333.9	320.77									GH₵21.34			13.13	34.47
30	28/01/2021	Thursday	286.3	274.81									GH₵21.34		03/03/2021	11.49	32.83
31	29/01/2021	Friday	241.98	232.14									GH₵22.99			9.84	32.83
32	30/01/2021	Saturday	199.31	191.14									GH₵21.41			8.17	29.58
33	31/01/2021	Sunday	161.56	151.71									GH₵27.09			9.85	36.94
34	01/02/2021	Monday	114.77	104.76									GH₵5.39			10.01	15.4
35	02/02/2021	Tuesday	89.36	83.4									GH₵13.50			5.96	19.46
36	03/02/2021	Wednesday	63.94	57.1									GH₵11.28		TRUE	6.84	18.12
37	04/02/2021	Thursday	38.98	31.69									GH₵11.28			7.29	18.57
38	05/02/2021	Friday	13.12	6.28									########			6.84	-479.47
39	06/02/2021	Saturday	485.75	479.89									GH₵12.71			5.86	18.57
40	07/02/2021	Sunday	461.32	52.52									########			408.8	-381.43
41	08/02/2021	Monday	433.95	426.13									GH₵13.68			7.82	21.5
42	09/02/2021	Tuesday	404.63	398.78									GH₵14.68			5.85	20.54
43	10/02/2021	Wednesday	378.24	373.35									GH₵15.63			4.89	20.52
44	11/02/2021	Thursday	352.83	346.96									GH₵13.92			5.87	19.8
45	12/02/2021	Friday	327.16	319.88									-GH₵1.93			7.28	5.35
46	13/02/2021	Saturday	314.53	288.68									-GH₵5.04			25.85	20.8
47	14/02/2021	Sunday	267.88	262.68									GH₵16.65			5.2	21.85
48	15/02/2021	Monday	240.83	233.55									GH₵9.35			7.28	16.63
49	16/02/2021	Tuesday	216.92	207.55									GH₵11.43			9.37	20.8
50	17/02/2021	Wednesday	186.75	180.51									GH₵11.44			6.24	17.68
51	18/02/2021	Thursday	162.83	155.55									GH₵13.53			7.28	20.81
52	19/02/2021	Friday	134.74	126.42									GH₵12.46			8.32	20.78
53	20/02/2021	Saturday	105.64	95.22									GH₵11.42			10.42	21.84
54	21/02/2021	Sunday	73.38	66.1									GH₵12.48			7.28	19.76
55	22/02/2021	Monday	46.34	39.05									GH₵14.11			7.29	21.4
56	23/02/2021	Tuesday	17.65	9.65									########			8	-463.69
57	24/02/2021	Wednesday	473.34	456.58									GH₵16.72			16.76	33.48
58	25/02/2021	Thursday	423.1	414.25									GH₵25.62			8.85	34.47
59	26/02/2021	Friday	379.78	363.39									GH₵16.46			16.39	32.85
60	27/02/2021	Saturday	330.54	320.7									GH₵34.48			9.84	44.32
61	28/02/2021	Sunday	276.38	236.5									-GH₵36.34			39.88	3.54
62	01/03/2021	Monday	232.96	229.66									GH₵17.22			3.3	20.52
63	02/03/2021	Tuesday	209.14	201.32									GH₵14.33			7.82	22.15
64	03/03/2021	Wednesday	179.17	172.97									GH₵10.41			6.2	16.61
65	04/03/2021	Thursday	156.36	150.49									GH₵19.54			5.87	25.41
66	05/03/2021	Friday	125.08	118.24									GH₵15.31			6.84	22.15
67	06/03/2021	Saturday	96.09	85.94									GH₵12.00			10.15	22.15
68	07/03/2021	Sunday	63.79	58.69									-GH₵1.10			5.1	4
69	08/03/2021	Monday	54.69	44.54									-GH₵3.70			10.15	6.45
70	09/03/2021	Tuesday	38.09	32.23									########			5.86	-479.47
71	10/03/2021	Wednesday	511.7	484.33									-GH₵27.34			27.37	0.03
72	11/03/2021	Thursday	484.3	478.47									GH₵13.72			5.83	19.55
73	12/03/2021	Friday	458.92	451.81									GH₵13.67			7.11	20.78
74	13/03/2021	Saturday	431.03	424.79									GH₵15.16			6.24	21.4
75	14/03/2021	Sunday	403.39	394.99									GH₵10.69			8.4	19.09
76	15/03/2021	Monday	375.9	370.7									GH₵16.95			5.2	22.15
77	16/03/2021	Tuesday	348.55	338.4									GH₵4.35			10.15	14.5
78	17/03/2021	Wednesday	323.9	318.7									GH₵14.56			5.2	19.76
79	18/03/2021	Thursday	298.94	292.69									GH₵12.47			6.25	18.72
80	19/03/2021	Friday	273.97	268.77									GH₵14.55			5.2	19.76
81	20/03/2021	Saturday	249.01	241.73									GH₵10.40			7.28	17.68
82	21/03/2021	Sunday	224.05	216.77									GH₵11.44			7.28	18.72
83	22/03/2021	Monday	198.05	189.72									GH₵11.43			8.33	19.76
84	23/03/2021	Tuesday	169.96	160.6									GH₵10.65			9.36	20.01
85	24/03/2021	Wednesday	140.59	132.47									GH₵24.70			8.12	32.82
86	25/03/2021	Thursday	99.65	91.44									GH₵83.23			8.21	91.44
87													-GH₵58.61			0	-58.61
88	26/03/2021	Friday	58.61	48.46									GH₵14.16			10.15	24.31
89	27/03/2021	Saturday	24.15										########			24.15	-519.22
90	27/03/2021	Saturday	519.22	509.07									GH₵14.18			10.15	24.33
91	28/03/2021	Sunday	484.74	433.37									-GH₵61.72			51.37	-10.35
92	29/03/2021	Monday	443.72	435.52									GH₵32.83			8.2	41.04
93	30/03/2021	Tuesday	394.48	366.58									-GH₵5.75			27.9	22.15
94	31/03/2021	Wednesday	344.43	334.28									GH₵1.72			10.15	11.87
95	01/04/2021	Thursday	322.41	312.26									GH₵12.00			10.15	22.15
DATA RECORDS

Cell Formulas
Range	Formula
N4:N95	N4	=ROUNDDOWN(SUM(M4-L4),2)
Q4:Q95	Q4	=SUM(D4-E4)
R4:R95	R4	=SUM(F4-G4)
P4:P8,P10	P4	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($Q$4:$Q$108=MAXIFS($Q$4:$Q$108,$Q$4:$Q$108,">=1",$Q$4:$Q$108,"<=60")),ROWS(P$4:P4))),"")
P9	P9	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($Q$4:$Q$108=MAXIFS($Q$4:$Q$108,$Q$4:$Q$108,">=1",$Q$4:$Q$108,"<=100")),ROWS(P$4:P9))),"")
P11	P11	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($Q$4:$Q$108=MAXIFS($Q$4:$Q$108,$Q$4:$Q$108,">=1",$Q$4:$Q$108,"<=60")),ROWS(P$4:P5))),"")=""
P13:P18	P13	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($R$4:$R$108=MAXIFS($R$4:$R$108,$R$4:$R$108,">=1",$R$4:$R$108,"<=60")),ROWS(P$13:P13))),"")
P19	P19	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($R$4:$R$108=MAXIFS($R$4:$R$108,$R$4:$R$108,">=1",$R$4:$R$108,"<=60")),ROWS(P$13:P19))),"")=""
P21:P25,P27	P21	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($Q$4:$Q$108=MINIFS($Q$4:$Q$108,$Q$4:$Q$108,">=1",$Q$4:$Q$108,"<=60")),ROWS(P$21:P21))),"")
P26	P26	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($Q$4:$Q$108=MINIFS($Q$4:$Q$108,$Q$4:$Q$108,">=1",$Q$4:$Q$108,"<=100")),ROWS(P$21:P26))),"")
P28	P28	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($Q$4:$Q$108=MINIFS($Q$4:$Q$108,$Q$4:$Q$108,">=1",$Q$4:$Q$108,"<=60")),ROWS(P$21:P22))),"")=""
P30:P35	P30	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($R$4:$R$108=MINIFS($R$4:$R$108,$R$4:$R$108,">=1",$R$4:$R$108,"<=60")),ROWS(P$30:P30))),"")
P36	P36	=IFERROR(INDEX($B$4:$B$108,AGGREGATE(15,6,ROW($B$4:$B$108)/($R$4:$R$108=MINIFS($R$4:$R$108,$R$4:$R$108,">=1",$R$4:$R$108,"<=60")),ROWS(P$30:P31))),"")=""
D62	D62	=E61-3.54
D64,D94,D77,D67:D68	D64	=E63-22.15
E67,E69,E94:E95,E90,E88,E77	E67	=[@DAY2]-10.15
E68	E68	=[@DAY2]-5.1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B33:J33	Expression	="GH₵ "&TEXT(AGGREGATE(14,6,MID($J$4:$J$108,5,20)/((MID($J$4:$J$108,5,20)+0>=1)*(MID($J$4:$J$108,5,20)+0<=60)),1),"###.00")=B33	text	NO
B33:J33	Expression	="GH₵ "&TEXT(AGGREGATE(14,6,MID($J$4:$J$108,5,20)/((MID($J$4:$J$108,5,20)+0>=1)*(MID($J$4:$J$108,5,20)+0<=60)),1),"###.00")=B33	text	NO