Ask Your Question
0

certain functions don't handle named ranges including relative refs

asked 2020-11-21 15:25:17 +0100

SteenLarsen gravatar image

updated 2020-11-21 16:05:28 +0100

Opaque gravatar image

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: C:\fakepath\NamedRangeRelativeRefs.ods

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

edit retag flag offensive close merge delete

Comments

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

Opaque gravatar imageOpaque ( 2020-11-21 15:43:38 +0100 )edit

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).

Opaque gravatar imageOpaque ( 2020-11-21 15:52:30 +0100 )edit

@Opaque 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.

SteenLarsen gravatar imageSteenLarsen ( 2020-11-21 17:45:34 +0100 )edit

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.

Opaque gravatar imageOpaque ( 2020-11-21 18:40:16 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-11-22 10:15:09 +0100

SteenLarsen gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-11-21 15:25:17 +0100

Seen: 25 times

Last updated: Nov 22