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]

Thank you Fluff for copying this. Was there data in columns L:M? The N formula is showing empty when I copy it since it's not seeing anything in L:M. I don't think that's important though.

EinarOSies, could you show what your expected result would be? Your formula seems to be in column P, looking at the data in columns B and Q. But I'm seeing inconsistent formulas in column P. Some have a range of 1-60, some have a range of 1-100. Which is it, or does it vary in some predictable manner? Also on rows 11, 19, 28, those formulas have ="" on the end, completely changing the result of the formula.

The formula is designed to show multiple cases where 2 or more rows have the same maximum value, but looking at the table, I don't see any instances of that.

Could you say in words what you want, for example: I want P4 to be 18/1/21, and P5 to be 22/1/21 because those values are on rows 20 and 24 and the values in Q20 and Q24 are both 2.08, and 2.08 is the lowest value in column Q.

 

[Fluff]

Was there data in columns L:M?

Yes there was, hadn't noticed that they were being referenced in col N.

KUMASI ASSEMBLY HALL ECG MONITORING SHEET.xlsx
	B	C	D	E	F	G					L	M	N	O	P	Q	R
1	DATE	DAY	DAY2	…	NIGHT	….					Column1	Column2	Column3	Column4	Column5	Column6	Column7
2			6AM REC.	6PM REC.	6PM REC.	6AM REC.
3
4	02/01/2021	Saturday	6.7	2.79	2.79	481.29					GH₵3.91	-GH₵478.50	########		31/03/2021	3.91	-478.5
5	03/01/2021	Sunday	481.29	475.42	475.42	455.87					GH₵5.87	GH₵19.55	GH₵13.68			5.87	19.55
6	04/01/2021	Monday	455.87	450.01	450.01	431.44					GH₵5.86	GH₵18.57	GH₵12.71			5.86	18.57
7	05/01/2021	Tuesday	431.44	425.58	425.58	404.07					GH₵5.86	GH₵21.51	GH₵15.65			5.86	21.51
8	06/01/2021	Wednesday	404.07	389.21	389.21	373.77					GH₵14.86	GH₵15.44	GH₵0.57			14.86	15.44
9	07/01/2021	Thursday	373.77	368.89	368.89	349.34					GH₵4.88	GH₵19.55	GH₵14.67			4.88	19.55
10	08/01/2021	Friday	349.34	352.5	352.5	322.95					-GH₵3.16	GH₵29.55	GH₵32.71			-3.16	29.55
11	09/01/2021	Saturday	322.95	318.06	318.06	299.49					GH₵4.89	GH₵18.57	GH₵13.68		TRUE	4.89	18.57
12	10/01/2021	Sunday	299.49	293.63	293.63	274.08					GH₵5.86	GH₵19.55	GH₵13.69			5.86	19.55
13	11/01/2021	Monday	274.08	268.21	268.21	251.6					GH₵5.87	GH₵16.61	GH₵10.74		02/03/2021	5.87	16.61
14	12/01/2021	Tuesday	251.6	246.65	246.65	225.85					GH₵4.95	GH₵20.80	GH₵15.85			4.95	20.8
15	13/01/2021	Wednesday	225.85	217.53	217.53	196.72					GH₵8.32	GH₵20.81	GH₵12.49			8.32	20.81
16	14/01/2021	Thursday	196.72	189.44	189.44	168.64					GH₵7.28	GH₵20.80	GH₵13.52			7.28	20.8
17	15/01/2021	Friday	168.64	162.4	162.4	148.6					GH₵6.24	GH₵13.80	GH₵7.56			6.24	13.8
18	16/01/2021	Saturday	148.6	136.4	136.4	115.6					GH₵12.20	GH₵20.80	GH₵8.60			12.2	20.8
19	17/01/2021	Sunday	115.6	109.35	109.35	87.51					GH₵6.25	GH₵21.84	GH₵15.59		TRUE	6.25	21.84
20	18/01/2021	Monday	87.51	85.43	85.43	63.59					GH₵2.08	GH₵21.84	GH₵19.76			2.08	21.84
21	19/01/2021	Tuesday	63.59	57.35	57.35	39.67					GH₵6.24	GH₵17.68	GH₵11.44		25/01/2021	6.24	17.68
22	20/01/2021	Wednesday	39.67	34.47	34.47	14.71					GH₵5.20	GH₵19.76	GH₵14.56			5.2	19.76
23	21/01/2021	Thursday	14.71	5.34	5.34	500.14					GH₵9.37	-GH₵494.80	########			9.37	-494.8
24	22/01/2021	Friday	500.14	498.06	498.06	477.26					GH₵2.08	GH₵20.80	GH₵18.72			2.08	20.8
25	23/01/2021	Saturday	477.26	469.98	469.98	451.26					GH₵7.28	GH₵18.72	GH₵11.44			7.28	18.72
26	24/01/2021	Sunday	451.26	445.02	445.02	419.25					GH₵6.24	GH₵25.77	GH₵19.53			6.24	25.77
27	25/01/2021	Monday	419.25	409.4	409.4	376.59					GH₵9.85	GH₵32.81	GH₵22.96			9.85	32.81
28	26/01/2021	Tuesday	376.59	366.72	366.72	333.9					GH₵9.87	GH₵32.82	GH₵22.95		TRUE	9.87	32.82
29	27/01/2021	Wednesday	333.9	320.77	320.77	286.3					GH₵13.13	GH₵34.47	GH₵21.34			13.13	34.47
30	28/01/2021	Thursday	286.3	274.81	274.81	241.98					GH₵11.49	GH₵32.83	GH₵21.34		03/03/2021	11.49	32.83
31	29/01/2021	Friday	241.98	232.14	232.14	199.31					GH₵9.84	GH₵32.83	GH₵22.99			9.84	32.83
32	30/01/2021	Saturday	199.31	191.14	191.14	161.56					GH₵8.17	GH₵29.58	GH₵21.41			8.17	29.58
33	31/01/2021	Sunday	161.56	151.71	151.71	114.77					GH₵9.85	GH₵36.94	GH₵27.09			9.85	36.94
34	01/02/2021	Monday	114.77	104.76	104.76	89.36					GH₵10.01	GH₵15.40	GH₵5.39			10.01	15.4
35	02/02/2021	Tuesday	89.36	83.4	83.4	63.94					GH₵5.96	GH₵19.46	GH₵13.50			5.96	19.46
36	03/02/2021	Wednesday	63.94	57.1	57.1	38.98					GH₵6.84	GH₵18.12	GH₵11.28		TRUE	6.84	18.12
37	04/02/2021	Thursday	38.98	31.69	31.69	13.12					GH₵7.29	GH₵18.57	GH₵11.28			7.29	18.57
38	05/02/2021	Friday	13.12	6.28	6.28	485.75					GH₵6.84	-GH₵479.47	########			6.84	-479.47
39	06/02/2021	Saturday	485.75	479.89	479.89	461.32					GH₵5.86	GH₵18.57	GH₵12.71			5.86	18.57
40	07/02/2021	Sunday	461.32	52.52	52.52	433.95					GH₵408.80	-GH₵381.43	########			408.8	-381.43
41	08/02/2021	Monday	433.95	426.13	426.13	404.63					GH₵7.82	GH₵21.50	GH₵13.68			7.82	21.5
42	09/02/2021	Tuesday	404.63	398.78	398.78	378.24					GH₵5.85	GH₵20.54	GH₵14.68			5.85	20.54
43	10/02/2021	Wednesday	378.24	373.35	373.35	352.83					GH₵4.89	GH₵20.52	GH₵15.63			4.89	20.52
44	11/02/2021	Thursday	352.83	346.96	346.96	327.16					GH₵5.87	GH₵19.80	GH₵13.92			5.87	19.8
45	12/02/2021	Friday	327.16	319.88	319.88	314.53					GH₵7.28	GH₵5.35	-GH₵1.93			7.28	5.35
46	13/02/2021	Saturday	314.53	288.68	288.68	267.88					GH₵25.85	GH₵20.80	-GH₵5.04			25.85	20.8
47	14/02/2021	Sunday	267.88	262.68	262.68	240.83					GH₵5.20	GH₵21.85	GH₵16.65			5.2	21.85
48	15/02/2021	Monday	240.83	233.55	233.55	216.92					GH₵7.28	GH₵16.63	GH₵9.35			7.28	16.63
49	16/02/2021	Tuesday	216.92	207.55	207.55	186.75					GH₵9.37	GH₵20.80	GH₵11.43			9.37	20.8
50	17/02/2021	Wednesday	186.75	180.51	180.51	162.83					GH₵6.24	GH₵17.68	GH₵11.44			6.24	17.68
DATA RECORDS

Cell Formulas
Range	Formula
F4:F50	F4	=E4
G4:G50	G4	=D5
L4:L50	L4	=SUM(D4-E4)
M4:M50	M4	=SUM(F4-G4)
N4:N50	N4	=ROUNDDOWN(SUM(M4-L4),2)
Q4:Q50	Q4	=SUM(D4-E4)
R4:R50	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))),"")=""

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