Most recent value greater than x

[Skawn]

Hullo folks, I'm totally stuck on this one, help greatly appreciated!

Here's an example of what my data looks like, in reality there's 1000's of rows and 100 columns...

Please Login or Register  to view this content.

So, what I'm after is the most recent date on which a user goes over 2000. I've populated column I manually with what the value *should* be, I just can't figure out how to do this with a formula.

Using Excel 2007, have attached the sample data as an xlsx too.

[NBVC]

Try:

=MAX(IF($B2:$H2>2000,$B$1:$H$1))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

or

=LOOKUP(2,1/($B2:$H2>2000),$B$1:$H$1)

entered with just ENTER and copied down

[zbor]

Or without IF (ctrl+shift+enter)...

=MAX($B$1:$H$1*(B2:H2>2000))

[Skawn]

Those are awesome.

I do kinda like to understand why stuff is working though.

I'd discounted using MAX as I figured it'd give me the date of the highest value in the series, not the latest entry, similarly I thought lookup wouldn't identify the right date... can anyone explain why this works?

[zbor]

MAX don't give date of highest value. Check again. both solutions give same result.

Difference could be if dates are not sorted in ascending order.

In that case (see in example 1.1.2011 and 5.1.2011)
- MAX would return highest date
- LOOKUP would return last date entered.

You can choose solution what you would like to show if date is entered that way.

[zbor]

How do they work?

MAX:

=MAX($B$1:$H$1*(B2:H2>2000))

It take first row (40545 40546 40547 etc) and multiply it with (B2:H2>2000) that would return 0's and 1's (FALSE and TRUE to be honest).

so you would get

Please Login or Register  to view this content.

Formated as date it's 5.1.

LOOKUP:

=LOOKUP(2,1/($B2:$H2>2000),$B$1:$H$1)
It search for number 2 in array 1/(0,0,1,1,1,0,0)

Please Login or Register  to view this content.

Note that in first solution it will always return MAX, and in second solution it will retunr last date entered (in case of 2.1, 3.1, 4.1, 5.1, 1.1, 6.1, 7.1. it will return 1.1)

[Skawn]

Brilliant, that makes sense now. Thanks for the help.

[cannon_fodder]

A google search brought me to this website, forum and specific thread. Thanks for the answer, too, as I was looking for a way to find how many days it had been since a commodity price had either been higher or lower than today's price. This worked perfectly for me.