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 ( and
later on the minimum, average and media) value within a given time
frame. The thing is that the time frame should be easy changeable so I
want my maximum equation to look in cell B10 for the lower limit and in
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) I
know this don’t Work I just want to show you what I’m looking for.

Thanks in advance.




--
RuneVSP

[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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[RuneVSP]

Claus Busch;1614931 Wrote:
> 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.
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

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




--
RuneVSP

[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))

[RuneVSP]

Claus Busch;1614936 Wrote:
> 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.
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

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.

Further could you tell me the concept of offset and match and why this
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 a
cell this would solve everything so i did not have to make sneaky tricks
to shutout/ include numbers.

Once Again thanks




--
RuneVSP

[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%21326
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

[RuneVSP]

RuneVSP;1614954 Wrote:
> 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.
>
> Further could you tell me the concept of offset and match and why this
> 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 a
> cell this would solve everything so i did not have to make sneaky tricks
> to shutout/ include numbers.
>
> Once Again thanks

Ive played a bit with the offset and the match functions and ive found
out they could solve all my problems as i simply took an average and
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 new
functions




--
RuneVSP