How to use CURRENT() multiple times in CALC

[keme1]

the a<x<b syntax is permissible, and does work like @mikekaganski exemplified. However, in most cases it does not work as you may expect, and it only does because Calc does not enforce strict type. Most cases where it is used, it is done by mistake, and if it works as intended it is by sheer luck.

In the common mathemathical usage, the meaning of this type of “ternary operation” will be obvious from context. In a cell formula, allowing the use of combinations of < , = and > as ternary operator sets would introduce ambiguity of whether the relational operators are intended as two binary operators or one ternary operator set.

If it were introduced, it would require stricter type handling in Calc formulas, and this would most likely do more damage than good.

[karolus]

Yes youre both @keme1 @mikekaganski right, but I’ve never seen this syntax in the wild, and thought it throws an Error anyway.

[erAck]

@mikekaganski

Or do I confuse with deprecated “a AND b”?

Yes, you do ;)
a<x<b is simply evaluated as (a<x)<b so =1<1<1 is TRUE as it’s (1<1)<1 giving (0)<1.

[winfrieddonkers]

I created a solution for the time being:
I created a macro Between(value, lowerbound, upperbound) that works like the SQL BETWEEN operator and I use Between(CURRENT();onevalue;othervalue) in my formula, so that CURRENT() is used only once.

I may implement LET as suggested by @mikekaganski -when I have an opportunity to do so- or I may implement a new function BETWEEN is the discussion this morning showed that there is at least some need for it.

[sokol92]

I vote for LET.
Between:

=LET(a;value;AND(lowerbound<=a;a<=upperbound))

[Lupp]

It’s not exactly necessary to create an UDF like BETWEEN() for the cases where the suggestion by @erAck isn’t accepted.
With V, LB, UB in the obvious roles ABS(V-(LB+UB)/2)<=(UB-LB)/2 should return what’s expected for BETWEEN(), and V can now well be replaced with CURRENT(). No second reference to the value needed.

[erAck]

Use conditional formatting instead of STYLE(), especially if formatting according to the result of a formula expression is needed. Best avoid STYLE() altogether, it can do nothing that conditional formatting couldn’t do, except one thing that is if the style has to be immediately coupled with formula evaluation and the timeout parameter is used, which serves only in the context of asynchronous Add-In functions and was the reason it (and CURRENT()) was introduced. If that feature is not used then STYLE() has only disadvantages in comparison to conditional formatting, as it has to be always executed whenever the formula is processed, whereas conditional formatting kicks only in for the view area.

[winfrieddonkers]

I would like to, but so far I haven’t found out how to use relative cell adressing in the condition for conditional formatting. I my case I have conditions depending on the result of a formula and the contents of other cells, the same cell for all cells in the conditional format range as well as cells depending on the location of the cell to be formatted.

[karolus]

the same rules in Formulas (absolute versus relativ Addresses ) are applied regardless you are inside CF or not.

[mikekaganski]

… with a detail that the relative addresses are relative to the top left cell of the range of the CF; you should treat it as if you entered the formula into that top left cell.

[winfrieddonkers]

(Finally) got it. Thanks!