Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
I would like to modify the following formula such that the cell
address J$8354 is replaced by a dynamic cell address formula. The original formula (in array format) is: {=CELL("address",INDEX(J$8:J$8354,MATCH(MAX((J$8:J $8354<=K$6)*J$8:J $8354),J$8:J$8354,0)))} The dynamic formula that would substitute cell address J$8354 is: =ADDRESS(MATCH(99^99,J:J),10) Unfortunately, when I merge the two formulas together, I get an error... Is it even possible to do what I have in mind without resorting to VBA? Thanks. -- tb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
Hi,
Is your ultimate objective to get the Row Address which contains the value appearing in cell K6 ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
On Dec 13, 9:12 am, Carim wrote:
Hi, Is your ultimate objective to get the Row Address which contains the value appearing in cell K6 ? Hi. Yes, the ultimate objective would be to find the cell address which contains the value <= to the one in cell K$6. (There could be more than one cell with the same value and <= to the one in K6. I would need to find the address of the cell with the biggest row number. So, if K$6's value is "80.0%", and both cells J1234 and J5678 share the value 80.0%, the formula would return address $J$5678 because that is the cell with the biggest row number and with the biggest value <= to K6.) Once I can successfully develop this formula, I would incorporate it in yet another formula... Should I go ahead and post this formula too, or do you have enough info? Thanks. -- tb |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
OK then ... could following formula help :
=ADDRESS(SUMPRODUCT(MAX((ROW(J8:J9000))*(J8:J9000< =K6))),2,4) HTH |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
On Dec 13, 10:48 am, Carim wrote:
OK then ... could following formula help : =ADDRESS(SUMPRODUCT(MAX((ROW(J8:J9000))*(J8:J9000< =K6))),2,4) HTH Hi, Carim. Unfortunately, the formula does not seem to work. I always get J9000 as the cell address. I am also looking for a way to DYNAMICALLY incorporate in the formula the last cell address (in your formula, statically indicated as "J9000") as that address will change in time and I do not want to run the risk of forgetting to manually adjust the range in the formula. The last cell address would be the cell in column J that has the closest value <= to cell K6. Should there be more than one cell in column J with the same value which also happens to be the closest value to K6, then I need the address of the cell that has the biggest row number containing such value. I hope I'm making sense... Thanks. -- tb |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
Hi again,
Just tested formula which is working at my end ... To answer specifically your question, you can use offset() like in this example : =SUM(J8:OFFSET(J8,COUNT(J8:J965536),0)) HTH |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
Sorry for the confusion ...
=ADDRESS(SUMPRODUCT(MAX((ROW(J8:J9000))*(J8:J9000< =K6))),10,4) HTH |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
Sorry for the confusion ...
=ADDRESS(SUMPRODUCT(MAX((ROW(J8:OFFSET(J8,COUNT(J8 :J965536), 0)))*(J8:OFFSET(J8,COUNT(J8:J965536),0)<=K6))),10, 4) HTH |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
On Dec 13, 12:43 pm, Carim wrote:
Sorry for the confusion ... =ADDRESS(SUMPRODUCT(MAX((ROW(J8:OFFSET(J8,COUNT(J8 :J965536), 0)))*(J8:OFFSET(J8,COUNT(J8:J965536),0)<=K6))),10, 4) HTH Thanks, Carim. What the above formula seems to do is return the address of the first cell whose value is <= than K6. What I am after is the address of the cell whose value comes _closest_ or _matches_ the value in K6. If there are more than one cell which meet this criteria, then I need the address of the cell matching that criteria and that has the bigger row number of all those cells. I realize that this seems confusing and I might not be doing a great job in explaining what I want. Would you be open to the possibility of me sending you the spreadsheet that I am preparing so that you might better see what I am trying to accomplish? Thanks. -- tb |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Assignment of a Cell Address In Formula
Well, at least we have overcome the first obstacle ... since the
formula now seems to work ... As you requested it, the formula is intended to return the Last cell address thanks to the MAX() function ... It could be that using <=K6 creates some illogical pattern with your data ... My initial recommendation for you is to test the formula without <=K6 ... but with =K6 to fully master the formula, and determine if you are reaching your objective. Should you still face difficulties, feel free to send me your spreadsheet ... HTH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Dynamic Cell Address As Formula Location | Excel Worksheet Functions | |||
Dynamic Formula with Dynamic Address | Excel Worksheet Functions | |||
Average Function and dynamic cell address | Excel Worksheet Functions | |||
Dynamic flexible cell address | Excel Discussion (Misc queries) | |||
ADDRESS function - dynamic input cell | Excel Discussion (Misc queries) |