|
Find Max Date and the value
I was wondering if this is possible in Excel 2007.
I have six columns of data,starting in columns AB labeled as follows: Note column placements may change as data is added. Letter Sent#1 - this has a "Yes" or "" Value Letter Sent Date - Date Value Letter Sent #2 - this has a "Yes' or "" Value Letter Sent Date - Date Value Letter Sent # 3 - this has a "Yes" or "" Value Letter Sent Date - Date Value I need to look at each date value and find the most recent and then pull in which it was; Letter Sent #1, or #2 or #3? Also if I need to change the format in the future for the Letter Sent #1, #2 and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula provided need updated or would it not matter since it is loking at the maximum date first? |
Find Max Date and the value
Try
=MAX(AB2:AG2) (and format the formula cell to excel date format...Right click FormatCellsselect Date and a format) If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: I was wondering if this is possible in Excel 2007. I have six columns of data,starting in columns AB labeled as follows: Note column placements may change as data is added. Letter Sent#1 - this has a "Yes" or "" Value Letter Sent Date - Date Value Letter Sent #2 - this has a "Yes' or "" Value Letter Sent Date - Date Value Letter Sent # 3 - this has a "Yes" or "" Value Letter Sent Date - Date Value I need to look at each date value and find the most recent and then pull in which it was; Letter Sent #1, or #2 or #3? Also if I need to change the format in the future for the Letter Sent #1, #2 and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula provided need updated or would it not matter since it is loking at the maximum date first? |
Find Max Date and the value
Hi Jacob, You are all over th epalce in here : )
Thank you for all your help. How does the max function you provided pull in which Letter had the maximum date too ? "Jacob Skaria" wrote: Try =MAX(AB2:AG2) (and format the formula cell to excel date format...Right click FormatCellsselect Date and a format) If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: I was wondering if this is possible in Excel 2007. I have six columns of data,starting in columns AB labeled as follows: Note column placements may change as data is added. Letter Sent#1 - this has a "Yes" or "" Value Letter Sent Date - Date Value Letter Sent #2 - this has a "Yes' or "" Value Letter Sent Date - Date Value Letter Sent # 3 - this has a "Yes" or "" Value Letter Sent Date - Date Value I need to look at each date value and find the most recent and then pull in which it was; Letter Sent #1, or #2 or #3? Also if I need to change the format in the future for the Letter Sent #1, #2 and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula provided need updated or would it not matter since it is loking at the maximum date first? |
Find Max Date and the value
Probably I have misunderstood your initial post; try the below which returns
the header in AB1:AG1 for the max date in row 2 AB2:AG2 =INDEX(AB1:AG1,MATCH(MAX(AB2:AG2),AB2:AG2,0)-1) If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: Hi Jacob, You are all over th epalce in here : ) Thank you for all your help. How does the max function you provided pull in which Letter had the maximum date too ? "Jacob Skaria" wrote: Try =MAX(AB2:AG2) (and format the formula cell to excel date format...Right click FormatCellsselect Date and a format) If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: I was wondering if this is possible in Excel 2007. I have six columns of data,starting in columns AB labeled as follows: Note column placements may change as data is added. Letter Sent#1 - this has a "Yes" or "" Value Letter Sent Date - Date Value Letter Sent #2 - this has a "Yes' or "" Value Letter Sent Date - Date Value Letter Sent # 3 - this has a "Yes" or "" Value Letter Sent Date - Date Value I need to look at each date value and find the most recent and then pull in which it was; Letter Sent #1, or #2 or #3? Also if I need to change the format in the future for the Letter Sent #1, #2 and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula provided need updated or would it not matter since it is loking at the maximum date first? |
Find Max Date and the value
These will be nonadjacent cells, how do I indicate this in the formula, I
thought I had mention but may not have, sorry. "Jen_T" wrote: I was wondering if this is possible in Excel 2007. I have six columns of data,starting in columns AB labeled as follows: Note column placements may change as data is added. Letter Sent#1 - this has a "Yes" or "" Value Letter Sent Date - Date Value Letter Sent #2 - this has a "Yes' or "" Value Letter Sent Date - Date Value Letter Sent # 3 - this has a "Yes" or "" Value Letter Sent Date - Date Value I need to look at each date value and find the most recent and then pull in which it was; Letter Sent #1, or #2 or #3? Also if I need to change the format in the future for the Letter Sent #1, #2 and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula provided need updated or would it not matter since it is loking at the maximum date first? |
Find Max Date and the value
ColAB ColAC ColAD ColAE ColAF ColAG
LS#1 Date1 LS#2 Date2 LS#3 Date3 Y 10/4/2009 Y 10/6/2009 Y 10/8/2009 =INDEX($AB$1:$AG$1,MATCH(MAX(AB2:AG2),AB2:AG2,0)-1) I tried the formula with data as above...which is what I understood from your initial post. In the aboave example the formula will return the header "LS$3" since the date in Col AG is the highest... If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: These will be nonadjacent cells, how do I indicate this in the formula, I thought I had mention but may not have, sorry. "Jen_T" wrote: I was wondering if this is possible in Excel 2007. I have six columns of data,starting in columns AB labeled as follows: Note column placements may change as data is added. Letter Sent#1 - this has a "Yes" or "" Value Letter Sent Date - Date Value Letter Sent #2 - this has a "Yes' or "" Value Letter Sent Date - Date Value Letter Sent # 3 - this has a "Yes" or "" Value Letter Sent Date - Date Value I need to look at each date value and find the most recent and then pull in which it was; Letter Sent #1, or #2 or #3? Also if I need to change the format in the future for the Letter Sent #1, #2 and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula provided need updated or would it not matter since it is loking at the maximum date first? |
Find Max Date and the value
I mistated, these will now be non adjacent cells. I apologize, so I will have
data lets, say in AD:AC, AE:AF, AH:AI, AK:AL "Jacob Skaria" wrote: ColAB ColAC ColAD ColAE ColAF ColAG LS#1 Date1 LS#2 Date2 LS#3 Date3 Y 10/4/2009 Y 10/6/2009 Y 10/8/2009 =INDEX($AB$1:$AG$1,MATCH(MAX(AB2:AG2),AB2:AG2,0)-1) I tried the formula with data as above...which is what I understood from your initial post. In the aboave example the formula will return the header "LS$3" since the date in Col AG is the highest... If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: These will be nonadjacent cells, how do I indicate this in the formula, I thought I had mention but may not have, sorry. "Jen_T" wrote: I was wondering if this is possible in Excel 2007. I have six columns of data,starting in columns AB labeled as follows: Note column placements may change as data is added. Letter Sent#1 - this has a "Yes" or "" Value Letter Sent Date - Date Value Letter Sent #2 - this has a "Yes' or "" Value Letter Sent Date - Date Value Letter Sent # 3 - this has a "Yes" or "" Value Letter Sent Date - Date Value I need to look at each date value and find the most recent and then pull in which it was; Letter Sent #1, or #2 or #3? Also if I need to change the format in the future for the Letter Sent #1, #2 and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula provided need updated or would it not matter since it is loking at the maximum date first? |
Find Max Date and the value
Try the below. Please note that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX($AC$1:$AL$1,MATCH(MAX(IF($AC$1:$AL$1="Lette r Sent Date",$AC$2:$AL$2)),$AC$2:$AL$2,0)-1) If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: I mistated, these will now be non adjacent cells. I apologize, so I will have data lets, say in AD:AC, AE:AF, AH:AI, AK:AL "Jacob Skaria" wrote: ColAB ColAC ColAD ColAE ColAF ColAG LS#1 Date1 LS#2 Date2 LS#3 Date3 Y 10/4/2009 Y 10/6/2009 Y 10/8/2009 =INDEX($AB$1:$AG$1,MATCH(MAX(AB2:AG2),AB2:AG2,0)-1) I tried the formula with data as above...which is what I understood from your initial post. In the aboave example the formula will return the header "LS$3" since the date in Col AG is the highest... If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: These will be nonadjacent cells, how do I indicate this in the formula, I thought I had mention but may not have, sorry. "Jen_T" wrote: I was wondering if this is possible in Excel 2007. I have six columns of data,starting in columns AB labeled as follows: Note column placements may change as data is added. Letter Sent#1 - this has a "Yes" or "" Value Letter Sent Date - Date Value Letter Sent #2 - this has a "Yes' or "" Value Letter Sent Date - Date Value Letter Sent # 3 - this has a "Yes" or "" Value Letter Sent Date - Date Value I need to look at each date value and find the most recent and then pull in which it was; Letter Sent #1, or #2 or #3? Also if I need to change the format in the future for the Letter Sent #1, #2 and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula provided need updated or would it not matter since it is loking at the maximum date first? |
Find Max Date and the value
The date field starts with 'Letter Sent Date' then try the below version
which search for this keywords and look for the dates in these columns alone... '(array entered) =INDEX($AC$1:$AL$1,MATCH(MAX(IF(ISNUMBER(SEARCH("L etter Sent Date",$AC$1:$AL$1)),$AC$2:$AL$2)),$AC$2:$AL$2,0)-1) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX($AC$1:$AL$1,MATCH(MAX(IF($AC$1:$AL$1="Lette r Sent Date",$AC$2:$AL$2)),$AC$2:$AL$2,0)-1) If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: I mistated, these will now be non adjacent cells. I apologize, so I will have data lets, say in AD:AC, AE:AF, AH:AI, AK:AL "Jacob Skaria" wrote: ColAB ColAC ColAD ColAE ColAF ColAG LS#1 Date1 LS#2 Date2 LS#3 Date3 Y 10/4/2009 Y 10/6/2009 Y 10/8/2009 =INDEX($AB$1:$AG$1,MATCH(MAX(AB2:AG2),AB2:AG2,0)-1) I tried the formula with data as above...which is what I understood from your initial post. In the aboave example the formula will return the header "LS$3" since the date in Col AG is the highest... If this post helps click Yes --------------- Jacob Skaria "Jen_T" wrote: These will be nonadjacent cells, how do I indicate this in the formula, I thought I had mention but may not have, sorry. "Jen_T" wrote: I was wondering if this is possible in Excel 2007. I have six columns of data,starting in columns AB labeled as follows: Note column placements may change as data is added. Letter Sent#1 - this has a "Yes" or "" Value Letter Sent Date - Date Value Letter Sent #2 - this has a "Yes' or "" Value Letter Sent Date - Date Value Letter Sent # 3 - this has a "Yes" or "" Value Letter Sent Date - Date Value I need to look at each date value and find the most recent and then pull in which it was; Letter Sent #1, or #2 or #3? Also if I need to change the format in the future for the Letter Sent #1, #2 and #3 to values such as "Yes' or "No" instead "Yes" or "". Would the formula provided need updated or would it not matter since it is loking at the maximum date first? |
| All times are GMT +1. The time now is 07:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com