|
Get highest value and adjacent cell value
Formula help:
Would it be possible to get a highest of some cells and but instead of retaining the value get the value of adjacent cell. For example, I have numbers in cells C1 and C2 I also have Dates in Cell B1 and B2 each date corresponds to a score value. I like get a highest value =MAX(C1:C2) and place it in C4 also like to get the adjacent date to the highest value and place it in B3. Can this be done? A B C 1 3/1/2006 5 2 3/5/2006 4 3 4 Date(Adjacent to highest Formula(highest) |
Get highest value and adjacent cell value
Hi
In C4 =MAX(C1:C2) in B4 =INDEX(B1:B2,MATCH(C4,C1:C2,0)) -- Regards Roger Govier "Ardy" wrote in message oups.com... Formula help: Would it be possible to get a highest of some cells and but instead of retaining the value get the value of adjacent cell. For example, I have numbers in cells C1 and C2 I also have Dates in Cell B1 and B2 each date corresponds to a score value. I like get a highest value =MAX(C1:C2) and place it in C4 also like to get the adjacent date to the highest value and place it in B3. Can this be done? A B C 1 3/1/2006 5 2 3/5/2006 4 3 4 Date(Adjacent to highest Formula(highest) |
Get highest value and adjacent cell value
On Jul 6, 2:40 pm, "Roger Govier"
wrote: Hi In C4 =MAX(C1:C2) in B4 =INDEX(B1:B2,MATCH(C4,C1:C2,0)) -- Regards Roger Govier "Ardy" wrote in message oups.com... Formula help: Would it be possible to get a highest of some cells and but instead of retaining the value get the value of adjacent cell. For example, I have numbers in cells C1 and C2 I also have Dates in Cell B1 and B2 each date corresponds to a score value. I like get a highest value =MAX(C1:C2) and place it in C4 also like to get the adjacent date to the highest value and place it in B3. Can this be done? A B C 1 3/1/2006 5 2 3/5/2006 4 3 4 Date(Adjacent to highest Formula(highest) Roger: Nothing happens. As if the formula is a string of text. I have modify the cells to reflect the original spreadsheet =INDEX(B7:B10,MATCH(F8,C7:C10,0)) B7:B10 Range of dates C7:C10 Range of Scores F8 The MAX(C7:C10) OH i got a question what is 0 Ardy |
Get highest value and adjacent cell value
"Ardy" wrote
.. Nothing happens. As if the formula is a string of text. =INDEX(B7:B10,MATCH(F8,C7:C10,0)) Your adaptation's ok and should work. From your comment: "As if the formula is a string of text.", one possibility is that the formula cell was earlier pre-formatted as Text (unknown to you of course). Just reformat the formula cell to either general or number (via Format Cells Number tab), then re-enter the formula by clicking inside the formula bar and pressing ENTER. Note that you need to re-enter the formula, re-formatting alone doesn't trigger it. .. OH i got a question what is 0 I suppose you are referring to the zero "0" (match type) within: MATCH(F8,C7:C10,0) A zero (match type) means to find an exact match for the lookup value F8 within the ref range C7:C10 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Get highest value and adjacent cell value
On Jul 6, 8:09 pm, "Max" wrote:
"Ardy" wrote .. Nothing happens. As if the formula is a string of text. =INDEX(B7:B10,MATCH(F8,C7:C10,0)) Your adaptation's ok and should work. From your comment: "As if the formula is a string of text.", one possibility is that the formula cell was earlier pre-formatted as Text (unknown to you of course). Just reformat the formula cell to either general or number (via Format Cells Number tab), then re-enter the formula by clicking inside the formula bar and pressing ENTER. Note that you need to re-enter the formula, re-formatting alone doesn't trigger it. .. OH i got a question what is 0 I suppose you are referring to the zero "0" (match type) within: MATCH(F8,C7:C10,0) A zero (match type) means to find an exact match for the lookup value F8 within the ref range C7:C10 -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- Max: Thanks |
Get highest value and adjacent cell value
welcome. trust you got it sorted out ..
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ardy" wrote Max: Thanks |
Get highest value and adjacent cell value
On Jul 9, 6:46 am, "Max" wrote:
welcome. trust you got it sorted out .. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- "Ardy" wrote Max: Thanks OK Max: I have another question related to the same thing. I modified the formula to fit my application =INDEX(C5:C8,MATCH(G6,D5:D8,0)) Works grate with one exception, The C5:C8 are date range if null you will see 1/0/1900 I use to have this formula with help from this group to empty the cell if there was no date: =IF(OR(ISERROR(MAX(C5:C8)),MAX(C5:C8)=0),"",MAX(C5 :C8)) How would I be able to melt your formula to this so it dose both. I have tried couple of ways to just see what would happen but have been unsuccessful. |
Get highest value and adjacent cell value
On Jul 6, 2:40 pm, "Roger Govier"
wrote: Hi In C4 =MAX(C1:C2) in B4 =INDEX(B1:B2,MATCH(C4,C1:C2,0)) -- Regards Roger Govier "Ardy" wrote in message oups.com... Formula help: Would it be possible to get a highest of some cells and but instead of retaining the value get the value of adjacent cell. For example, I have numbers in cells C1 and C2 I also have Dates in Cell B1 and B2 each date corresponds to a score value. I like get a highest value =MAX(C1:C2) and place it in C4 also like to get the adjacent date to the highest value and place it in B3. Can this be done? A B C 1 3/1/2006 5 2 3/5/2006 4 3 4 Date(Adjacent to highest Formula(highest) Roger: I have another question related to the same thing. I modified the formula to fit my application =INDEX(C5:C8,MATCH(G6,D5:D8,0)) Works grate with one exception, The C5:C8 are date range if null you will see 1/0/1900 I use to have this formula with help from this group to empty the cell if there was no date: =IF(OR(ISERROR(MAX(C5:C8)),MAX(C5:C8)=0),"",MAX(C5 :C8)) How would I be able to melt your formula to this so it dose both. I have tried couple of ways to just see what would happen but have been unsuccessful. |
Get highest value and adjacent cell value
=INDEX(C5:C8,MATCH(G6,D5:D8,0))
My interp / guess is you just want the above to return blank: "" if the result is zero. If so: =IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,"",INDEX(C5:C 8,MATCH(G6,D5:D8,0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ardy" wrote OK Max: I have another question related to the same thing. I modified the formula to fit my application =INDEX(C5:C8,MATCH(G6,D5:D8,0)) Works grate with one exception, The C5:C8 are date range if null you will see 1/0/1900 I use to have this formula with help from this group to empty the cell if there was no date: =IF(OR(ISERROR(MAX(C5:C8)),MAX(C5:C8)=0),"",MAX(C5 :C8)) How would I be able to melt your formula to this so it dose both. I have tried couple of ways to just see what would happen but have been unsuccessful. |
Get highest value and adjacent cell value
Hi
I'm not sure what you are after here. If you are saying you want a null returned, rather than Date 0 (00/01/1900) then =IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,"",INDEX(C5:C 8,MATCH(G6,D5:D8,0))) Alternatively, if there is no data in column C opposite the largest value in column D and you wanted the date next to the second highest value, then you could use. =IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,INDEX(C5:C8,M ATCH(LARGE(D5:D8,2),D5:D8,0)),INDEX(C5:C8,MATCH(G6 ,D5:D8,0))) -- Regards Roger Govier "Ardy" wrote in message oups.com... On Jul 6, 2:40 pm, "Roger Govier" wrote: Hi In C4 =MAX(C1:C2) in B4 =INDEX(B1:B2,MATCH(C4,C1:C2,0)) -- Regards Roger Govier "Ardy" wrote in message oups.com... Formula help: Would it be possible to get a highest of some cells and but instead of retaining the value get the value of adjacent cell. For example, I have numbers in cells C1 and C2 I also have Dates in Cell B1 and B2 each date corresponds to a score value. I like get a highest value =MAX(C1:C2) and place it in C4 also like to get the adjacent date to the highest value and place it in B3. Can this be done? A B C 1 3/1/2006 5 2 3/5/2006 4 3 4 Date(Adjacent to highest Formula(highest) Roger: I have another question related to the same thing. I modified the formula to fit my application =INDEX(C5:C8,MATCH(G6,D5:D8,0)) Works grate with one exception, The C5:C8 are date range if null you will see 1/0/1900 I use to have this formula with help from this group to empty the cell if there was no date: =IF(OR(ISERROR(MAX(C5:C8)),MAX(C5:C8)=0),"",MAX(C5 :C8)) How would I be able to melt your formula to this so it dose both. I have tried couple of ways to just see what would happen but have been unsuccessful. |
Get highest value and adjacent cell value
On Jul 13, 1:15 am, "Roger Govier"
wrote: Hi I'm not sure what you are after here. If you are saying you want a null returned, rather than Date 0 (00/01/1900) then =IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,"",INDEX(C5:C 8,MATCH(G6,D5:D8,0))) Alternatively, if there is no data in column C opposite the largest value in column D and you wanted the date next to the second highest value, then you could use. =IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,INDEX(C5:C8,M ATCH(LARGE(D5:D8,2),D5:D8,0)),INDEX(C5:C8,MATCH(G6 ,D5:D8,0))) -- Regards Roger Govier "Ardy" wrote in message oups.com... On Jul 6, 2:40 pm, "Roger Govier" wrote: Hi In C4 =MAX(C1:C2) in B4 =INDEX(B1:B2,MATCH(C4,C1:C2,0)) -- Regards Roger Govier "Ardy" wrote in message groups.com... Formula help: Would it be possible to get a highest of some cells and but instead of retaining the value get the value of adjacent cell. For example, I have numbers in cells C1 and C2 I also have Dates in Cell B1 and B2 each date corresponds to a score value. I like get a highest value =MAX(C1:C2) and place it in C4 also like to get the adjacent date to the highest value and place it in B3. Can this be done? A B C 1 3/1/2006 5 2 3/5/2006 4 3 4 Date(Adjacent to highest Formula(highest) Roger: I have another question related to the same thing. I modified the formula to fit my application =INDEX(C5:C8,MATCH(G6,D5:D8,0)) Works grate with one exception, The C5:C8 are date range if null you will see 1/0/1900 I use to have this formula with help from this group to empty the cell if there was no date: =IF(OR(ISERROR(MAX(C5:C8)),MAX(C5:C8)=0),"",MAX(C5 :C8)) How would I be able to melt your formula to this so it dose both. I have tried couple of ways to just see what would happen but have been unsuccessful. Roger: Your First assumption was correct and the formula for that works perfect- I modify the cell numbers =IF(INDEX(B7:B10,MATCH(F8,C7:C10,0))=0,"",INDEX(B7 :B10,MATCH(F8,C7:C10,0))) Your second one got my curiosity going and am trying to understand it so I placed it in the situation to see what results I get so maybe by seeing the results I would understand it better but it Keeps giving me error(#N/A). - I modify the cell numbers =IF(INDEX(B17:B20,MATCH(G16,C17:C20,0))=0,INDEX(B1 7:B20,MATCH(LARGE(C17:C20,2),C17:C20,0)),INDEX(B17 :B20,MATCH(G16,C17:C20,0))) I Thank you for the first formula Regards Ardy |
Get highest value and adjacent cell value
Hi
Maybe if you just moved the values down the page, then it should be looking at cell F16 not G16 =IF( INDEX(B17:B20,MATCH(G16,C17:C20,0))=0, INDEX(B17:B20,MATCH(LARGE(C17:C20,2),C17:C20,0)), INDEX(B17:B20,MATCH(F16,C17:C20,0))) Works fine for me and returns 01/03/2006 With B17 = 01/01/2006 C17 =5 B18 =01/03/2006 C18 =6 B19 = C19=7 F16 =MAX(C17:C20) The formula basically says, if the first test (checking for the Date relative to the largest value in C17:C20) =0, then use the date opposite the second largest value in C17:C20 LARGE(C17:C20,2), otherwise return the date opposite the largest value in C17:C20 -- Regards Roger Govier "Ardy" wrote in message ups.com... On Jul 13, 1:15 am, "Roger Govier" wrote: Hi I'm not sure what you are after here. If you are saying you want a null returned, rather than Date 0 (00/01/1900) then =IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,"",INDEX(C5:C 8,MATCH(G6,D5:D8,0))) Alternatively, if there is no data in column C opposite the largest value in column D and you wanted the date next to the second highest value, then you could use. =IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,INDEX(C5:C8,M ATCH(LARGE(D5:D8,2),D5:D8,0)),INDEX(C5:C8,MATCH(G6 ,D5:D8,0))) -- Regards Roger Govier "Ardy" wrote in message oups.com... On Jul 6, 2:40 pm, "Roger Govier" wrote: Hi In C4 =MAX(C1:C2) in B4 =INDEX(B1:B2,MATCH(C4,C1:C2,0)) -- Regards Roger Govier "Ardy" wrote in message groups.com... Formula help: Would it be possible to get a highest of some cells and but instead of retaining the value get the value of adjacent cell. For example, I have numbers in cells C1 and C2 I also have Dates in Cell B1 and B2 each date corresponds to a score value. I like get a highest value =MAX(C1:C2) and place it in C4 also like to get the adjacent date to the highest value and place it in B3. Can this be done? A B C 1 3/1/2006 5 2 3/5/2006 4 3 4 Date(Adjacent to highest Formula(highest) Roger: I have another question related to the same thing. I modified the formula to fit my application =INDEX(C5:C8,MATCH(G6,D5:D8,0)) Works grate with one exception, The C5:C8 are date range if null you will see 1/0/1900 I use to have this formula with help from this group to empty the cell if there was no date: =IF(OR(ISERROR(MAX(C5:C8)),MAX(C5:C8)=0),"",MAX(C5 :C8)) How would I be able to melt your formula to this so it dose both. I have tried couple of ways to just see what would happen but have been unsuccessful. Roger: Your First assumption was correct and the formula for that works perfect- I modify the cell numbers =IF(INDEX(B7:B10,MATCH(F8,C7:C10,0))=0,"",INDEX(B7 :B10,MATCH(F8,C7:C10,0))) Your second one got my curiosity going and am trying to understand it so I placed it in the situation to see what results I get so maybe by seeing the results I would understand it better but it Keeps giving me error(#N/A). - I modify the cell numbers =IF(INDEX(B17:B20,MATCH(G16,C17:C20,0))=0,INDEX(B1 7:B20,MATCH(LARGE(C17:C20,2),C17:C20,0)),INDEX(B17 :B20,MATCH(G16,C17:C20,0))) I Thank you for the first formula Regards Ardy |
Get highest value and adjacent cell value
On Jul 16, 2:14 pm, "Roger Govier"
wrote: Hi Maybe if you just moved the values down the page, then it should be looking at cell F16 not G16 =IF( INDEX(B17:B20,MATCH(G16,C17:C20,0))=0, INDEX(B17:B20,MATCH(LARGE(C17:C20,2),C17:C20,0)), INDEX(B17:B20,MATCH(F16,C17:C20,0))) Works fine for me and returns 01/03/2006 With B17 = 01/01/2006 C17 =5 B18 =01/03/2006 C18 =6 B19 = C19=7 F16 =MAX(C17:C20) The formula basically says, if the first test (checking for the Date relative to the largest value in C17:C20) =0, then use the date opposite the second largest value in C17:C20 LARGE(C17:C20,2), otherwise return the date opposite the largest value in C17:C20 -- Regards Roger Govier "Ardy" wrote in message ups.com... On Jul 13, 1:15 am, "Roger Govier" wrote: Hi I'm not sure what you are after here. If you are saying you want a null returned, rather than Date 0 (00/01/1900) then =IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,"",INDEX(C5:C 8,MATCH(G6,D5:D8,0))) Alternatively, if there is no data in column C opposite the largest value in column D and you wanted the date next to the second highest value, then you could use. =IF(INDEX(C5:C8,MATCH(G6,D5:D8,0))=0,INDEX(C5:C8,M ATCH(LARGE(D5:D8,2),D5:D8,0)),INDEX(C5:C8,MATCH(G6 ,D5:D8,0))) -- Regards Roger Govier "Ardy" wrote in message groups.com... On Jul 6, 2:40 pm, "Roger Govier" wrote: Hi In C4 =MAX(C1:C2) in B4 =INDEX(B1:B2,MATCH(C4,C1:C2,0)) -- Regards Roger Govier "Ardy" wrote in message groups.com... Formula help: Would it be possible to get a highest of some cells and but instead of retaining the value get the value of adjacent cell. For example, I have numbers in cells C1 and C2 I also have Dates in Cell B1 and B2 each date corresponds to a score value. I like get a highest value =MAX(C1:C2) and place it in C4 also like to get the adjacent date to the highest value and place it in B3. Can this be done? A B C 1 3/1/2006 5 2 3/5/2006 4 3 4 Date(Adjacent to highest Formula(highest) Roger: I have another question related to the same thing. I modified the formula to fit my application =INDEX(C5:C8,MATCH(G6,D5:D8,0)) Works grate with one exception, The C5:C8 are date range if null you will see 1/0/1900 I use to have this formula with help from this group to empty the cell if there was no date: =IF(OR(ISERROR(MAX(C5:C8)),MAX(C5:C8)=0),"",MAX(C5 :C8)) How would I be able to melt your formula to this so it dose both. I have tried couple of ways to just see what would happen but have been unsuccessful. Roger: Your First assumption was correct and the formula for that works perfect- I modify the cell numbers =IF(INDEX(B7:B10,MATCH(F8,C7:C10,0))=0,"",INDEX(B7 :B10,MATCH(F8,C7:C10,0))) Your second one got my curiosity going and am trying to understand it so I placed it in the situation to see what results I get so maybe by seeing the results I would understand it better but it Keeps giving me error(#N/A). - I modify the cell numbers =IF(INDEX(B17:B20,MATCH(G16,C17:C20,0))=0,INDEX(B1 7:B20,MATCH(LARGE(C17:C20,2),C17:C20,0)),INDEX(B17 :B20,MATCH(G16,C17:C20,0))) I Thank you for the first formula Regards Ardy Thanks Roger: I think I see what is happening now...... If you would recommend a book that would get in to more advanced functions and formulas what would it be....... Regards Ardy |
| All times are GMT +1. The time now is 03:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com