Find Max after a date
 

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

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" wrote in message
...
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

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" wrote in message
...
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

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 <atyahoo<dotcom
----
"Bruce" wrote in message
...
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