Conditional SUBTOTAL

[Dallman Ross]

Great advice is hard to beat. I get that in this group. Thank you!

I have a column of dates. Another column contains ordinary
integers, positive or negative. I want to find the lowest date
where the offset column is a negative number.

Example:

Col. F Col. G
------ ------
18-Apr-08 -12
14-Apr-08 12
7-Apr-08 -6
1-Apr-08 -24
24-Mar-08 -20
19-Mar-08 18


I'm looking for 24-Mar-08 -- the lowest date where Col. G is
negative. The dates might not always be sorted, in case that
matters. Can someone think of how? I'm stumped.

=dman=

 

[Pete_UK]

Try this array* formula:

=MIN(IF(G1:G6<0,F1:F6,40000))

Adjust the ranges to suit your real data.

* As this is an array formula you must use CTRL-SHIFT-ENTER (CSE) to
commit it rather than the usual ENTER. If you do this correctly then
Excel will wrap curly braces { } around the formula when viewed in the
formula bar - do not type these yourself. Use CSE again if you edit
the formula.

Hope this helps.

Pete

 

[Dallman Ross]

Pete: excellent. Thank you!

=dman=

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete