How to use CURRENT() multiple times in CALC

English
#1

I have cells with formulas and want to add styles to the cells depending on the outcome (X) of the formula.
The condition for the formatting depends on comparison of X with values of other cells. Therefore I need to use X more than once to determine the formatting.
Example (with values instead of formulas and cell references and Red, Green being styles):
=2+STYLE(IF(CURRENT()>1;“Green”;“Red”)) works
=2+STYLE(IF(AND(CURRENT()>1;CURRENT()<3);“Green”;“Red”)) does not work
=A2+$B2 +STYLE(IF(AND(CURRENT()>C2;(CURRENT()+$D$1/E2)<3);“Green”;“Red”))

How do I solve this?

Avoid repeating value in IF arguments
#2

No, multiple use of CURRENT() within the same formula can be used, but it makes no sense - of course, this is in case you are not going to deliberately confuse the essence of the calculations. Moreover, it does not make sense in the framework of the evaluation of the condition in the conditional operator. Of course, if you only need to get one of the two values “Green” or “Red”, then you can use not very complicated arithmetic transformations to convert the existing calculated value to 1 or 2 and use the CHOOSE() function.

However, using normal conditional formatting for the same purpose is much easier and more convenient.

1 Like
#3

A good reading with in-depth analysis of the complications related to CURRENT and the evaluation order is tdf#111675.

#4

Thanks for you comments.

To make myself more clear: I know and understand that/why CURRENT() doesn’t work in my case.
I do not know how to achieve my goal, i.e. use STYLE() with two comparisons using the current value of the cell, and hope that someone has a solution for this.

#5

=A2+$B2+STYLE(IF(AND(A2+$B2>C2;(A2+$B2+$D$1/E2)<3);"Green";"Red"))?

#6

JohnSUN, thank you for trying to help.
In my actual case A2 is not just a cell reference, but a formula of 70 characters long and I need to use it not in 1 cell but in hundreds of cells. Your suggestion would mean that the formula needs to be calculated three times for each cell. That imposes a significant performance loss…

#7

@winfrieddonkers: please note my comment 20 in the bug I mentioned above. I bet you are in the best position to implement LET and have it solved perfectly :wink:

#8

Is it possible to have that “A2 expansion” as an intermediate calculation in a separate cell? The evaluation would then happen once and would be referenced exactly like in the example. If required you can hide intermediate calculations from view.

Is it viable to provide a realistic sample file for us to inspect and test proposed solutions on? There may be other ways to achieve your goal.

#9

Just some background: @winfrieddonkers is one of our great contributors, who had implemented and improved a lot of spreadsheet functions in Calc. See e.g. in 6.0, in 5.4, in 5.2, …

1 Like
#11

Thanks for the “warning”, @mikekaganski! I guessed as much from your previous “implement LET” comment. Still, I let my comment stand. There may be “smart” workarounds a user has thought of before, which may not be obvious even to someone with deep understanding of the inner workings of Calc. Minds work in different ways…

1 Like
#17

As a non-user of Excel I shouldn’t judge. Nonetheless I can’t believe that an Excel-compatible (-mimicking) solution can solve anything perfectly. What I once read in an MS-related site about LET() didn’t convince me otherwise (reasons now forgotten).
However, a workaround written in LibreOffice Basic based on an instance of the service com.sun.star.beans.PropertyBag is a simple thing - and a respective enhancement of Calc itself should also not be too complicated. .

#18

:slight_smile:

Let us not over-generalize. I didn’t suggest to just mimic Excel everywhere. On the other side, I can’t agree with an assertion that any such mimicking can’t solve anything perfectly (i.e., the claim that nothing can be solved perfectly that way; that is the exact meaning of the doubt you expressed from the logic PoV, do you agree?)

So I do claim that: allowing one to explicitly declare calculated-once used-several-times values in a formula scope would resolve the problem expressed here (“needs to be calculated three times for each cell”) perfectly; and at the same time the solution that MS introduced happens to be well-thought, and implementing it would also increase compatibility.

#20

I agree, and what you describe as the purpose is exactly what I implemented the workaround-way abouit two years ago “just for fun”: (I never used that very raw solution substantially myself. However, I had missed at that time the advantages of the mentioned service, and had written respective helper functions. There is even a demo.) A solution enhancing Calc will surely try to get “better compatibility with Excel” at the same time.

#21

Thank you all for your comments. I know now for sure that I need either to solve it myself (e,g, by implementing LET in Calc) or to accept a performance loss because of double calculations and/or use of intermediate results in extra cells.

@mikekaganski : I currently need all my time to implement XLOOKUP with is very useful once implemented, but a beast to implement… I have marked LET as a possible next implementation candidate.

2 Likes
#22

I might misunderstand it; maybe it was meant as “accept … use of intermediate results in extra cells”. Just wanted to say that, if there is a perceived performance loss because of use of extra cells for intermediate results, I believe there should be no such loss, and use of cells for that is one of normal ways to use spreadsheets.

@winfrieddonkers: great that you do your outstanding contributions - THANK YOU!

#10

Hallo
Instead your second Example you should use something like:

=A2+STYLE(IFNA(VLOOKUP(CURRENT();ref;2);"Standard"))

ref is here some NamedRange with the sorted lower limits on the left Column and the StyleNames on the right Column

#12

Maybe offtopic: python can do the above comparism in the form: if 1<x<3
IHMO nice to Implement this semantics into Calc.

#13

No. That would be a completely incompatible syntax - due to vast number of existing spreadsheets and formulas - and thus, it is completely impossible (even if it would possibly be nice if spreadsheets were invented anew today).

By the way, I have a feeling that it works for some select cases (as a deprecated feature) - for compat reasons (@erAck can correct me). Or do I confuse with deprecated “a AND b”?

#14

maybe its if( (x>n) and (x<m) ; … but thats complete different.

as I said IHMO … but the syntax cannot exist in existing Formulas, so thereis no risk of disturbing backwardcompatibilty, and second [ironically] what happens, if lets say: Excel implement that semantic … how long would it take to make it possible? [/ironically]

#15

:slight_smile:

Unfortunately, this syntax does exist in existing spreadsheets (both as a mistake, and in cases where some formulas (ab)use it for generic conditions like “do it conditionally”/“do it unconditionally” depending on the constant used in the last comparison).

E.g., something like

=IF(A1>5>Conditions.C2; FOO; BAR)

where C2 may be -1 (“always do FOO”), 0 (“honor the first condition”), or 1 (“never do FOO”). Yes, there are uses like that in the wild.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.