Thread: Identifying Min and Max values using array formulas

1. Identifying Min and Max values using array formulas

Hi,

I have a spreadsheet with multiple columns and am looking to identify the minimum and maximum values in specific columns where certain conditions are met in other columns, e.g. where the values column D = '1' and the values in column E = 'A' that the maximum value in column F be returned.

I have therefore written the following,

{=MAX(IF(AND('Sheet1'!\$D\$2:\$D\$572='Sheet2'!H28,She et1'!\$E\$2:\$E\$572='Sheet2'!I28),'Sheet1'!\$F\$2:\$F\$57 2,""))}

For a reason I cannot work out (having never really used array formulas) this is returning a #VALUE error. If I do not enter it as an array formula I get the maximum value in column F irrespective of the values in coums D and E.

Any advice/guidance that can be offered is appreciated.

Robin

2. You can't use AND that way in an array formula, so you'll need nested ifs:
=MAX(IF('Sheet1'!\$D\$2:\$D\$572='Sheet2'!H28,IF(Sheet 1'!\$E\$2:\$E\$572='Sheet2'!I28,'Sheet1'!\$F\$2:\$F\$572," "),""))

3. The Following User Says Thank You to rory For This Useful Post:

RobinHackshall (2011-09-15)

4. Rory,

Thanks. Formulas now working.

Robin

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•