How could I use IF without a THEN clause, similar to IFERROR?

English
, ,
#1

I mean to test a value with a condition and, if it’s true, i’d like that value itself to be returned, instead of having to reenter it (as in a THEN caluse inside the IF). I’d also like to have a ELSE condition so I can enter a default value in case the condition isn’t true.

Example:
=MYIF(INDEX(C5:N5;0;$P$1); "<>0"; "")

With this example I’d like the “INDEX(C5:N5;0;$P$1)” itself to be returned in case it’s not a 0 value, otherwise an empty string would be returned.

That is, I’d like to avoid reentering expressions in a THEN clause, as in the equivalent formula:
=IF(INDEX(C5:N5;0;$P$1)<>0; INDEX(C5:N5;0;$P$1); "")

Is there a way to do that?

#2

For your specific example just use your INDEX() formula but suppress the display of 0 values by applying a number format like

General;-General;;@

Note the empty third sub-format for the 0-condition, and this gets saved and loaded as

[>0]General;[<0]-General;"";@

which is the complete definition. If you also want to suppress text results then use

[>0]General;[<0]-General;"";""

See also online help Number Format Codes .
Or suppress display of 0 values entirely with Tools → Options → Calc → View, Display, uncheck Zero values; but that affects all.

#3

Thanks @erAck!

I realize that’s an option if I’m concerned only about the cell visualization, but it might not be the case for some situations. The intent of the example I gave was only to simplify it.
The final idea is to find a way to have “any kind of expression”, not only comparing 0 values, and that the MYIF’s returned value could actually be reused in a nested expression, for example.

#4

The usual approach to that is to have an interim cell for the otherwise to be recalculated expression once, and a second formula cell to evaluate the return value and further process. There is no function that would do what you want.

1 Like
#5

A formula like:
=IFERROR(INDEX(C5:N5;0;$P$1)*CURRENT()/CURRENT();"")
seems to work,
CURRENT() gets the value of last calculation, in this case INDEX() result, so if it is ‘0’ produce an error dividing by or multiply by ‘1’ at dividing by itself.

#6

The CURRENT() function is volatile in a sense; it will change during the course of formula evaluation: When you multiply by CURRENT(), the CURRENT() value updates, so the formula will evaluate to 1 if the “zero condition” does not occur.

#7

Elaborating on a previous suggestion:

While the CURRENT()/CURRENT() construct is not guaranteed to return either 1 or error, there is another construct which will. It gets a bit convoluted, but may still be worthwile if your expression is very complex, or if it is important that you evaluate the expression exactly once (e.g. if it contains volatile elements).

A conditional expression will evaluate to TRUE or FALSE, which are internally represented as 1 and 0, respectively. This means that “Divide by FALSE” is equivalent to “divide by zero” and “divide by TRUE” is equivalent to “divide by one”.

In current implementation, strict type check is not implemented, so this construct should work:
=IFERROR((your expression)/(your condition);"")

Edit:

The above also works in MS Excel which does some level of type checking for number vs. boolean. Still, to ensure a future proof formula if Calc goes full tilt “strict data type” (not very likely), you may want to add the “extract number” function N():

=IFERROR((your expression)/N(your condition);"")

Edit 2:
There seems to be a pitfall where your condition applies the original value, but in that case the CURRENT() function should work as expected. I haven’t tested this. You may need to have CURRENT() as the very first element of the conditional.

#8

The original idea to semantically refer to CURRENT()<>0 without needing to calculate the original expression a second time is easily implemented with the help of +(CURRENT()^-1 * 0).
See attached demo. The suggested solution as exemplified there would, of course, also return the empty string in any case of error, and this way for every interim result of type Text.
disask82990demo.ods (10.1 KB)

1 Like
#9

The original example was a “zero detector”, but …

Using the result of a conditional as the divisor, to throw an error which the IFERROR() will catch, should work.

The most serious pitfalls I can see with my approach revolve around data types. E.g.:

  • boolean and numeric are freely interchangeable in Calc spreadsheet formulas, but not necessarily in attached systems.
  • True is represented as 1 in Calc. In other contexts it may be a negative (-1).
#10

The simplest variation on this theme would be:

=IFERROR(1/(1/YourFormula);"")
#11

In fact we can see the generalized current question “how to detect without recalculation if results of a complicated calculation meet the needs?” as a reduced or specialized case of what sometimes is requested as “implement Excel’s LET() function”.
The advice concerning LET() generally is, of course, “use a helper range” and that’s good advice, except … You see? There are cases where it’s reasonable to avoid locked output ranges coming with the well-known problems for the structure of the used sheet, and not being re-dimensionable on update,
I considered the topic more than once during the last 6 years, and once even sketched a toy box (in Basic) containing MEMORIZEAS(), RECALL(), and DISPOSE() .I never tidied things up, and there isn’t a version I cold show somebody. A main reason (beyond efficiency, of course) for the mess was the conceptual conflict between “RECALL() usable also from an expression not doing the MEMORIZE() itself” and the need of a “dirty-storage-judgement” for UserDefinedStorage not properly disposed (freed) for exceptional reasons.

#12

Thanks @keme1 and @mariosv!

@keme1, as I understand from your suggestion I’d still have to replicate the formula for the condition term, is that right? The idea would be not needing to replicate the “value” formula just for inserting it into a condition.

I could make it work with something similar to @mariosv 's suggestion, but there’s the issue of CURRENT()'s output being volatile and changing along the formula, as you mentioned.

This is how I did it:
=IFERROR(INDEX(K18:Q18;0;M15)/(CURRENT()<>0);"")

About the CURRENT’s volatility issue, that makes it especially hard to use this result within other functions, within the same formula. But I figured its result is resetted each time it’s used inside as nested function, as in below:

=5+CURRENT()+4+CURRENT()
This returns “28”, since the second CURRENT will output “14”, the result from the whole preceding formula. But if you nest the second CURRENT’s usage within another function, this is what you get:

=5+CURRENT()+SUM(4+CURRENT())
This returns “18”, that is, the second CURRENT outputs “4”, instead of “14”, which means it’s resetted when used from inside a different function.

I think by using this workaround we could elaborate to create even more complex formulas, without needing to repeat the expression. It may become a bit convoluted for sure, but I thik it might still be better than repeating “complex” formulas in more places than needed, until we have a better solution.

#13

I suspect that if the condition can start with your formula, you can replace that by inserting the CURRENT() function as the very first element inside the parenthesis (the one which goes after the division operator).

As far as I can tell, when CURRENT() is the first operand inside parenthesis it returns the intermediate result before entering the parenthesis. If it is used later in the parenthesis, it is a “local CURRENT()” pertaining to the intermediate value calculated inside the parenthesis.

If you can’t make it work, can you give a practical (=actual) example of what formula you are struggling with?

The particular behavior of CURRENT() in this kind of situation is not documented anywhere I can find, so I guess it is not guaranteed to work like that in future versions of the software.

#14

CURRENT() is neither volatile nor has it “resetted its result”, it delivers the current result of the calculation up to the position where it is used. To completely understand the implications you have to know that a formula expression is compiled into RPN (Reverse Polish Notation) using a left-to-right recursive descent parser and the resulting RPN code is interpreted in order. For example, =1+2+SUM(3+4) leads to the RPN code

1, 2, +, 3, 4, +, SUM, +

where if interpreted the values 1 and 2 are pushed on the stack, then operator + is executed popping these two values and pushing the result 3 on the stack; same for values 3 and 4 and operator + result 7; the stack then has 3,7; SUM then is executed and pops its sole argument 7 and pushes the result 7 on the stack again so that has 3,7 again; the last operator + pops these two values and pushes 10, which is the final result.

For =5+CURRENT()+SUM(4+CURRENT()) the same, RPN:

5, CURRENT, +, 4, CURRENT, +, SUM, +
  • 5 is pushed on the stack
  • CURRENT sees 5 as last value on the stack and pushes 5 on the stack
    => stack is 5,5
  • operator + pops 5 and 5 from the stack and pushes 10
    => stack is 10
  • 4 is pushed on the stack
    => stack is 10,4
  • CURRENT sees 4 as last value on the stack and pushes 4 on the stack
    => stack is 10,4,4
  • operator + pops 4 and 4 from the stack and pushes 8
    => stack is 10,8
  • SUM pops 8 from the stack and pushes 8
    => stack is 10,8
  • operator + pops 8 and 10 from the stack and pushes 18
    => stack is 18, the final result
5 Likes

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