Find the Date of Last Occurrence [SOLVED]

[imla]

Hi,

given a 2 rows:

row date: is an incremented serie of dates B2, B2+1, C2+1, ...etc

row data: is an increasing sequence of values from a fixed set X={60,30,20,10}

[pre]

date: 7/22/2013 7/23/2013 7/24/2013 7/25/2013 7/26/2013 7/27/2013 7/28/2013 7/29/2013
data:   60        60        60        30        30        20        10        10

[/pre]
Question: I want a formula given a value x in the set X find the LAST date.

examples:

for x=60 the date is 7/24/2013

for x=30 the date is 7/26/2013


PS: in general the sequence may start from 30 to 10 and 20 may not be in the sequence.

 

[Faseeh]

Hi Imla,


With your data in A1:H2, like below:

[pre]

7/22/2013	7/23/2013	7/24/2013	7/25/2013	7/26/2013	7/27/2013	7/28/2013	7/29/2013
60	        60	        60	        30	        30	               20	        10	        10[/pre]
Try this:


=INDEX(A1:H1,0,SUMPRODUCT(MAX((A2:H2=60)*(COLUMN(A2:H2)))))

You can replace 60 with cell reference.


Regards,

 

[imla]

Thanks, It worked very well.

I want to anderstand it.

the COLUMN() return the serie the array (1,2,3 ...)

the (A2:H2=60) return the array of (0,1) (True,false) if data is 60

the ()*() return array of coloumn N° such the condition is true

but what max return ?! is it the array where all elements are the max columns N°!?

So how I can interprete the work of SUMPRODUCT or SUM if I enter an array formula?


Thanks in advance.

 

[Luke M]

The MAX reduces the array of column #'s down to a single digit (the largest, of course). The SUMPRODUCT doesn't really do anything, just lets you handle arrays naturally w/o having to use a special array formula confirmation. It then takes that number and uses it in the INDEX function.


If you wanted to just confirm as an array formula, you can do:

=INDEX(1:1,0,MAX((A2:H2=60)*(COLUMN(A2:H2)))

 

[imla]

Thanks to you both, every thing is clear now.