Dynamic Assignment of a Cell Address In Formula

[tb]

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.

 

[Carim]

Hi,

Is your ultimate objective to get the Row Address which contains the
value appearing in cell K6 ?

 

[tb]

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.

 

[Carim]

OK then ... could following formula help :

=ADDRESS(SUMPRODUCT(MAX((ROW(J8:J9000))*(J8:J9000<=K6))),2,4)

HTH

 

[tb]

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.

 

[Carim]

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

 

[Carim]

Sorry for the confusion ...

=ADDRESS(SUMPRODUCT(MAX((ROW(J8:J9000))*(J8:J9000<=K6))),10,4)

HTH

 

[Carim]

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

 

[tb]

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.

 

[Carim]

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