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)



"Bruce" <[email protected]> wrote in message
news:[email protected]...
> 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

[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!
==============================================


"Bruce" <[email protected]> wrote in message
news:[email protected]...
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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Bruce" <[email protected]> wrote in message
news:[email protected]...
> 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