| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Contents of adjacent cell from a function
I have a list (column) of three digit numbers (telephone area codes) that
have an adjacent listing of number of calls associated with each area code. Elsewhere on the spreadsheet I use the Large function to determine the top ten area codes by call volume. My problem is identifying the area code associated with each of the top ten. Offset would seems to be the answer but I don't know how to use the Large function as the reference in the Offset function. A B 201 15 202 25 203 12 zzz xxx Top Ten Area Codes Calls ? 25 =Large(b1:bxxx,1) ? 15 =Large(b1:bxxx,2) ? 12 =Large(b1:bxxx,3) -- Thanks, Nick |
|
#2
|
|||
|
|||
|
Try this and copy down 10 rows:
=INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW(A1)), $B$1:$B$20,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Nick" wrote in message ... I have a list (column) of three digit numbers (telephone area codes) that have an adjacent listing of number of calls associated with each area code. Elsewhere on the spreadsheet I use the Large function to determine the top ten area codes by call volume. My problem is identifying the area code associated with each of the top ten. Offset would seems to be the answer but I don't know how to use the Large function as the reference in the Offset function. A B 201 15 202 25 203 12 zzz xxx Top Ten Area Codes Calls ? 25 =Large(b1:bxxx,1) ? 15 =Large(b1:bxxx,2) ? 12 =Large(b1:bxxx,3) -- Thanks, Nick |
|
#3
|
|||
|
|||
|
This seems to work well except that duplicate entries in column B always
return the first occurrence from column A. Any thoughts and thanks for your help, it's greatly appreciated. -- Thanks, Nick "RagDyer" wrote: Try this and copy down 10 rows: =INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW(A1)), $B$1:$B$20,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Nick" wrote in message ... I have a list (column) of three digit numbers (telephone area codes) that have an adjacent listing of number of calls associated with each area code. Elsewhere on the spreadsheet I use the Large function to determine the top ten area codes by call volume. My problem is identifying the area code associated with each of the top ten. Offset would seems to be the answer but I don't know how to use the Large function as the reference in the Offset function. A B 201 15 202 25 203 12 zzz xxx Top Ten Area Codes Calls ? 25 =Large(b1:bxxx,1) ? 15 =Large(b1:bxxx,2) ? 12 =Large(b1:bxxx,3) -- Thanks, Nick |
|
#4
|
|||
|
|||
|
Lets say that *YOUR* Large() formula is in Column C, with the Area codes in
A and the number of calls in B. Enter this in D1, and copy down to D10: =INDEX($A$1:$A$25,SMALL(IF($B$1:$B$25=C1,ROW($A$1: $A$25)),COUNTIF(C1:$C$10,C 1))) This will return the area codes with duplicate volumes in Column C, in the order that they are listed in Column A. If you wish to reverse this listing of duplicates, simply replace the Small() function in the formula with Large(). -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nick" wrote in message ... This seems to work well except that duplicate entries in column B always return the first occurrence from column A. Any thoughts and thanks for your help, it's greatly appreciated. -- Thanks, Nick "RagDyer" wrote: Try this and copy down 10 rows: =INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW(A1)), $B$1:$B$20,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Nick" wrote in message ... I have a list (column) of three digit numbers (telephone area codes) that have an adjacent listing of number of calls associated with each area code. Elsewhere on the spreadsheet I use the Large function to determine the top ten area codes by call volume. My problem is identifying the area code associated with each of the top ten. Offset would seems to be the answer but I don't know how to use the Large function as the reference in the Offset function. A B 201 15 202 25 203 12 zzz xxx Top Ten Area Codes Calls ? 25 =Large(b1:bxxx,1) ? 15 =Large(b1:bxxx,2) ? 12 =Large(b1:bxxx,3) -- Thanks, Nick |
|
#5
|
|||
|
|||
|
This is brilliant. Thank you very much.
-- Thanks, Nick "Ragdyer" wrote: Lets say that *YOUR* Large() formula is in Column C, with the Area codes in A and the number of calls in B. Enter this in D1, and copy down to D10: =INDEX($A$1:$A$25,SMALL(IF($B$1:$B$25=C1,ROW($A$1: $A$25)),COUNTIF(C1:$C$10,C 1))) This will return the area codes with duplicate volumes in Column C, in the order that they are listed in Column A. If you wish to reverse this listing of duplicates, simply replace the Small() function in the formula with Large(). -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nick" wrote in message ... This seems to work well except that duplicate entries in column B always return the first occurrence from column A. Any thoughts and thanks for your help, it's greatly appreciated. -- Thanks, Nick "RagDyer" wrote: Try this and copy down 10 rows: =INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW(A1)), $B$1:$B$20,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Nick" wrote in message ... I have a list (column) of three digit numbers (telephone area codes) that have an adjacent listing of number of calls associated with each area code. Elsewhere on the spreadsheet I use the Large function to determine the top ten area codes by call volume. My problem is identifying the area code associated with each of the top ten. Offset would seems to be the answer but I don't know how to use the Large function as the reference in the Offset function. A B 201 15 202 25 203 12 zzz xxx Top Ten Area Codes Calls ? 25 =Large(b1:bxxx,1) ? 15 =Large(b1:bxxx,2) ? 12 =Large(b1:bxxx,3) -- Thanks, Nick |
|
#6
|
|||
|
|||
|
Appreciate the feed-back.
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Nick" wrote in message ... This is brilliant. Thank you very much. -- Thanks, Nick "Ragdyer" wrote: Lets say that *YOUR* Large() formula is in Column C, with the Area codes in A and the number of calls in B. Enter this in D1, and copy down to D10: =INDEX($A$1:$A$25,SMALL(IF($B$1:$B$25=C1,ROW($A$1: $A$25)),COUNTIF(C1:$C$10,C 1))) This will return the area codes with duplicate volumes in Column C, in the order that they are listed in Column A. If you wish to reverse this listing of duplicates, simply replace the Small() function in the formula with Large(). -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Nick" wrote in message ... This seems to work well except that duplicate entries in column B always return the first occurrence from column A. Any thoughts and thanks for your help, it's greatly appreciated. -- Thanks, Nick "RagDyer" wrote: Try this and copy down 10 rows: =INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW(A1)), $B$1:$B$20,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Nick" wrote in message ... I have a list (column) of three digit numbers (telephone area codes) that have an adjacent listing of number of calls associated with each area code. Elsewhere on the spreadsheet I use the Large function to determine the top ten area codes by call volume. My problem is identifying the area code associated with each of the top ten. Offset would seems to be the answer but I don't know how to use the Large function as the reference in the Offset function. A B 201 15 202 25 203 12 zzz xxx Top Ten Area Codes Calls ? 25 =Large(b1:bxxx,1) ? 15 =Large(b1:bxxx,2) ? 12 =Large(b1:bxxx,3) -- Thanks, Nick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I obtain the address of a cell using the vlookup function? | Excel Worksheet Functions | |||
| clock | Excel Worksheet Functions | |||
| Get current cell with VBA function | Excel Worksheet Functions | |||
| Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
| Hiding a function value in a cell until function used | Excel Worksheet Functions | |||
Linear Mode
