Find Max of Year

[Pestomania]

Hello,

I am trying to find the "max" per year. I have this formula, but it returns value of 1 even when the max is 0 and doesn't define the year appropriately. This formula works when it is "sumproduct" but not when it is max.. It does return the appropriate number whenever there is a value greater than 0 in the selection.

=MAX($G$15:$AP$15,--(YEAR($G$14:$AP$14)=G$10))

10 Year Capacity Planning Model Rev 20240321 - Full_Morin_Densification.xlsm
	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
10		2023	2024	2025
11	Max	39	39	39
12	Sum	12	37	88
13
14		1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	12/1/2023	1/1/2024	2/1/2024	3/1/2024	4/1/2024	5/1/2024	6/1/2024	7/1/2024	8/1/2024	9/1/2024	10/1/2024	11/1/2024	12/1/2024	1/1/2025	2/1/2025	3/1/2025	4/1/2025	5/1/2025	6/1/2025	7/1/2025	8/1/2025	9/1/2025	10/1/2025	11/1/2025	12/1/2025
15		2	0	0	10	0	0	0	0	0	0	0	0	4	0	0	6	0	0	0	0	0	12	15	0	0	0	39	0	14	0	35	0	0	0	0	0
Proposed Equipment Add

Cell Formulas
Range	Formula
G11:I11	G11	=MAX($G$15:$AP$15,--(YEAR($G$14:$AP$14)=G$10))
G12:I12	G12	=SUMPRODUCT($G$15:$AP$15,--(YEAR($G$14:$AP$14)=G$10))

 

[Fluff]

Not sure why you are using a table when you have what looks like a load of blank rows in there, as that defeats the main reason for a table.
try

=MAX(FILTER(INDIRECT("Morin_"&$A5&"_Equipment_Proposed"),YEAR(Capacity_Charts_Date_Range)=C$1+0))-SUM($B5:B5)

 

[Pestomania]

Not sure why you are using a table when you have what looks like a load of blank rows in there, as that defeats the main reason for a table.
try

=MAX(FILTER(INDIRECT("Morin_"&$A5&"_Equipment_Proposed"),YEAR(Capacity_Charts_Date_Range)=C$1+0))-SUM($B5:B5)

That worked both in and out of the table. I am slowly adding all of those references as named ranges for the remainder of the tables.

 

[bobsan42]

How would that be if it works whenever it is outside of a table?

It provides results whenever not in a table:
View attachment 109003

But as soon as I turn that range into a table, it stops working. I can work with it and just work on different methods, but tables look nice.

Just a guess, but ... turning the range into a table you use the years as a header row. Before the table they are numbers, but the table headers are text strings, so you get no match comparing numbers with strings.
so try this:

=MAX(FILTER(INDIRECT("Morin_"&$A5&"_Equipment_Proposed"),YEAR(Capacity_Charts_Date_Range)=VALUE(C$1)))-SUM($B5:B5)