MAX function does not work with Defined Name range.

Question

Using Office365 Excel

Here is a snapshot of a simple worksheet.

Range A2:A6 Has defined name X

Column B shows that =X works fine

Column C shows expected results using formula =MAX(A2,0)

Column D shows actual results of =MAX(X,0)

Obviously a problem...

Answer 1

Hello Harold

I am V. Arya, Independent Advisor, to work with you on this issue. MAX function is working correctly.

Even if you drag =$A$2:$A$6 OR MAX($A$2:$A$6,0), you will get the same results. So this is not specific to named range.

Hence, while within a function it behaves the way it should, when you try dragging this without any function, it looks that it ignores $ when this is a range, hence giving A2, A3, A4 etc.

Answer 2

V. Arya,

Thanks for your quick response, but this is deeper than first blush.

I should have made this more of a question.  It is common to use Defined name ranges to reference a cell in a column of cells.  Example, In column B a formula of =SQRT(X^2) would result in values in the cells of column B that would represent the appropriate value for the specific row…  What happens if I use the MAX function (and apparently several others) is it takes the MAX of the whole range rather than the MAX of the cell.  This is certainly logical, but it makes using defined range names in columns very risky as some functions work on a cell by cell basis and others work on the whole range.  The deeper question becomes is there a way to say I want the MAX (and other functions) to execute on a row address rather than the whole range?  In other words can column D be made to work like column C using Defined name?

Again I see the logic.  But it makes using defined named ranges risky in designing a sheet…

Answer 3

If you want it to make work on a cell, you will have to extract that cell using INDEX.

=MAX(INDEX(x,1),0)

When 1 means first value in the range

You can use a ROW function to drag it down and select 1, 2, 3 and so on value

=MAX(INDEX(x,ROW(1:1)),0)

Answer 4

< Deleted.  Not interested >