certain functions don't handle named ranges including relative refs

[SteenLarsen]

I am using named ranges to clarify formulas in CALC, but MIN and MAX do not understand the ref when using named ranges including relative refs.

Example:

Named Range: Amount ($B$1:$B$10)
Named Range: Balance ($C$1:$C$10)
Named Range: PrevBalance (=$C2 when selected cell is $C$2) relative ref.
Cell C2: 0
Cell B3: 100,000
Cell C3: =PrevBalance+Amount
Cell E4: =MIN(Balance;500,000) returns 0, should be 100,000.
Cell E4: =MIN(Balance+0;500,000) returns correct 100000.

I think it has to do with when the ref is resolved.`
Any ideas - I do have a short example here: NamedRangeRelativeRefs.ods

[Edit - Opaque] Changed formatting of details for better readability

[anon73440385]

Could you please upload your sample: Something like : =PrevBalance+Amount (cell C3) is hard to understand if any of the names is a named range and implicit intersections came to play a role

[anon73440385]

Hmm - the range for Balance includes C1 and C2 and hence MIN(Balance) is always 0 - can’t see the problem here (MIN(Balance) doesn’t perform any implicit intersection here; it always evals to the minimum of the whole range, which is 0),

The effect of `+0` (you could also use `Balance*1`) obviously is, that it forces an implicit intersection of the cell's row with the array `Balance` (i.e. it calculates in fact the same thing as `=MIN(OFFSET(E3;0;-2;1;1);InterestBalanceLimit)` would do in eg. cell `E3`). Overall: I'd rate this method contrary to your initial goal to **clarify formulas**. In fact this makes it hard to see what's going on).

[SteenLarsen]

@anon73440385
Thanks for the formatting - I am new at this.
I allways use named ranges and rely on implicit intersection as I certainly believe that formulas with names are far better understandable. This example is a very short extract of the real formula.

[anon73440385]

It’s OK to use implicit intersections (though I clearly doubt many people understanding the underlying functionality), but there are functions, which convert ranges into a NumberSequenceList and hence ranges are “allowed” parameters to the function and won’t result in an implicit intersection. And this is clearly the case with using MIN() here. See OASIS Open Document Format for Office Applications (OpenDocument) Version 1.2

6.18.48 MIN

6.3.7 Conversion to NumberSequence

6.3.8 Conversion to NumberSequenceList

and that’s why you only can force the implicit intersection by the mentioned operations.

[SteenLarsen]

Thanks for the answers - it make sense though annoying.
On other google answers there are workarounds to force implicit intersection ala NamedRange+0, NamedRange*0, --NamedRange and VALUE(namedRange).
I remember to use the first workaround in the future.
Regards Steen