Maximum value within a time range

[Somnifer]

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

 

[Pete_UK]

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

 

[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


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

 

[Pete_UK]

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

 

[Somnifer]

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:B1000))

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

Thanks




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

 

[Pete_UK]

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

 

[Somnifer]

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


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

 

[Pete_UK]

Well, that's good to hear.

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

Pete