Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max if between 2 dates?
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Charting data against dates where dates are not at fixed intervals | Charts and Charting in Excel |