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):
![custom format for blank cells](../../I/static/images/6e3e07c1eceecaed220e6cf5dc88969955f674a29b9bb1113f15428ec5d922d2.png)
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.
1if 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 – 2017-11-24T13:35:03.2272Just curious too, have you "golfed" the long formula down as much as you can? To make it shorter maybe add named ranges? – BruceWayne – 2017-11-25T00:52:20.267
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 – 2017-11-27T17:47:14.687
Loosely related: Display Blank when Referencing Blank Cell in Excel
– G-Man Says 'Reinstate Monica' – 2017-12-08T04:19:20.577