Hi,

I have a worksheet with two columns, the first (A) contains a large number
of dates and the second (B) a value corresponding to that date.

What I want to do is pick out the maximum value of column B within a certain
time range (say, only the first trimester of 2008).

All help would be greatly appreciated...

Somnifer

Suppose you put your start date of the period of interest in D1 and
the end date in E1 (as Excel will not understand "the first trimester
of 2008"), then you can use this array* formula, say in F1:

=MAX(IF((A1:A1000=D1)*(A1:A1000<=E1),B1:B1000))

I've assumed 1000 rows - adjust as required.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) instead of the usual <enter. If you do this
correctly then Excel will wrap curly braces around the formula when
viewed in the formula bar - do not type these yourself. If you need to
edit the formula, then you will need to use CSE again.

Hope this helps.

Pete

On Sep 2, 11:15*pm, "Somnifer" wrote:
Hi,

I have a worksheet with two columns, the first (A) contains a large number
of dates and the second (B) a value corresponding to that date.

What I want to do is pick out the maximum value of column B within a certain
time range (say, only the first trimester of 2008).

All help would be greatly appreciated...

Somnifer

Thank you very much! This is exactly what I wanted :-)

However, I don't quite understand the use of the multiplication in the IF
function. Could you please explain this for me please?

Thanks


"Pete_UK" wrote in message
...
Suppose you put your start date of the period of interest in D1 and
the end date in E1 (as Excel will not understand "the first trimester
of 2008"), then you can use this array* formula, say in F1:

=MAX(IF((A1:A1000=D1)*(A1:A1000<=E1),B1:B1000))

I've assumed 1000 rows - adjust as required.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) instead of the usual <enter. If you do this
correctly then Excel will wrap curly braces around the formula when
viewed in the formula bar - do not type these yourself. If you need to
edit the formula, then you will need to use CSE again.

Hope this helps.

Pete

On Sep 2, 11:15 pm, "Somnifer" wrote:
Hi,

I have a worksheet with two columns, the first (A) contains a large number
of dates and the second (B) a value corresponding to that date.

What I want to do is pick out the maximum value of column B within a
certain
time range (say, only the first trimester of 2008).

All help would be greatly appreciated...

Somnifer

You're welcome - thanks for the feedback.

The asterisk acts as an AND operation on the two arrays, so it's
basically saying if the date in column A is after the start date AND
before the end date then take the value in column B on that row (and
continue down the rows, building up another array that the MAX
function then acts upon).

Hope this helps.

Pete

On Sep 2, 11:49*pm, "Somnifer" wrote:
Thank you very much! This is exactly what I wanted :-)

However, I don't quite understand the use of the multiplication in the IF
function. Could you please explain this for me please?

Thanks

"Pete_UK" wrote in message

...
Suppose you put your start date of the period of interest in D1 and
the end date in E1 (as Excel will not understand "the first trimester
of 2008"), then you can use this array* formula, say in F1:

=MAX(IF((A1:A1000=D1)*(A1:A1000<=E1),B1:B1000))

I've assumed 1000 rows - adjust as required.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) instead of the usual <enter. If you do this
correctly then Excel will wrap curly braces around the formula when
viewed in the formula bar - do not type these yourself. If you need to
edit the formula, then you will need to use CSE again.

Hope this helps.

Pete

On Sep 2, 11:15 pm, "Somnifer" wrote:



Hi,

I have a worksheet with two columns, the first (A) contains a large number
of dates and the second (B) a value corresponding to that date.

What I want to do is pick out the maximum value of column B within a
certain
time range (say, only the first trimester of 2008).

All help would be greatly appreciated...

Somnifer- Hide quoted text -

- Show quoted text -

Thanks for the explanation!

So do I understand it correctly that it's only in combination with arrays
that the asterisk acts as an AND operation? Because my first guess to write
this would have been:

=MAX(IF(AND((A1:A1000=D1),(A1:A1000<=E1)),B1:B100 0))

And this seems to trigger a cell by cell true/false comparison for both
conditions....

Thanks




"Pete_UK" wrote in message
...
You're welcome - thanks for the feedback.

The asterisk acts as an AND operation on the two arrays, so it's
basically saying if the date in column A is after the start date AND
before the end date then take the value in column B on that row (and
continue down the rows, building up another array that the MAX
function then acts upon).

Hope this helps.

Pete

On Sep 2, 11:49 pm, "Somnifer" wrote:
Thank you very much! This is exactly what I wanted :-)

However, I don't quite understand the use of the multiplication in the IF
function. Could you please explain this for me please?

Thanks

"Pete_UK" wrote in message

...
Suppose you put your start date of the period of interest in D1 and
the end date in E1 (as Excel will not understand "the first trimester
of 2008"), then you can use this array* formula, say in F1:

=MAX(IF((A1:A1000=D1)*(A1:A1000<=E1),B1:B1000))

I've assumed 1000 rows - adjust as required.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) instead of the usual <enter. If you do this
correctly then Excel will wrap curly braces around the formula when
viewed in the formula bar - do not type these yourself. If you need to
edit the formula, then you will need to use CSE again.

Hope this helps.

Pete

On Sep 2, 11:15 pm, "Somnifer" wrote:



Hi,

I have a worksheet with two columns, the first (A) contains a large
number
of dates and the second (B) a value corresponding to that date.

What I want to do is pick out the maximum value of column B within a
certain
time range (say, only the first trimester of 2008).

All help would be greatly appreciated...

Somnifer- Hide quoted text -

- Show quoted text -

Except, AND doesn't work with arrays, so your "first guess" would not
work.

Think of TRUE being 1 and FALSE being 0, and a truth table for AND
with two variables is:

a b a AND b a * b
0 0 0 0
0 1 0 0
1 0 0 0
1 1 1 1

I'm not sure how that will look, but you can see that a*b gives the
same results as a AND b.

Hope this helps.

Pete

On Sep 3, 12:55*am, "Somnifer" wrote:
Thanks for the explanation!

So do I understand it correctly that it's only in combination with arrays
that the asterisk acts as an AND operation? Because my first guess to write
this would have been:

=MAX(IF(AND((A1:A1000=D1),(A1:A1000<=E1)),B1:B100 0))

And this seems to trigger a cell by cell true/false comparison for both
conditions....

Thanks

"Pete_UK" wrote in message

...
You're welcome - thanks for the feedback.

The asterisk acts as an AND operation on the two arrays, so it's
basically saying if the date in column A is after the start date AND
before the end date then take the value in column B on that row (and
continue down the rows, building up another array that the MAX
function then acts upon).

Hope this helps.

Pete

On Sep 2, 11:49 pm, "Somnifer" wrote:



Thank you very much! This is exactly what I wanted :-)

However, I don't quite understand the use of the multiplication in the IF
function. Could you please explain this for me please?

Thanks

"Pete_UK" wrote in message

...
Suppose you put your start date of the period of interest in D1 and
the end date in E1 (as Excel will not understand "the first trimester
of 2008"), then you can use this array* formula, say in F1:

=MAX(IF((A1:A1000=D1)*(A1:A1000<=E1),B1:B1000))

I've assumed 1000 rows - adjust as required.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) instead of the usual <enter. If you do this
correctly then Excel will wrap curly braces around the formula when
viewed in the formula bar - do not type these yourself. If you need to
edit the formula, then you will need to use CSE again.

Hope this helps.

Pete

On Sep 2, 11:15 pm, "Somnifer" wrote:

Hi,

I have a worksheet with two columns, the first (A) contains a large
number
of dates and the second (B) a value corresponding to that date.

What I want to do is pick out the maximum value of column B within a
certain
time range (say, only the first trimester of 2008).

All help would be greatly appreciated...

Somnifer- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I'm starting to make sense of it all :-)
Thanks again for explaining this to me...


"Pete_UK" wrote in message
...
Except, AND doesn't work with arrays, so your "first guess" would not
work.

Think of TRUE being 1 and FALSE being 0, and a truth table for AND
with two variables is:

a b a AND b a * b
0 0 0 0
0 1 0 0
1 0 0 0
1 1 1 1

I'm not sure how that will look, but you can see that a*b gives the
same results as a AND b.

Hope this helps.

Pete

On Sep 3, 12:55 am, "Somnifer" wrote:
Thanks for the explanation!

So do I understand it correctly that it's only in combination with arrays
that the asterisk acts as an AND operation? Because my first guess to
write
this would have been:

=MAX(IF(AND((A1:A1000=D1),(A1:A1000<=E1)),B1:B100 0))

And this seems to trigger a cell by cell true/false comparison for both
conditions....

Thanks

"Pete_UK" wrote in message

...
You're welcome - thanks for the feedback.

The asterisk acts as an AND operation on the two arrays, so it's
basically saying if the date in column A is after the start date AND
before the end date then take the value in column B on that row (and
continue down the rows, building up another array that the MAX
function then acts upon).

Hope this helps.

Pete

On Sep 2, 11:49 pm, "Somnifer" wrote:



Thank you very much! This is exactly what I wanted :-)

However, I don't quite understand the use of the multiplication in the
IF
function. Could you please explain this for me please?

Thanks

"Pete_UK" wrote in message

...
Suppose you put your start date of the period of interest in D1 and
the end date in E1 (as Excel will not understand "the first trimester
of 2008"), then you can use this array* formula, say in F1:

=MAX(IF((A1:A1000=D1)*(A1:A1000<=E1),B1:B1000))

I've assumed 1000 rows - adjust as required.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) instead of the usual <enter. If you do this
correctly then Excel will wrap curly braces around the formula when
viewed in the formula bar - do not type these yourself. If you need to
edit the formula, then you will need to use CSE again.

Hope this helps.

Pete

On Sep 2, 11:15 pm, "Somnifer" wrote:

Hi,

I have a worksheet with two columns, the first (A) contains a large
number
of dates and the second (B) a value corresponding to that date.

What I want to do is pick out the maximum value of column B within a
certain
time range (say, only the first trimester of 2008).

All help would be greatly appreciated...

Somnifer- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Well, that's good to hear.

Come back any time you have any other queries - there's always someone
around to offer help.

Pete

On Sep 3, 3:00*pm, "Somnifer" wrote:
I'm starting to make sense of it all :-)
Thanks again for explaining this to me...

"Pete_UK" wrote in message

...
Except, AND doesn't work with arrays, so your "first guess" would not
work.

Think of TRUE being 1 and FALSE being 0, and a truth table for AND
with two variables is:

a * * *b * * *a AND b * * * * * * a * b
0 * * *0 * * * * * 0 * * * * * * * * * * 0
0 * * *1 * * * * * 0 * * * * * * * * * * 0
1 * * *0 * * * * * 0 * * * * * * * * * * 0
1 * * *1 * * * * * 1 * * * * * * * * * * 1

I'm not sure how that will look, but you can see that a*b gives the
same results as a AND b.

Hope this helps.

Pete

On Sep 3, 12:55 am, "Somnifer" wrote:



Thanks for the explanation!

So do I understand it correctly that it's only in combination with arrays
that the asterisk acts as an AND operation? Because my first guess to
write
this would have been:

=MAX(IF(AND((A1:A1000=D1),(A1:A1000<=E1)),B1:B100 0))

And this seems to trigger a cell by cell true/false comparison for both
conditions....

Thanks

"Pete_UK" wrote in message

....
You're welcome - thanks for the feedback.

The asterisk acts as an AND operation on the two arrays, so it's
basically saying if the date in column A is after the start date AND
before the end date then take the value in column B on that row (and
continue down the rows, building up another array that the MAX
function then acts upon).

Hope this helps.

Pete

On Sep 2, 11:49 pm, "Somnifer" wrote:

Thank you very much! This is exactly what I wanted :-)

However, I don't quite understand the use of the multiplication in the
IF
function. Could you please explain this for me please?

Thanks

"Pete_UK" wrote in message

....
Suppose you put your start date of the period of interest in D1 and
the end date in E1 (as Excel will not understand "the first trimester
of 2008"), then you can use this array* formula, say in F1:

=MAX(IF((A1:A1000=D1)*(A1:A1000<=E1),B1:B1000))

I've assumed 1000 rows - adjust as required.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) instead of the usual <enter. If you do this
correctly then Excel will wrap curly braces around the formula when
viewed in the formula bar - do not type these yourself. If you need to
edit the formula, then you will need to use CSE again.

Hope this helps.

Pete

On Sep 2, 11:15 pm, "Somnifer" wrote:

Hi,

I have a worksheet with two columns, the first (A) contains a large
number
of dates and the second (B) a value corresponding to that date.

What I want to do is pick out the maximum value of column B within a
certain
time range (say, only the first trimester of 2008).

All help would be greatly appreciated...

Somnifer- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -