Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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," "),""))
    Regards,
    Rory

    Microsoft MVP - Excel

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

    RobinHackshall (2011-09-15)

  4. #3
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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
  •