2

What I'm trying to do is get the most recent date from a row while the cell below it has a value of 1.

I've tried working with MAX and hLOOKUP but neither are doing what I need.

Cell A3 to G3 contain dates. Cell A4 to G4 either contain a 1 or a 0. Cell A5 wants to return the max value of a cell in the range (A3:G3) while the cell below the cell that contains the max value=1

3 Answers 3

6

There is a pretty solution using an Array formula.

=MAX(IF(1=$A$4:$G$4, $A$3:$G$3, 0))

How it works: the conditional statement created an array of {date or 0,...} based on whether there is a 1 in the range A4:G4. If there is, the value above it (date-value) is added to the array, otherwise 0 is added. The max function return the highest value in the array.

Notes:

1. The formulae must be typed in then instead of pressing ENTER, press CTRL-SHIFT-ENTER to specify that it is an array formula. You will know if you have done this correctly as the formula will display curly braces { } at each end. These can not be entered manually.

2. Ensure that all date cells are formatted to date-type. This includes the cell you put your formulae in :)

3. There is a potential bug if none of the dates have a 1 under it. The max function will find the largest value (0) and display it as a date, something like 1/0/1900.

1
  • 1
    Excellent, Nic - +1 - To get round point 3 you can custom format cell as m/d/yyyy;; - with the two semi-colons at the end zero displays as blank....or replace MAX with LARGE, in which case the formula returns an error in case of no 1s....and you can trap that and return some text, e.g. =IFERROR(LARGE(IF($A$4:$G$4=1,$A$3:$G$3),1),"No valid data") Commented Oct 3, 2014 at 23:09
2

Here's another array formula solution that works along the same lines as Brian Folan's solution.

=MAX(A3:G3*A4:G4)

Enter with Ctrl+Shift+Enter. This will return the date value of the max date with a 1 below it in row 4.

If you would like to return the value in a date format, you can wrap it in a TEXT function and specify the format in the second argument.

=TEXT(MAX(A3:G3*A4:G4),"mm/dd/yyyy")

This must also be entered with Ctrl+Shift+Enter.

1

Not sure if it will fit into what you are trying to implement but how about...

1) Set A5 = A3 * A4

2) Same for B6 = B3 * B4... etc.

3) then use A5 =MAX(A5:G5)

enter image description here

1
  • That's definitely unconventional! It does what I need it to do the only problem that I am having with it is that I need to repeat it ~100 times. If there's another way to do it that's a little more real estate friendly, then I'd love to see that. If not, I'll figure out how to make this do. Thank you!! Commented Oct 3, 2014 at 22:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .