Find Max after a date

[Bruce]

On my worksheet column I contains dates, E contains my values and D9 is my
startdate.

=MAX(IF(($I:$I>=Shares!$D$9),$E:$E))

What I want to do is find the Max E value where the date (I) is greater than
D9.

I havnt got this quite right yet.

Bruce

 

[Peo Sjoblom]

You can't use the whole column range in an array formula, try

=MAX(IF($I1:I$500>=Shares!$D$9,$E1:$E500))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[RagDyer]

Try this:

=SUMPRODUCT(MAX((I1:I100>=Shares!D9)*E1:E100))

However, with SumProduct, you can't use entire columns.
If necessary you could use 1:65000.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


On my worksheet column I contains dates, E contains my values and D9 is my
startdate.

=MAX(IF(($I:$I>=Shares!$D$9),$E:$E))

What I want to do is find the Max E value where the date (I) is greater than
D9.

I havnt got this quite right yet.

Bruce

 

[Max]

Try something like:

=MAX(IF($I1:$I100>=Shares!$D$9,$E1:$E100))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of pressing ENTER

Adapt the ranges to suit
(but you can't use entire col refs)