Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Address of a Cell
Is there a way to find the cell address where a specific value is?
If I wanted to know in which row (of column P) I have the greatest value that is <= 80, how would I do that? Thanks. -- Tiziano |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Address of a Cell
=MATCH(1,(P1:P500<80)*(ISNUMBER(P1:P500)),0)
entered with ctrl +shift & enter will give you the row number =INDEX(P1:P500,MATCH(1,(P1:P500<80)*(ISNUMBER(P1:P 500)),0)) entered the same way will give you the value =CELL("address",=INDEX(P1:P500,MATCH(1,(P1:P500<80 )*(ISNUMBER(P1:P500)),0))) entered the same way will give you the address as a text string -- Regards, Peo Sjoblom "tb" wrote in message ... Is there a way to find the cell address where a specific value is? If I wanted to know in which row (of column P) I have the greatest value that is <= 80, how would I do that? Thanks. -- Tiziano |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Address of a Cell
On Dec 12, 5:42 pm, "Peo Sjoblom" wrote:
=MATCH(1,(P1:P500<80)*(ISNUMBER(P1:P500)),0) entered with ctrl +shift & enter will give you the row number =INDEX(P1:P500,MATCH(1,(P1:P500<80)*(ISNUMBER(P1:P 500)),0)) entered the same way will give you the value =CELL("address",=INDEX(P1:P500,MATCH(1,(P1:P500<80 )*(ISNUMBER(P1:P500)),0))) entered the same way will give you the address as a text string -- Regards, Peo Sjoblom "tb" wrote in message ... Is there a way to find the cell address where a specific value is? If I wanted to know in which row (of column P) I have the greatest value that is <= 80, how would I do that? Thanks. -- Tiziano Thanks, Peo! -- tb |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Address of a Cell
I created the following formula (which does not seem to work...)
{=CELL("address",INDEX(J$8:J$65000,MATCH(1,(J$8:J$ 65000<K $6)*(ISNUMBER(J$8:J$65000)),0)))} Where the original data is in Col. J (the data in this column is expressed in cumulative percentage form) and the reference value is in cell K6 (expressed as a percentage, in my case the value is "80.00%"). What I get as a result is always "$J$8" which is not the cell the has the highest cumulative percentage value that is lower than 80%. To be clear, J8 has 2.23%, J9 has 4.18%, J10 has 5.91%,... J652 has 79.98% and J653 has 80.01%. When applying the above-mentioned formula, I would expect the result to be "$J$652". Instead, I keep on getting "$J$8". Thanks for all your help. -- tb On Dec 12, 5:42 pm, "Peo Sjoblom" wrote: =MATCH(1,(P1:P500<80)*(ISNUMBER(P1:P500)),0) entered with ctrl +shift & enter will give you the row number =INDEX(P1:P500,MATCH(1,(P1:P500<80)*(ISNUMBER(P1:P 500)),0)) entered the same way will give you the value =CELL("address",=INDEX(P1:P500,MATCH(1,(P1:P500<80 )*(ISNUMBER(P1:P500)),0))) entered the same way will give you the address as a text string -- Regards, Peo Sjoblom "tb" wrote in message ... Is there a way to find the cell address where a specific value is? If I wanted to know in which row (of column P) I have the greatest value that is <= 80, how would I do that? Thanks. -- Tiziano |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Address of a Cell
(Formulas shown in sections for readability)
Try this regular formula: =CELL("address",INDEX(J8:J65000,MATCH(MAX( INDEX((J8:J65000<K6)*J8:J65000,0)),J8:J65000,0))) or...this ARRAY FORMULA version,committed with CTRL+SHIFT+ENTER: =CELL("address",INDEX(J8:J65000,MATCH(MAX( (J8:J65000<K6)*J8:J65000),J8:J65000,0))) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "tb" wrote in message ... Is there a way to find the cell address where a specific value is? If I wanted to know in which row (of column P) I have the greatest value that is <= 80, how would I do that? Thanks. -- Tiziano |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Address of a Cell
On Dec 12, 6:50 pm, "Ron Coderre"
wrote: (Formulas shown in sections for readability) Try this regular formula: =CELL("address",INDEX(J8:J65000,MATCH(MAX( INDEX((J8:J65000<K6)*J8:J65000,0)),J8:J65000,0))) or...this ARRAY FORMULA version,committed with CTRL+SHIFT+ENTER: =CELL("address",INDEX(J8:J65000,MATCH(MAX( (J8:J65000<K6)*J8:J65000),J8:J65000,0))) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "tb" wrote in message ... Is there a way to find the cell address where a specific value is? If I wanted to know in which row (of column P) I have the greatest value that is <= 80, how would I do that? Thanks. -- Tiziano Thanks, Ron! It works very well. -- tb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find last cell in range with data, display cell address | Excel Worksheet Functions | |||
Data Validation: Store cell address instead of value in the cell? | Excel Discussion (Misc queries) | |||
How to create table of cell names with the name's cell address | Excel Discussion (Misc queries) | |||
How make hyperlink refer to cell content rather than cell address. | Excel Discussion (Misc queries) | |||
Finding Cell Address for Use as Formula Argument | Excel Worksheet Functions |