 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2008Have a value be shown as the referneced cell
I am running a formula =Max($a$1:$B$55,1) to find the largest number in a set of data. The answer was found in cell A36. The formula was fine but I was wondering if there is a formula that will display the answer's cell location. The value of A36 is 10. With my formula =Max($a$1:$B$55,1) the answer is 10. But I would need another formula to display A36 (or the cell's location) The find function isn't working One play, in say, C1 =IF(ISNA(MATCH(MAX($A$1:$B$55,1),$A$1:$A$55,0)),IF(ISNA(MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"","B"&MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"A"&MATCH(MAX($A$1:$B$55,1),$A$1:$A$55,0)) The above sequentially matches the result from the MAX down col A first, then down col B. If there are any ties in the MAX, then only the 1st matched instance will be returned.  SignatureMax Singapore http://savefile.com/projects/236895 Downloads:16,200 Files:354 Subscribers:53 xdemechanik --- > I am running a formula =Max($a$1:$B$55,1) to find the largest number in a set > of data. The answer was found in cell A36. [quoted text clipped - 7 lines] > But I would need another formula to display A36 (or the cell's location) > The find function isn't working ok i think is would work but this was more than I expected. How would it work if this was my actual data range. $C$33 : $R$33 and I am finding the MIN > One play, in say, C1: > =IF(ISNA(MATCH(MAX($A$1:$B$55,1),$A$1:$A$55,0)),IF(ISNA(MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"","B"&MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"A"&MATCH(MAX($A$1:$B$55,1),$A$1:$A$55,0)) [quoted text clipped - 13 lines] > > But I would need another formula to display A36 (or the cell's location) > > The find function isn't working Try this: =CHAR(MATCH(MIN(C33:R33),C33:R33,0)+66)&"33" Hope this helps. Pete > ok i think is would work but this was more than I expected. How would it work > if this was my actual data range. [quoted text clipped - 27 lines] > > - Show quoted text - Thanks Pete that worked Perfect!!! > Try this: > [quoted text clipped - 35 lines] > > > > - Show quoted text - Thanks for feeding back, but see my later post for the embellishment you asked for. Pete > Thanks Pete that worked Perfect!!! also where is the answer referenced in the formula. > One play, in say, C1: > =IF(ISNA(MATCH(MAX($A$1:$B$55,1),$A$1:$A$55,0)),IF(ISNA(MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"","B"&MATCH(MAX($A$1:$B$55,1),$B$1:$B$55,0)),"A"&MATCH(MAX($A$1:$B$55,1),$A$1:$A$55,0)) [quoted text clipped - 13 lines] > > But I would need another formula to display A36 (or the cell's location) > > The find function isn't working Sorry I am not using MIN OR MAX but rather SMALL > also where is the answer referenced in the formula. > [quoted text clipped - 15 lines] > > > But I would need another formula to display A36 (or the cell's location) > > > The find function isn't working Use this instead of my MIN formula: =CHAR(MATCH(SMALL(C33:R33,1),C33:R33,0)+66)&"33" You can change the 1 in the SMALL function to 2, 3, 4 etc to get the next smallest etc. If you want to show the cell contents as well as the reference, you can do this: =CHAR(MATCH(SMALL(C33:R33,1),C33:R33,0)+66)&"33 contains "&SMALL(C33:R33,1) which shows: F33 contains 2 with my test data. Hope this helps. Pete > Sorry I am not using MIN OR MAX but rather SMALL > [quoted text clipped - 26 lines] > > - Show quoted text - Pete what is the +66 and "33" just curious > Use this instead of my MIN formula: > [quoted text clipped - 47 lines] > > > > - Show quoted text - Basically, the formula is returning a text value made up of a letter and a number (eg F33) which looks like a cell reference. As your data is all on row 33, then we don't need to search in different rows and can just return this as a constant value. If you do search in more than one row, you will need a more complex formula like the one Max gave to you. The CHAR function returns the character for the ASCII code provided as the parameter. MATCH will return the relative postition of the found cell, so if the found value is in cell C33 then MATCH will return 1. The ASCII code for C is 67, so 66 needs to be added on in order to return the correct letter. Note that this approach will only work for up to column Z. Hope this helps. Pete > Pete what is the +66 and "33" just curious > [quoted text clipped - 18 lines] > > > Pete because now if I change the data range from C33:R35 instead of R33 it gives me an answer for N/A? > Use this instead of my MIN formula: > [quoted text clipped - 47 lines] > > > > - Show quoted text - Try this *array* formula: =ADDRESS(MAX((C33:R35=MIN(C33:R35))*ROW(C33:R35)),MAX((C33:R35=MIN(C33:R35))*COLUMN(C33:R35)),4) SignatureArray formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. Don't let the Max functions in the formula confuse you. This will find the smallest number's address. If there are duplicates, the last entry is returned. Blanks are considered as 0's. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== because now if I change the data range from C33:R35 instead of R33 it gives me an answer for N/A? "Pete_UK" wrote: > Use this instead of my MIN formula: > [quoted text clipped - 56 lines] > > > > - Show quoted text - You can drop the last argument in the Address function (,4). That just formats the return to a relative reference. Without it, the return is absolute. =ADDRESS(MAX((C33:R35=MIN(C33:R35))*ROW(C33:R35)),MAX((C33:R35=MIN(C33:R35))*COLUMN(C33:R35))) SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- Try this *array* formula: =ADDRESS(MAX((C33:R35=MIN(C33:R35))*ROW(C33:R35)),MAX((C33:R35=MIN(C33:R35))*COLUMN(C33:R35)),4) SignatureArray formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. Don't let the Max functions in the formula confuse you. This will find the smallest number's address. If there are duplicates, the last entry is returned. Blanks are considered as 0's. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== because now if I change the data range from C33:R35 instead of R33 it gives me an answer for N/A? "Pete_UK" wrote: > Use this instead of my MIN formula: > [quoted text clipped - 55 lines] > > > > - Show quoted text - RagDyeR came provides an EXCELLENT solution. I think the following refines his solution just a tad: First, I name the data range: MyRng. Second, I used the SMALL function in lieu of the MIN function. Thus to find the smallest value in the range: MyRng, the following array function should work: {=ADDRESS(MAX((MyRng=MIN(MyRng))*ROW(MyRng)),MAX((MyRng=MIN(MyRng))*COLUMN(MyRng)))&" contains the smallest number: "&SMALL(MyRng,1)} To find the 3rd smallest value, the following function should work: '{=ADDRESS(MAX((MyRng=SMALL(MyRng,3))*ROW(MyRng)),MAX((MyRng=SMALL(MyRng,3))*COLUMN(MyRng)))&" contains the 3rd smallest number: "&SMALL(MyRng,3)} Thanks marc for a good question and an even greater thank you to RagDyeR for his solution. Good Luck. > You can drop the last argument in the Address function (,4). > [quoted text clipped - 71 lines] > > > > > > - Show quoted text - Ooops ... let me re-do my 1st reply. Sorry. RagDyeR provides an EXCELLENT solution. I think the following refines his solution just a tad: First, I name the data range: MyRng. Second, I used the SMALL function in lieu of the MIN function. Thus to find the smallest value in the range: MyRng, the following array function should work: {=ADDRESS(MAX((MyRng=SMALL(MyRng,1))*ROW(MyRng)),MAX((MyRng=SMALL(MyRng,1))*COLUMN(MyRng)))&" contains the smallest number: "&SMALL(MyRng,1)} To find the 3rd smallest value, the following function should work: '{=ADDRESS(MAX((MyRng=SMALL(MyRng,3))*ROW(MyRng)),MAX((MyRng=SMALL(MyRng,3))*COLUMN(MyRng)))&" contains the 3rd smallest number: "&SMALL(MyRng,3)} Thanks marc for a good question and an even greater thank you to RagDyeR for his solution. Good Luck. > You can drop the last argument in the Address function (,4). > [quoted text clipped - 71 lines] > > > > > > - Show quoted text - Thanks for all of the help everyone! > Ooops ... let me re-do my 1st reply. Sorry. > [quoted text clipped - 95 lines] > > > > > > > > - Show quoted text - We appreciate your feed-back. SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- Thanks for all of the help everyone! "ND Pard" wrote: > Ooops ... let me re-do my 1st reply. Sorry. > [quoted text clipped - 99 lines] > > > > > > > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.