Max if between 2 dates?
 

I have dates in column A & B. They correspond to dates in column A of
sheet2... In column C (of sheet1) I would like to find the max value found on
Sheet2 in column B corresponding/between the two dates of cloumn A&B on
sheet1. Is that make sense?

Thanks for your cunning thoughts!

Max if between 2 dates?
 

=MAX(INDEX((Sheet2!$A$2:$A$10=A2)*(Sheet2!$A$2:$A $10<=B2)*(Sheet2!$B$2:$B$10),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John" wrote in message
...
I have dates in column A & B. They correspond to dates in column A of
sheet2... In column C (of sheet1) I would like to find the max value found
on
Sheet2 in column B corresponding/between the two dates of cloumn A&B on
sheet1. Is that make sense?

Thanks for your cunning thoughts!

Max if between 2 dates?
 

=MAX(INDEX('data FI'!$T$3:$X$1000,('data FI'!$T$3:$T$1000=W2)*('data
FI'!$T$3:$T$1000<=T2)*('data FI'!$V$3:$V$1000),0))

AND

=MAX(INDEX(('data FI'!$T$3:$T$1000=W2)*('data FI'!$T$3:$T$1000<=T2)*('data
FI'!$V$3:$V$1000),0))

For my sheet is not working

I will keep playing around with it, but any thoughts?

"Bob Phillips" wrote:

=MAX(INDEX((Sheet2!$A$2:$A$10=A2)*(Sheet2!$A$2:$A $10<=B2)*(Sheet2!$B$2:$B$10),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John" wrote in message
...
I have dates in column A & B. They correspond to dates in column A of
sheet2... In column C (of sheet1) I would like to find the max value found
on
Sheet2 in column B corresponding/between the two dates of cloumn A&B on
sheet1. Is that make sense?

Thanks for your cunning thoughts!

Max if between 2 dates?
 

And I tried =MAX(OFFSET(INDEX(('data FI'!$T$3:$T$1000=W2)*('data
FI'!$T$3:$T$1000<=T2),1),0,2)) which doesn't work either :)

"Bob Phillips" wrote:

=MAX(INDEX((Sheet2!$A$2:$A$10=A2)*(Sheet2!$A$2:$A $10<=B2)*(Sheet2!$B$2:$B$10),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John" wrote in message
...
I have dates in column A & B. They correspond to dates in column A of
sheet2... In column C (of sheet1) I would like to find the max value found
on
Sheet2 in column B corresponding/between the two dates of cloumn A&B on
sheet1. Is that make sense?

Thanks for your cunning thoughts!

Max if between 2 dates?
 

You can't use two columns as per your first example, and you cannot add a
third index argument as per your third. Your second one works fine in my
tests though.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John" wrote in message
...
And I tried =MAX(OFFSET(INDEX(('data FI'!$T$3:$T$1000=W2)*('data
FI'!$T$3:$T$1000<=T2),1),0,2)) which doesn't work either :)

"Bob Phillips" wrote:

=MAX(INDEX((Sheet2!$A$2:$A$10=A2)*(Sheet2!$A$2:$A $10<=B2)*(Sheet2!$B$2:$B$10),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"John" wrote in message
...
I have dates in column A & B. They correspond to dates in column A of
sheet2... In column C (of sheet1) I would like to find the max value
found
on
Sheet2 in column B corresponding/between the two dates of cloumn A&B on
sheet1. Is that make sense?

Thanks for your cunning thoughts!

Max if between 2 dates?
 

SO =MAX(INDEX(('data FI'!$T$3:$T$1000=W16)*('data
FI'!$T$3:$T$1000<=T16)*('data FI'!$V$3:$V$1000),0))

so this Finds the MAX in column V on the sheet 'data FI' between the dates
given in W16 and T16???.... so the index may find 5 dates between the 2
values and therfore 5 values in column V and takes the MAX of those 5
values???? ... it doesn't work for me. Can you send your sheet to me?



"Bob Phillips" wrote:

You can't use two columns as per your first example, and you cannot add a
third index argument as per your third. Your second one works fine in my
tests though.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John" wrote in message
...
And I tried =MAX(OFFSET(INDEX(('data FI'!$T$3:$T$1000=W2)*('data
FI'!$T$3:$T$1000<=T2),1),0,2)) which doesn't work either :)

"Bob Phillips" wrote:

=MAX(INDEX((Sheet2!$A$2:$A$10=A2)*(Sheet2!$A$2:$A $10<=B2)*(Sheet2!$B$2:$B$10),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"John" wrote in message
...
I have dates in column A & B. They correspond to dates in column A of
sheet2... In column C (of sheet1) I would like to find the max value
found
on
Sheet2 in column B corresponding/between the two dates of cloumn A&B on
sheet1. Is that make sense?

Thanks for your cunning thoughts!

Max if between 2 dates?
 

Where, you are using a forum.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John" wrote in message
...
SO =MAX(INDEX(('data FI'!$T$3:$T$1000=W16)*('data
FI'!$T$3:$T$1000<=T16)*('data FI'!$V$3:$V$1000),0))

so this Finds the MAX in column V on the sheet 'data FI' between the dates
given in W16 and T16???.... so the index may find 5 dates between the 2
values and therfore 5 values in column V and takes the MAX of those 5
values???? ... it doesn't work for me. Can you send your sheet to me?



"Bob Phillips" wrote:

You can't use two columns as per your first example, and you cannot add a
third index argument as per your third. Your second one works fine in my
tests though.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"John" wrote in message
...
And I tried =MAX(OFFSET(INDEX(('data FI'!$T$3:$T$1000=W2)*('data
FI'!$T$3:$T$1000<=T2),1),0,2)) which doesn't work either :)

"Bob Phillips" wrote:

=MAX(INDEX((Sheet2!$A$2:$A$10=A2)*(Sheet2!$A$2:$A $10<=B2)*(Sheet2!$B$2:$B$10),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"John" wrote in message
...
I have dates in column A & B. They correspond to dates in column A
of
sheet2... In column C (of sheet1) I would like to find the max value
found
on
Sheet2 in column B corresponding/between the two dates of cloumn A&B
on
sheet1. Is that make sense?

Thanks for your cunning thoughts!

Max if between 2 dates?
 

Max if between 2 dates?
 

I had a cell with text in my range... sorry for the confusion and thanks for
all the help

"Bob Phillips" wrote:

Where, you are using a forum.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John" wrote in message
...
SO =MAX(INDEX(('data FI'!$T$3:$T$1000=W16)*('data
FI'!$T$3:$T$1000<=T16)*('data FI'!$V$3:$V$1000),0))

so this Finds the MAX in column V on the sheet 'data FI' between the dates
given in W16 and T16???.... so the index may find 5 dates between the 2
values and therfore 5 values in column V and takes the MAX of those 5
values???? ... it doesn't work for me. Can you send your sheet to me?



"Bob Phillips" wrote:

You can't use two columns as per your first example, and you cannot add a
third index argument as per your third. Your second one works fine in my
tests though.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"John" wrote in message
...
And I tried =MAX(OFFSET(INDEX(('data FI'!$T$3:$T$1000=W2)*('data
FI'!$T$3:$T$1000<=T2),1),0,2)) which doesn't work either :)

"Bob Phillips" wrote:

=MAX(INDEX((Sheet2!$A$2:$A$10=A2)*(Sheet2!$A$2:$A $10<=B2)*(Sheet2!$B$2:$B$10),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"John" wrote in message
...
I have dates in column A & B. They correspond to dates in column A
of
sheet2... In column C (of sheet1) I would like to find the max value
found
on
Sheet2 in column B corresponding/between the two dates of cloumn A&B
on
sheet1. Is that make sense?

Thanks for your cunning thoughts!