Forum Discussion
IFTRUE() formula that does nothing if False
I want a built-in Excel worksheet function named IFTRUE() { IFTRUE(logical_test, value_if_true) } that is similar to the IF() function, except that it performs an action only if the test condition is True. However, it should do absolutely nothing (takes no action) if the condition is not True and should not contain a value_if_false parameter - not even an optional one.
Using the formula IF() formula, such as IF(1+2=3, "True", ""), does not work for my purposes because it a writes an empty string to the target cell when the condition evaluates to False. Using the slight alternate of IF(1+2=3, "True") without the value_if_False parameter also does not work because this one writes a blank value, a zero (0), or "FALSE" to the cell if the condition evaluates to False. The reason this does not work is because assigning any value at all is not the same as doing absolutely nothing to change the value of the target cell. This is the whole point for this function because performing any action at all upon the taget cell when the condition evaluates to False imposes negative consequences for numerous other things I am trying to do within the worksheet. Even if it merely gives the cell a blank value or a value of 0/False/"", the IF() function still results in some action on the target cell, and I want it to do nothing at all if the condition parameter evaluates to False.
If the condition parameter of the IFTRUE() function equates to 'False' I do not want:
- A blank value written to the target/currently selected cell.
- A value of zero (0) written to the target/currently selected cell.
- An empty string value ("") written to the target/currently selected cell.
- A triggered Worksheet.Change() for the target cell.
If the conditional parameter evaluates to a value of False, simply exit the IFTRUE() function immediately and without doing ANYTHING else!!
The current IF(logical_test, value_if_true, value_if_false) function equates to the following Visual Basic conditional structure:
Using IF(1+2=3, "Evaluates to True", "")...
If 1+2=3 Then
Range("A1").value = "Evaluates to True" 'Or some other command.
Else
Range("A1").value = "" 'Or 0, or "FALSE"... depending on the cell's formatting.
End If
I want my IFTRUE() formula to do absolutely nothing at all if the condition parameter evaluates to False.
So, my IFTRUE(logical_test, value_if_true) formula would, instead, equate to the following Visual Basic conditional structure:
If 1+2=3 Then
Range("A1").value = "Evaluates to True" 'Or some other command.
Else
Exit Sub
End If
Or...
If 1+2=3 Then
Range("A1").value = "Evaluates to True" 'Or some other command.
End If
This is somewhat similar to a Trilean custom data type I created to replace the limited Boolean data type:
Rather than always being limited to the following built-in Boolean data type:
Public Enum Boolean 'Indicates whether a data member, routine, property, event, event handler, or conditional statement evaluates to a Boolean state of True or False.
True = (-1) 'Equates to True, yes, on, or positive.
False = 0 'Equates to False, no, off, or negative.
End Enum
I created and often use the following custom data type:
Public Enum Trilean 'Indicates whether a data member, routine, property, event, event handler, or conditional statement evaluates to an extended Boolean state of True, False, or neither.
Aye = 1 'Equates to True, yes, on, or positive.
Nay = (-1) 'Equates to False, no, off, or negative.
Nil = 0 'Equates to Neutral, nothing, null, undefined, uninitialized, or undetermined. Neither True nor False. Neither On nor Off.
End Enum
Very often I need to distinguish whether a property, a function, or a variable was referenced or initialized and it actually returned a value of False, or if it has not yet actually been referenced or initialized at all. So, using a 'False' value to represent both an actual returned value of False and to indicate that the member has not yet been referenced or initialized just doesn't cut it for me - because they are 2 different things and I need to know specifically which occurred. Likewise, just as a Boolean data type sometime needs a third "neither" or "neutral" value, so too, does an IF() function sometimes needs a companion IFTRUE() function that does nothing when the condition evaluates to False, or rather, it performs an action only if the condition evaluates to True.
An IFTRUE() function is easy enough to achieve using VBA. However, the IT departments at many, many companies (like my employer) block all macros and VBA within Microsoft documents. Also, allowing and using circular references in Excel at work is not an option. So, an IFTRUE() formula would very easily solve this issue. And since I've seen numerous others ask about accomplishing the same thing in other forums, I know others have a need for this feature, as well.
EDIT: This formula can also evaluate to True for intentionally false (inverse) condition values, thereby eliminating the need for an additional IFFALSE() function: IFTRUE(1+2<>5, "This also evaluates to True") or IFTRUE(NOT(13+2=7), "This also evaluates to True")
This was meant to be a suggestion for a new feature in a future version of Excel. Since Microsoft changes links for suggestions like I change my own underwear, this is where the latest link sent me to make a feature suggestion. As far as it being possible... that is actually the whole point of suggesting the feature - to make it possible. It is not currently a feature but it can become a feature. And yes, Microsoft's programmers should have the ability to include such a formula within future versions of Excel without user's having to resort to VBA. If they can't, then Microsoft needs to hire more imaginative and more capable developers.
7 Replies
- Patrick2788Silver Contributor
A NULL function has been suggested but does not appear to be in the pipeline at the moment.
(Link includes a response from Microsoft to the suggestion)
- GypsyPrinceCopper Contributor
From the proposals I've seen for the Null() function, that does not meet my needs either because in assigning a value of null to the cell the function in the event of a False result, it is still performing an action. I do NOT want the function to perform ANY action if a False result occurs. Nothing... nada... zip... zilch. A NULL() function is counter-productive to what I (and others) need. We don't need a null or empty value. We want the function to immediately end without assigning any value to (OR MAKING ANY CHANGE AT ALL) to the cell in the event of a False result to the condition.
That is not possible. A function always returns something.
As far as I know, this is not possible. A worksheet function ALWAYS returns something to the cell it is in, even if only the empty string "".
The custom VBA function
Function IFTRUE(condition As Boolean, value_if_true As Variant) As Variant If condition Then IFTRUE = value_if_true End If End Functionwill return 0 in a cell if the condition is FALSE.
- GypsyPrinceCopper Contributor
This was meant to be a suggestion for a new feature in a future version of Excel. Since Microsoft changes links for suggestions like I change my own underwear, this is where the latest link sent me to make a feature suggestion. As far as it being possible... that is actually the whole point of suggesting the feature - to make it possible. It is not currently a feature but it can become a feature. And yes, Microsoft's programmers should have the ability to include such a formula within future versions of Excel without user's having to resort to VBA. If they can't, then Microsoft needs to hire more imaginative and more capable developers.