Max Value, Sum Product, Between Date Range
 

I have used the following formula to return the max value for ID1, ID2, ID3
etc for the whole data set.

=SUMPRODUCT(MAX((X2:X5000=A2)*(Y2:Y5000))

Where
Col A = ID1, ID2, ID3 etc in my new table.

Imported data (X,Y,Z)
Col X = ID1, ID2, ID3 etc (multiple occurances/values for each ID)
Col Y = Value
Col Z = Date (Format dd/mm/yyyy)

I would like to be able to extract the MAX value for ID1, ID2 etc between
date periods i.e. 1/10/1995 to 1/12/1995 but am unsure how to do this.

Any help appreciated
Many Thanks
Chris

Max Value, Sum Product, Between Date Range
 

The below formula will pick the max value from ColB ....for ID1 (cell F1)
between the dates mentioned in D1 and E1

=MAX(IF((A1:A100=F1)*(C1:C100=D1)*(C1:C100<=E1),B 1:B100))

Col A Col B Col C Col D Col E Col F
ID1 1 8/1/2009 8/3/2009 8/3/2009 ID1
ID1 2 8/2/2009
ID1 20 8/3/2009
ID2 1 8/4/2009
ID2 2 8/5/2009
ID2 3 8/6/2009


If this post helps click Yes
---------------
Jacob Skaria


"Chris26" wrote:

I have used the following formula to return the max value for ID1, ID2, ID3
etc for the whole data set.

=SUMPRODUCT(MAX((X2:X5000=A2)*(Y2:Y5000))

Where
Col A = ID1, ID2, ID3 etc in my new table.

Imported data (X,Y,Z)
Col X = ID1, ID2, ID3 etc (multiple occurances/values for each ID)
Col Y = Value
Col Z = Date (Format dd/mm/yyyy)

I would like to be able to extract the MAX value for ID1, ID2 etc between
date periods i.e. 1/10/1995 to 1/12/1995 but am unsure how to do this.

Any help appreciated
Many Thanks
Chris

Max Value, Sum Product, Between Date Range
 

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=MAX(IF((A1:A100=F1)*(C1:C100=D1)*(C1:C100<=E1),B 1:B100))


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

The below formula will pick the max value from ColB ....for ID1 (cell F1)
between the dates mentioned in D1 and E1

=MAX(IF((A1:A100=F1)*(C1:C100=D1)*(C1:C100<=E1),B 1:B100))

Col A Col B Col C Col D Col E Col F
ID1 1 8/1/2009 8/3/2009 8/3/2009 ID1
ID1 2 8/2/2009
ID1 20 8/3/2009
ID2 1 8/4/2009
ID2 2 8/5/2009
ID2 3 8/6/2009


If this post helps click Yes
---------------
Jacob Skaria


"Chris26" wrote:

I have used the following formula to return the max value for ID1, ID2, ID3
etc for the whole data set.

=SUMPRODUCT(MAX((X2:X5000=A2)*(Y2:Y5000))

Where
Col A = ID1, ID2, ID3 etc in my new table.

Imported data (X,Y,Z)
Col X = ID1, ID2, ID3 etc (multiple occurances/values for each ID)
Col Y = Value
Col Z = Date (Format dd/mm/yyyy)

I would like to be able to extract the MAX value for ID1, ID2 etc between
date periods i.e. 1/10/1995 to 1/12/1995 but am unsure how to do this.

Any help appreciated
Many Thanks
Chris

Max Value, Sum Product, Between Date Range
 

=SUMPRODUCT(MAX((X2:X5000=A2)*(Y2:Y5000)*(Z2:Z5000 =B2)*(Z2:Z5000<=C2)))

assumes B2 contains start date, C2 contains end date.
You may want to adjust the '=' and '<=' to just '' and '<' depending
on whether you want the dates in B2 and C2 to be included or excluded
from the result.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121906

Max Value, Sum Product, Between Date Range
 

Thanks Jacob and p45cal
Much Appreciated

"p45cal" wrote:


=SUMPRODUCT(MAX((X2:X5000=A2)*(Y2:Y5000)*(Z2:Z5000 =B2)*(Z2:Z5000<=C2)))

assumes B2 contains start date, C2 contains end date.
You may want to adjust the '=' and '<=' to just '' and '<' depending
on whether you want the dates in B2 and C2 to be included or excluded
from the result.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121906