Formula Query

[motherboard85]

Hi i have a dataset of utility consumption with dates down the left and time in 30 min increments along the top. Im looking for a formula than can tell me the highest consumption but also what time of that day it occurred

 

[awoohaw]

There may be more concise formulas, but this seems to work:

=INDEX($A$2:$Z$11,
SUM(($A$2:$Z$11=MAX($A$2:$Z$11))*(ROW($A$15:$A$24)-ROW(A14))),
SUM(($A$2:$Z$11=MAX($A$2:$Z$11))*(COLUMN($A$13:$Z$13))))

 

[motherboard85]

There may be more concise formulas, but this seems to work:

=INDEX($A$2:$Z$11,
SUM(($A$2:$Z$11=MAX($A$2:$Z$11))*(ROW($A$15:$A$24)-ROW(A14))),
SUM(($A$2:$Z$11=MAX($A$2:$Z$11))*(COLUMN($A$13:$Z$13))))

Hi Can this apply to one line at a time?

 

[awoohaw]

I didn't read your question that way. Are you asking for a new column that tells you what hour of each line had the highest usage?

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1		00:00	00:30	01:00	01:30	02:00	02:30	03:00	03:30	04:00	04:30	05:00	05:30	06:00	06:30	07:00	07:30	08:00	08:30	09:00	09:30	10:00	10:30	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30	18:00	18:30	19:00	19:30	20:00	20:30	21:00	21:30	22:00	22:30	23:00	23:30
2	10/02/2024	293.57	10.83	12.84	48.75	287.40	45.39	255.12	79.72	278.96	197.21	167.43	248.20	18.00	101.69	8.89	19.13	24.73	117.83	99.43	120.23	283.03	211.86	237.58	297.10	171.03	247.37	10.56	242.04	137.76	101.87	54.27	164.27	48.98	65.16	288.04	31.65	268.11	251.21	197.94	93.73	120.24	19.19	31.75	27.47	62.57	119.48	98.04	159.02	297.10	11:30:00
3	11/02/2024	286.47	89.32	251.70	266.15	163.62	268.01	268.70	122.93	18.19	43.53	6.57	218.85	86.27	65.12	103.74	287.91	115.54	264.03	271.55	209.07	155.12	148.49	249.42	46.61	226.30	296.24	16.98	158.09	58.57	78.17	110.28	179.25	264.84	298.00	38.03	229.56	24.77	299.87	144.17	114.22	18.26	266.17	209.59	258.56	192.48	186.49	151.98	69.50	299.87	18:30:00
4	12/02/2024	148.43	120.24	114.51	175.50	29.28	69.57	58.94	220.94	135.40	235.68	74.47	206.17	153.29	34.68	69.94	294.03	26.25	39.52	198.15	26.75	204.07	30.58	244.90	288.28	73.32	158.99	76.03	136.21	34.04	108.96	215.33	297.62	200.17	143.13	135.72	126.61	281.08	151.79	88.68	172.16	101.35	88.27	185.59	214.05	15.88	197.80	178.29	204.97	297.62	15:30:00
5	13/02/2024	224.91	130.25	107.29	170.30	81.97	73.70	187.39	181.55	143.28	265.52	210.58	148.41	175.81	34.46	245.37	210.37	55.69	126.65	78.31	11.53	284.15	1.09	223.21	173.40	91.51	268.94	192.47	126.09	34.32	37.45	154.42	245.98	275.90	251.72	34.18	101.31	57.70	125.75	49.62	28.48	125.90	94.51	210.82	57.68	245.91	241.74	32.77	277.58	284.15	10:00:00
6	14/02/2024	88.09	190.60	85.95	152.78	71.53	43.26	211.19	24.07	27.69	251.46	173.32	83.47	250.74	131.18	188.94	152.19	268.80	99.08	296.36	230.60	52.45	137.24	18.50	157.32	32.77	90.96	215.73	96.70	183.76	249.46	209.15	93.93	201.13	176.53	26.92	110.74	181.28	288.08	176.69	235.27	97.64	297.01	37.91	37.19	58.35	222.40	151.82	141.61	297.01	20:30:00
7	15/02/2024	128.99	8.01	48.90	210.81	151.89	76.35	27.74	12.90	101.03	3.45	73.12	281.39	51.36	272.08	202.79	72.42	264.24	80.10	54.52	234.55	152.89	5.17	164.68	286.33	252.88	239.80	126.88	253.52	217.64	15.98	63.06	6.38	252.53	63.84	34.30	182.35	41.47	135.35	48.06	169.30	76.42	143.85	284.16	154.15	298.60	71.74	193.51	154.72	298.60	22:00:00
8	16/02/2024	190.78	296.98	108.24	53.31	202.47	221.35	206.62	57.32	185.90	58.65	256.26	17.40	213.28	6.39	4.62	123.67	99.76	115.20	251.57	213.77	63.85	245.64	291.41	219.28	6.77	112.43	269.68	65.15	252.75	87.07	289.03	286.44	27.17	217.69	188.19	55.58	71.48	224.46	239.15	210.10	129.20	114.93	239.77	171.04	115.80	100.39	284.43	103.11	296.98	00:30:00
9	17/02/2024	140.92	115.83	67.63	293.08	275.18	296.23	257.60	81.84	48.98	294.89	147.58	209.37	224.44	281.51	28.12	286.34	160.35	277.57	2.32	159.94	221.68	286.64	200.71	103.63	181.59	96.00	239.64	284.05	205.63	281.16	22.07	71.53	67.68	99.85	220.41	66.47	230.90	225.08	1.31	136.18	42.63	157.97	208.64	39.18	282.77	118.41	126.21	161.86	296.23	02:30:00
Data

Cell Formulas
Range	Formula
AX2:AX9	AX2	=MAX(B2:AW2)
AY2:AY9	AY2	=INDEX($B$1:$AW$1,,MATCH(AX2,B2:AW2,0))

 

[alansidman]

And yet another option is to use Power Query
Load the table into the PQ editor and determine the max value per day
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Column1"}, {{"Max", each List.Max([Value]), type number}})
in
#"Grouped Rows"

Join the original query result on the original table and filter for the time

let
    Source = Table.NestedJoin(Table1, {"Column1", "Max"}, #"Table1 (2)", {"Column1", "Value"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Attribute"}, {"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1 (2)", each not Text.Contains([Attribute], "Column"))
in
    #"Filtered Rows"

Book1
	A	B	C
11	Column1	Max	Attribute
12	2/10/2024	297.1037793	11:30:00 AM
13	2/11/2024	299.8747566	6:30:00 PM
14	2/12/2024	297.6199826	3:30:00 PM
15	2/13/2024	284.1497508	10:00:00 AM
16	2/14/2024	297.0142725	8:30:00 PM
17	2/15/2024	298.601772	10:00:00 PM
18	2/16/2024	296.9774048	12:30:00 AM
19	2/17/2024	296.2268754	2:30:00 AM
Sheet1