Universal IF statement - to meet needs of IFNULL, IFBLANK, IFZERO etc.
Are you tired of duplicating complicated formulas within an IF statement in order manage blanks or zeros?
e.g. IF(long formula="","",long formula)
A universal IF statement (lets call it IFX) would decrease this type of duplication without having to add multiple new IF formulas the likes of IFNULL, IFBLANK or IFZERO.
An IFX function would accept the condition you want to test the formula against, if the condition is true it would return the specified alternative (such as blank). If the condition is false it would return the value of the formula itself. Ideally it would be able to accept multiple conditions.
IFX(condition,value if true,(condition2,value if true2...),formula)
eg: IFX(0,"",SUM(A1:A3))
If the sum of A1:A3 is 0 the result will be blank ("") otherwise the result will be the value of the sum.
Obviously this is very simplified example. IFX would more useful in more complicated situations. One I come across all the time is when a lengthy IndexMatch formula returns a zero but I want the cell to be blank instead. These types of formulas can end up being 2-3 lines long by the time I duplicate the IndexMatch to test for the zero.
Carole
shared this idea
12 comments
-
Tom
commented
For simple situations I have just used =iferror( FORMULA /1,"") and to get rid of the pesty "1/0/1900" for formulas related to dates with 0 as the result my son came up with =iferror(1/ FORMULA ^-1,"") which works great.
BUT, we need this idea as something simpler to use. "I vote YES!" -
Anonymous
commented
This would be great for readability and maintenance!
Duplicating long formulas makes thing so much cluttered, hard to read and prone to mistakes!
This would be so useful for a large number of use cases where you want the result of a long formula unless X, Y, Z...
IFX() would be great and IFXS() would be amazing
(but I must admit I'm not sure about the argument order... maybe beginning with the formula would be easier to read... maybe not) -
lupo1
commented
I worked around for 2 of them:
IFNULL: =IFERROR(1/(1/A1),"")
IFNULLORNEGATIVE: =IFERROR(EXP(LN(A1)),"")
The 2nd one is a nice replacement for an arrayable-MIN and -MAX, too (if using a comparison of only 2 arrays). If you want to MIN-MAX n arrays, you stay with the old solution ...SUBTOTAL(...;OFFSET())...
-
Stevenson Yu commented
@kalecka: It won't. The formula will simply return LongFormula for all FALSE values.
For example:
IF(LongFormula="","",IF(LongFormula>=B3,"Not Applicable",LongFormula))
will just become
IFX(LongFormula,"","",">="&B3,"Not Applicable")
-
kalecka
commented
To Stevenson Yu: Unfortunately, your proposal is not solving the main problem, as we want to avoid using LongFormula more than once, and in your idea it will have to be repeated in Value if True part to work correctly.
As well as Richard, I think that ms@baccma's idea is great and could be a big step forward not only for IF statements, but much more excel calculations.
-
After mulling it over, I propose a slight correction to Carole's proposal.
Since condition 2 and thereafter are optional, this should be implemented just like the plural version of the arithmetic IF functions.
IFX(LongFormula, Condition1, Value if True, Condition2, Value if True, ... ConditionN, Value if True)
-
Richard Wein
commented
ms@baccma's suggestion is good too.
-
Richard Wein
commented
Great idea, Carole. I was struggling with this just yesterday, trying to to find a way to make my exception condition return an error, so I could use IFERROR. I failed, so had to duplicate my long expression.
Alternative name suggestion: IFSHORT. It's really a general-purpose function, like IF, except that it restricts the condition to one that tests the value of one of the two alternative output expressions. It could allow tests for non-equality, e.g. ">0" (in quotes) for the first argument.
-
ms@baccma.co.uk
commented
A more comprehensive solution to this, which I have suggested somewhere else here would be to be able to label formula fragments so rather than say:
IF(AA120+SIN(AB120)>AC120,AA120+SIN(AB120),0) you could write:
IF(test:(AA120+SIN(AB120)>AC120),test,0) -
kalecka
commented
Wow, I`m happy to see that more people have the same problem with duplicating long formulas while checking particular condition. I have a feeling that this improvement will speed up calculation a lot, and make formulas more clear for editors.
I spend a while to create a vba alternative function to solve this, but I think it would be better implement globally by professionals :).In case it will note happen in the near future, please see below my alternative:
Function IFTRUE(form, comp, ift) ''''' IFTRUE(statement, condition, statement if true)
If form.Count > 1 Then
IFTRUE = CVErr(xlErrRef)
Exit Function
End IfDim compL As Byte
'If IsNumber(form.Value) Then
If Mid(comp, 2, 1) = "=" Or Mid(comp, 2, 1) = ">" Then
compL = 2
ElseIf Left(comp, 1) = "<" Or Left(comp, 1) = ">" Or Left(comp, 1) = "=" Then
compL = 1
Else: compL = 0
End If
Select Case IIf(compL > 0, Left(comp, compL), "")
Case "<"
If form.Value < Right(comp, Len(comp) - 1) Then
IFTRUE = ift
Exit Function
End If
Case ">"
If form.Value > Right(comp, Len(comp) - 1) Then
IFTRUE = ift
Exit Function
End If
Case "<>"
If form.Value <> Right(comp, Len(comp) - 2) Then
IFTRUE = ift
Exit Function
End If
Case "<="
If form.Value <= Right(comp, Len(comp) - 2) Then
IFTRUE = ift
Exit Function
End If
Case ">="
If form.Value >= Right(comp, Len(comp) - 2) Then
IFTRUE = ift
Exit Function
End If
Case "="
If form.Value = Right(comp, Len(comp) - 1) Then
IFTRUE = ift
Exit Function
End If
Case Else
If form.Value = comp Then
IFTRUE = ift
Exit Function
End If
End SelectIFTRUE = form.Value
End Function
-
A.C. WILSON
commented
Great idea, Carole! I frequently have had the same situation as Carole has, when I'm using INDEX(MATCH()...) formulas. I already use IFERROR() for situations where the invalid result is an error, but that doesn't cover all of my situations.
This proposed IFX() would actually generalize the specific case now addressed by IFERROR(). IFX() would manage a default response to any one specific "invalid" value that the user defines.
Right now, IFERROR() only allows the "invalid" value to be whatever Excel defines as a formula error. I note, however, that IFERROR()'s arguments are not very intuitive. [Others have previously commented that Microsoft's implementation of this functionality, especially the actual name "IFERROR", is not very intuitive.]So, I suggest an alternative format for IFX():
GET-VALID-UNLESS(<get,-test,-and-use-result>,<unless-result-equals-this-invalid-value>,<alternative-result-instead-of-invalid-value>[Other possible names for the proposed function: "IF-GET-VALID()"; "IF-GET-ELSE()"; "USE-UNLESS()"; "OK-UNLESS-BAD()"; "WONDERFUL-EXCEPT-WHEN()", ...]
A more complex enhanced function would allow for several different "invalid values", each with its own alternative alternative result:
GET-VALID-UNLESS(<get,-test,-and-use-result>,
<unless-result-equals-this-invalid-value-1>,
<alternative-result-1-instead-of-invalid-value-1>,
<unless-result-equals-this-invalid-value-2>,
<alternative-result-2-instead-of-invalid-value-2>,
...
<unless-result-equals-this-invalid-value-N>,
<alternative-result-N-instead-of-invalid-value-N>)[which in turn begs the question of why Excel formulas lack a "Select...Case..." functionality].
-
I want this formula. It will likely result in more efficiency since you only need to evaluate LongFormula once instead of twice.