Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
find date in Col A corresponding to min value in Col B | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions |