How can i find the max value within a time range ased on two cells

[RuneVSP]

II would like to make a formula which simply find the maximum ( an
later on the minimum, average and media) value within a given tim
frame. The thing is that the time frame should be easy changeable so
want my maximum equation to look in cell B10 for the lower limit and i
B11 for the upper limit.

Example

Date Temp
1oct 10
2oct 10
3oct 12
4oct 10
5oct 14

B10=2oct
B11=4oct

Can I make an equation which simply finds: Max.Temp.(B10<date<B11)
know this don’t Work I just want to show you what I’m looking for.

Thanks in advance

 

[Claus Busch]

Hi Rune,

Am Thu, 14 Nov 2013 08:33:52 +0000 schrieb RuneVSP:

Date Temp
1oct 10
2oct 10
3oct 12
4oct 10
5oct 14

B10=2oct
B11=4oct

try:
=MAX((A2:A6>=B10)*(A2:A6<=B11)*(B2:B6))
and array-enter the formula with CTRL+Shift+Enter


Regards
Claus B.

 

[RuneVSP]

Hi Rune,

Am Thu, 14 Nov 2013 08:33:52 +0000 schrieb RuneVSP:
-

try:
=MAX((A2:A6>=B10)*(A2:A6<=B11)*(B2:B6))
and array-enter the formula with CTRL+Shift+Enter


Regards
Claus B.

Thanks you it worked out fine, for the max. However the minimum an
median cannot be identified this way. I gues it is due to the fact tha
my product is 0 in most cases.. Any idea how i can expand so i can als
find these two parameter

 

[Claus Busch]

Hi Rune,

Am Thu, 14 Nov 2013 10:29:49 +0000 schrieb RuneVSP:

Thanks you it worked out fine, for the max. However the minimum and
median cannot be identified this way. I gues it is due to the fact that
my product is 0 in most cases.. Any idea how i can expand so i can also
find these two parameters

for the minimum:
=MIN(OFFSET($A$1,MATCH(B10,$A1:$A10,0)-1,1,B11-B10+1))
for the average:
=SUMPRODUCT(--(A2:A8>=B10),--(A2:A8<=B11),B2:B8)/SUMPRODUCT(--(A2:A8>=B10),--(A2:A8<=B11))


Regards
Claus B.

 

[RuneVSP]

Hi Rune,

Am Thu, 14 Nov 2013 10:29:49 +0000 schrieb RuneVSP:
-

for the minimum:
=MIN(OFFSET($A$1,MATCH(B10,$A1:$A10,0)-1,1,B11-B10+1))
for the average:
=SUMPRODUCT(--(A2:A8>=B10),--(A2:A8<=B11),B2:B8)/SUMPRODUCT(--(A2:A8>=B10),--(A2:A8<=B11))


Regards
Claus B.

Ive tried to copypaste the minimum formula but i get an error and as
do not understand the offset nor the match function i dont know how t
correct it. Excel mark the "$A$1,MATCH" part of the formula so gues
this is where the problem is. Do you know how to correct this.

Further could you tell me the concept of offset and match and why thi
should Work for me ( im trying not to be so helpless NeXT time).

I got the average to Work on my own earlier ( = (
SUMIF($B33:$B6000;"<"&(F17+1);F33:F6000)
SUMIF($B33:$B6000;"<"&(F17);F33:F6000) )
(COUNTIF($B33:$B6000;"<"&(F17+1)) - COUNTIF($B33:$B6000;"<"&(F17)) )
but i still lack to be able to find the median.

It seems that if i could simply define the area for my formulas in
cell this would solve everything so i did not have to make sneaky trick
to shutout/ include numbers.

Once Again thank

 

[Claus Busch]

Hi Rune,

Am Fri, 15 Nov 2013 07:11:54 +0000 schrieb RuneVSP:

Ive tried to copypaste the minimum formula but i get an error and as i
do not understand the offset nor the match function i dont know how to
correct it. Excel mark the "$A$1,MATCH" part of the formula so guess
this is where the problem is. Do you know how to correct this.

I think that is a problem with formula translation or with the
seperators.
Have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Average"

Arguments of OFFSET
1. start cell
2. offset in rows
3. offset in columns
4. height in rows
5. width in columns


Regards
Claus B.

 

[RuneVSP]

Ive tried to copypaste the minimum formula but i get an error and as
do not understand the offset nor the match function i dont know how t
correct it. Excel mark the "$A$1,MATCH" part of the formula so gues
this is where the problem is. Do you know how to correct this.

Further could you tell me the concept of offset and match and why thi
should Work for me ( im trying not to be so helpless NeXT time).

I got the average to Work on my own earlier ( = (
SUMIF($B33:$B6000;"<"&(F17+1);F33:F6000)
SUMIF($B33:$B6000;"<"&(F17);F33:F6000) )
(COUNTIF($B33:$B6000;"<"&(F17+1)) - COUNTIF($B33:$B6000;"<"&(F17)) )
but i still lack to be able to find the median.

It seems that if i could simply define the area for my formulas in
cell this would solve everything so i did not have to make sneaky trick
to shutout/ include numbers.

Once Again thanks

Ive played a bit with the offset and the match functions and ive foun
out they could solve all my problems as i simply took an average an
then pushed it Down (using offset)a couple of rows (found by match)
Once Again thanks for helping me out and for teaching me these ne
function