Find Max Value in Range

[c3179514]

Hello,

In the below, i'm trying to make a new column in the table to the right. This column needs to find the max value of Remaining Demand corresponding to the range in colum 2 (capacity %).

E.g, it looks at all the values in storage percent between 0% - 25% and tells me the maximum remaining demand value and plots it in the corresponding row.

I tried a few ways and can't get it to work...

If I can do this, I can adjust supply rate to suit to get it to 0 demand as it would be telling me the max number I need to adjust between 0% - 25%. 

[#2]

I am very confused what exactly you need but if you have 365 you should consider MAXIFS() or FILTER() functions. I think maybe the following is what you need:
=INDEX(SORT(FILTER(Table[[Storage]:[Remaining Demand]], (Table[Storage Percent]>=0)*(Table[Storage Percent]<=0.25),0), 4, -1), 1,1)
so basically use FILTER to filter only the rows between 0 and 25% then Sort by column 4 (remaining demand) from high to low and then take the first value from the top row.