CODEWITHSUNDEEP
excel
Dimondwoof
Dimondwoof

Reputation: 71

How do I display a value of a derived cell address using the CELL command?

I am using the following to discover the address of the cell with the smallest value from within a range of cells:

=CELL("address",OFFSET(E1,MATCH(MIN(E1:E400),E1:E400,0)-1,0))

But it displays the actual address rather than the value of the address (e.g. $A$12). How would I get it to display the actual value of the cell that it finds?

Upvotes: 1

Views: 95

Answers (2)

user4039065
user4039065

Reputation:

If you insist on the most convoluted method, you could add the INDIRECT function as a 'wrapper'.

=INDIRECT(CELL("address",OFFSET(E1,MATCH(MIN(E1:E400),E1:E400,0)-1,0)))

TBH, seems like a waste of calculation cycles to me.

        min_E.

With your formula in H2, I 'wrapped' it with INDIRECT in G2. Given that you have expressed that you needed to also get the cell address, this may have been written as,

=INDIRECT(H2)

Note that you are working with volatile¹ functions.

¹ Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.

Upvotes: 0

Fred Truter
Fred Truter

Reputation: 666

If you want the smallest value in a range of cells, then you can use this simple formula (from your question) on its own:-

=MIN(E1:E400)

Upvotes: 1

Related Questions