CODEWITHSUNDEEP
excelindexingmaxformulaminimum
hrw08
hrw08

Reputation: 25

Identify minimum and maximum values based on 3 criteria

In the table shown, I need a formula for column D that will indicate the first date (minimum) and most recent date (maximum) that each participant (in column A) took survey A (in column C). Column D would need to indicate "first" and "last" tied to the Participant ID--for example, I would want D2 to populate with "3Last" and D5 to populate with "3First." Column E displays what I would need column D to display. If it's not a first or last date (something in between), or if it's not survey A, the cell in column D would be left blank or 0. If there is only one date that meets the criteria, it should return "First" rather than "Last." I'm pretty stumped on this one... Any help is much appreciated!

Upvotes: 0

Views: 44

Answers (1)

Nathan Unterseher
Nathan Unterseher

Reputation: 46

In E2, insert the ARRAY formula listed below. If you have never used an array formula, follow these steps:

  1. select the formula from this page
  2. copy it
  3. go to excel
  4. select cell E2,
  5. press the 'F2' key
  6. paste the formula
  7. press CTRL+SHIFT+Enter (instead of just pressing enter)

To copy down, follow these steps:

  1. Copy cell E2
  2. Move down to cell E3 (instead of selecting a range)
  3. Paste in cell E3
  4. Select your range and paste from there.

If you don't copy down in this manner, it will tell you that you cannot change the array...

 =IF($C2="A",IF($B2=MIN(IF(($C$2:$C$7=$C2)*($A$2:$A$7=$A2),$B$2:$B$7)),CONCATENATE($A2,"Last"),IF($B2=MAX(IF(($C$2:$C$7=$C2)*($A$2:$A$7=$A2),$B$2:$B$7)),CONCATENATE($A2,"First"))),0)

HTH

Upvotes: 1

Related Questions