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.

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

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

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=9378A...121822A3%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


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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