Looking for solution to shorten an IF formula

Question

I have a long formula with the following structure:

=IF(!X!<>0,!X!+A1,"")

where !X! is a very long formula.

Is there any solution to avoid repeating the !X! twice? I need it for two reasons:

To make the formula readable for user of the sheet
To reduce the process time

I appreciate any help.

Thanks, Dio

if you want `To reduce the process time` then obviously you have to store the result somewhere, otherwise Excel has to recalculate the value again when the condition matches. Division is also a very costly operation — phuclv, Nov 24 '17 at 13:35
Just curious too, have you "golfed" the long formula down as much as you can? To make it shorter maybe add named ranges? — BruceWayne, Nov 25 '17 at 00:52
To make a long formula more readable, separate the arguments onto different lines using alt+return. One downside is unless the user expands the height of the formula bar, the formula looks incomplete. — Brad Smith, Nov 27 '17 at 17:47
Loosely related: [Display Blank when Referencing Blank Cell in Excel](https://superuser.com/q/515932/354511) — G-Man Says 'Reinstate Monica', Dec 08 '17 at 04:19

score 23 · Answer 1 · answered Nov 24 '17 at 12:27

23

The obvious answer is to put the formula in a work cell, away from the main sheet. For example, if you use H1, set it to:

=!X!

Your formula then becomes:

=IF(H1<>0,H1+A1,"")

This is typical of what one would do in any other programming language.

answered Nov 24 '17 at 12:27

AFH

17,300
3
32
48

I do not have possibility to add a help column. Thanks anyway. – Solarinoos Nov 24 '17 at 13:20
2

@Dio if it's not supposed to be seen then simply hide the column. You can even name the column/range to make it easier to read – phuclv Nov 24 '17 at 13:32
4

@Dio - OK. Your accepted answer is rather clever, but be aware that, because of rounding, you may lose accuracy (`1/(1/x)` is not always exactly the same as `x`), especially if you're dealing with integer data. It's also not applicable more generally (eg checking for less than zero). (Comment crossed with @LưuVĩnhPhúc's under accepted answer.) – AFH Nov 24 '17 at 13:36
Thanks for your advice and comments. I will leave the question still open to see if there is still new suggestions. – Solarinoos Nov 24 '17 at 13:44
2

It would be helpful to see the long formula. Besides a work cell, it is possible to define a name in a worksheet that contains a formula – datatoo Nov 24 '17 at 18:21
@AFH your comment regarding precision is a bit exaggerated, and the part about integers is wrong in Excel. See my comment in Gary's answer. – André Chalella Nov 25 '17 at 19:23
@AndréNeves - I did a few tests on integers, without seeing an error in the few tests I did, though I can't see a mathematical reason that it will always be so. A difference of 1.6E-16 _is_ significant, though: two cells differing by this amount will not test as equal. So tests like `=IF(cell1=cell2,...,...)` now need to be `=IF(ABS(cell1-cell2)<2E-16,...,...)`. In general people don't write this, and errors occur. – AFH Nov 25 '17 at 20:39
That does not mean it is significant. It only means it is a difference. As I think rarely one will need to compare two evaluations of a complicated `f(x)`, I deemed such difference *rarely relevant*. You are, of course, correct too, since this call is largely subjective, so I think there's no need to extend this discussion further. – André Chalella Nov 25 '17 at 20:57

Gary's Student · Accepted Answer · 2017-11-25T12:41:31.980

12

Another approach is to use double inversion:

=IFERROR(1/(1/really_long_formula)+A1,"")

If the really_long_formula evaluates to 0, you will get a divide by zero and the IFERROR() will catch it!

Please note that the usual way (and best way) to handle this requirement is with a helper cell.

edited Nov 25 '17 at 12:41

answered Nov 24 '17 at 12:33

Gary's Student

19,266
6
25
39

7

this may suffer from precision lost due to floating-point division – phuclv Nov 24 '17 at 13:31
3

@LưuVĩnhPhúc **You are completely correct!** the *AFH* approach is the best approach! – Gary's Student Nov 24 '17 at 13:33
This is a nice, dirty trick. I would refrain heavily from using it, but, as a last resort, who knows? – André Chalella Nov 25 '17 at 05:30
3

**NOTE:** it's true that there *may* be some precision loss, but it'd rarely be a relevant issue. I tested `1/1/x` with 100,000 random numbers several times in Excel, and only 1.4% of the time there was a rounding error. The maximum relative error I ever found was 1.6e-16, which is, well, effectively zero. *Also, interestingly, integers never displayed rounding errors* (tested several ranges from 0 to 1e14). So, this answer may be frowned upon from a purity/cleanness standpoint, but not from a practical calculation standpoint. – André Chalella Nov 25 '17 at 19:22
@AndréNeves Thank you for your careful analysis. – Gary's Student Nov 25 '17 at 19:24
As what I hope is a final footnote on precision, I realised that any loss of precision can be restored with the `ROUND()` function, whether the data are integers (`ROUND(value)`) or financial data (`ROUND(value,2)`) or any other values which need to be precise. You may want to note this in your answer, @Gary'sStudent - I have always admired its ingenuity. – AFH Nov 26 '17 at 10:21
1

I find the Gary's solution as the best. First because it is clean (as mentioned also by André) and readable by other users. Second, because it satisfies my need. I do not have complicated or long integers. I hope the users, who choose this approach, read the comments to be awared about the pros and cons of this solution. – Solarinoos Nov 27 '17 at 11:46

score 3 · Answer 3 · answered Nov 25 '17 at 02:34

Do you really need the result to be "" in the false case? If you only need the cell to look blank (e.g. you won't use something like =ISNUMBER() on it later), you can use conditional formatting to hide the contents in the false case.

The conditional format you'll apply to the cell so that it doesn't display anything is the custom format "", like this (it's in Portuguese but you get the idea):

The formula in the cell will be, as expected, simply =!X!+A1.

The conditional formatting formula could be =!X!=0, but that would force recalculation of !X!, which you don't want (your "Point 2"). It's better to harness the cell itself by using =B1=A1 (supposing our cell is B1) -- that would imply !X! = 0.

_{Even if you need the cell content to actually be "", usually minor alterations can be made in the worksheet so that this approach can be used. If that's the case, leave a comment describing the situation.}

wizzwizz4 · Answer 4 · 2017-11-25T18:40:18.200

I do not have possibility to add a help column. Thanks anyway.

If you can't add a helper column, why not add a whole worksheet? There are several advantages to this:

Your helper cell doesn't take up space that might otherwise be used, because it's in a separate worksheet.
You can name the cell then address it by name, e.g. =IF(X<>0,X+A1,"")
If you need to do this in more than one cell, you can:
1. Rename the helper worksheet "helper"
2. Put the helper equation in the same cell as the main equation (let's call it D5.
3. Address the cell as helper!D5 in the main sheet.
You can hide the helper sheet if necessary.
It is faster than evaluating twice.
It doesn't lose precision.

The disadvantages I can see are:

You have to reference unnamed cells in the main sheet as sheetname!D5 instead of just as D5.
Now the formula is in two parts instead of one.
Worksheet proliferation.

Weighing up the advantages and disadvantages, I think that for many use cases this is a good solution. There exist situations where it is not optimal, though I can't think of any at the moment.

Great answer. The disadvantages I see are: 1. It doesn't help *a lot* with making the calculation simpler to the user, since now the big formula is hidden away. 2. Many workbooks are already crowded with sheets. Even if you hide it, you may have to go through lots of hidden sheets to unhide it. Other than that, it's a good solution. — André Chalella, Nov 25 '17 at 16:54
@AndréNeves Thanks for that! That first one occurred to me, but I forgot it by the time I was writing the _disadvantages_ section. — wizzwizz4, Nov 25 '17 at 18:41

score 1 · Answer 5 · edited Nov 30 '17 at 03:04

an option not yet suggested is to create a user-defined function. You would need to turn on the Developer tab in the menu bar (google it) and create a module.

 public function udf_myCalc(ValueToAdd as double)
    dim myvar as double
    dim udf_myCalc as double
    myvar = .. put the logic of !X! in here

    if myvar<>0 then 
        udf_myCalc = myvar + ValueToAdd
    else
        udf_myCalc = ValueToAdd
    end if
 end function

In the formula bar you would then do

=udf_myCalc(A1)

NOTE: This now becomes a .xlsx (with Macros) file and may need additional permissions in a corporate network as macros can be used for malicious purposes and some email filters will block them. The functions are undocumented so you will need to provide notes on what your functions are doing and I find it useful to call all my functions udf_xxxxx so that it is clear that it is not a built in function

There are some other GOTCHAs with UDFs as well. See this link for some good tips http://www.decisionmodels.com/calcsecretsj.htm

Looking for solution to shorten an IF formula

5 Answers5