Calculate max value in specific range

[Guest]

Hi all,


I've got a table with specific date values (start & end dates) like
this:

3/23/00 Start
3/26/00 End

etc.

note: the length of days will vary from time tot time.

--

I also have a table with all date values and corresponding numbers,
like this:

Column:
A B
Row:
1 3/23/00 1101.16
2 3/24/00 1106.16
3 3/25/00 1143.07 (=MAX)
4 3/26/00 1137.54

--

How can I calculate the max value from all those corresponding numbers
between a Start- & End date? (1143.07 in this case)
I've already tried the Vlookup and Max function but that only refers to
1 value (as far as I know)

Any suggestions? Thanks in advance!

- Jim

 

[Don Guillett]

This is a ARRAY formula which must be entered using ctrl+shift+enter
modify to suit your criteria
=MAX(IF(B9:B111>1,B9:B111<33),C9:C111)

 

[RagDyeR]

With start date entered in say D1,
And end date entered in D2
And data list in A1 to B50,

Try this *array* formula:

=MAX(IF((A1:A50>=D1)*(A1:A50<=D2),B1:B50))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi all,


I've got a table with specific date values (start & end dates) like
this:

3/23/00 Start
3/26/00 End

etc.

note: the length of days will vary from time tot time.

--

I also have a table with all date values and corresponding numbers,
like this:

Column:
A B
Row:
1 3/23/00 1101.16
2 3/24/00 1106.16
3 3/25/00 1143.07 (=MAX)
4 3/26/00 1137.54

--

How can I calculate the max value from all those corresponding numbers
between a Start- & End date? (1143.07 in this case)
I've already tried the Vlookup and Max function but that only refers to
1 value (as far as I know)

Any suggestions? Thanks in advance!

- Jim

 

[Guest]

Hello Don,

I presume that your range B9:B111 should refer to my dates (range
A1:A4) and that your range C9:C111 should refer to my corresponding
numbers (range B1:B4)? Unfortunately this won't work because the number
of rows vary from time to time. I've tried '=max(if(A:A>='date start
cell',A:A<='date end cell',D) but this didn't solve my answer.

- Jim

 

[Guest]

The problem is that the lenth of the range (rows with corresponding
numbers is unknow) Using a fixed range like B9:B111(by Don) or
A1:A50(by RD) doesn't give the required referende to calculate that
specific max value.

 

[RagDyeR]

The "range" of the specific max value is determined by the dates you enter
in D1 and D2.

The range of the dates to be polled can be made as large as you anticipate
that it can *ever* attain.

You could use A1:B1000 ... A1: B10000 ... couldn't you?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

The problem is that the lenth of the range (rows with corresponding
numbers is unknow) Using a fixed range like B9:B111(by Don) or
A1:A50(by RD) doesn't give the required referende to calculate that
specific max value.

 

[Don Guillett]

You could use a defined name range or just put in a number larger than
possible
insert>name>define>rangea
in the refers to box
=offset($a$1,0,0,counta(A:A),1)
hen same for rangeb
=MAX(IF(rangea>1,rangea<33),rangeb)