MAX IF/MIN If formula with Array

[sh_gaitonde3901]

Hi,

I have have a table to poulate where I have to calculate below in ranes

- Ranges are 0 (Zero) to 3K, 3K to 6K, 6K to 10K and so on as depicted in table attached in file

- Data columns are:
- Items counted & Containers counted

For above rage of containers counted i.e. say 3K-6K, I have to provide

- Average count of items counted when container count is between 3K-6K (Column J)
- MAX count of items counted when container count is between 3K-6K (Col K)
- MIN count of items counted when container count is between 3K-6K (Col L)

With AverageIFS, formula, I have been able to calculate averages, but hit a wall on how to calculate MAX and MIN

- I tried using nested Max(IF (), IF())) but was unable to get correct result

Need help here. May be this problem could be solved using pivot, but being a table based presentation, would like to use a formula as much possible.

Regards,

[Bernie Deitrick]

Try this - with an added column showing the break points: