Min, Max and Average within Array

[Metcalfe]

Hello, with reference to the attached image, I would like to calculate the minimum, maximum and average of column AF if the respective values in column AE fall between the range between A2 and B2, A3 and B3, etc. etc.

Can anyone help with this.

Thanks,

Steven

Capture.JPG

[vlady]

Welcome to the forum.

May I suggest that you upload a real excel file so that members will not recreate your workbook.

Regards,
Vlady

[Glenn Kennedy]

Using variants of an array formula like this one:

=MIN(IF($G$2:$G$18>=$A2,IF($G$2:$G$18<$B2,$H$2:$H$18)))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[dosydos]

Looking at your picture it looked like you wanted all your data (avg, Min, and max) in the same cell. If that is the case i used Glenn's formula above and added the formatting formulas:

="MIN: "&-ROUND(MIN(IF(G2:G18>=$A2,IF($G$2:$G$18<$B2,$H$2:$H$18))),2)&CHAR(10)&"MAX: "&ROUND(MAX(IF($G$2:$G$18>=$A2,IF($G$2:$G$18<$B2,$H$2:$H$18))),2)&CHAR(10)&"AVG: "&ROUND(AVERAGE(IF($G$2:$G$18>=$A2,IF($G$2:$G$18<$B2,$H$2:$H$18))),2)

Make sure to still enter this formula with ctrl+shift+enter as it is still an array formula

[Metcalfe]

Excellent thanks very much, issue solved!

[Glenn Kennedy]

FWIW... putting all those values into one cell is (In my view) a bit like carving them into stone. There's not much further you can do with the numbers, without the use of horrendous formulae to unpick the concatenation...