IF false Do Nothing

[philipad]

Hi all,

The IF function has the following syntax:

IF(logical_test,value_if_true,value_if_false)

My questions is,
If I don't want the function to take any action in the case of the logical test is false, what do I write in the "value_if_false" field?

 

[VoG]

Try

Sub test2()
Dim X As Variant, F As Range
X = Application.InputBox("Enter Value")
If TypeName(X) = "Boolean" Then Exit Sub
Set F = Columns("A").Find(what:=Format(Date, "mmm"), LookIn:=xlValues, lookat:=xlWhole)
If Not F Is Nothing Then F.Offset(, 1).Value = X
End Sub

 

[Kuzad]

Hii have a related question, so I have a series of numbers, and some are negative, and some are positive, i want excel to change the numbers that are negative to (-)####, for ex. A1=-874 and A2=942, i want a formula that will change the value of A1 to (-)874, but leave the value in A2 alone. so far i have this but it doesnt work:"=IF(A1<0,"-","")=REPLACE(A1,1,1,"(-)")" - Im trying to get it to do "if -, repalce with (-), if A1>0, then leave the value as is.

 

[Ahernandez04]

Hii have a related question, so I have a series of numbers, and some are negative, and some are positive, i want excel to change the numbers that are negative to (-)####, for ex. A1=-874 and A2=942, i want a formula that will change the value of A1 to (-)874, but leave the value in A2 alone. so far i have this but it doesnt work:"=IF(A1<0,"-","")=REPLACE(A1,1,1,"(-)")" - Im trying to get it to do "if -, repalce with (-), if A1>0, then leave the value as is.

Hi Kuzad,
Your question is similar to the original question. As previously stated, Excel can only return values if a condition is met, but it cannot simply "do nothing" or "leave as is". I definitely think this is something that should be worked on though.

 

[davidcullipher]

=if(a1<0,"-",a1)

 

[mikerickson]

This can be done with a UDF.

Function NoChange() As Variant
    NoChange = Application.Caller.Cells(1, 1).Text
    If IsDate(NoChange) Then
        NoChange = DateValue(NoChange)
    ElseIf IsNumeric(NoChange) Then
        NoChange = Val(NoChange)
    End If
End Function

Put 1 in A1, some value in B1 and in C1 the formula
=IF(A1=1, B1+10, NoChange())

Change B1 and C1 will react to that change.
Change the value in A1 and C1 will retain its current value and not react to changes in B1.

 

[cemx86]

I wanted to resurrect this old thread "if false do nothing". One of the suggestions was =IF(A1=1,"True",""), which I have used many times. The "" is referred to by Microsoft as "empty text". So if A1 <> 1 then the contents of the cell will be empty/nothing (""), right? Wrong!

Let's say I have 100 rows of this IF formula and they all return the false results ("") of empty text. Certainly I see a bunch of "empty" cells. But now lets say at the bottom I use =COUNTA(B1:B100) which counts non-empty cells, I should get a result of zero, right? Nope, I get 100. see COUNTA - Excel - Office.com web site which says that "empty text" cells are counted. You have to wonder why.

I have tried =IF(A1=1,"True",) which results in the cells showing "0" which is just as bad, maybe worse.

I sure wish there was a NULL() function in Excel, that might work.

Any ideas how I can truly get ABSOLUTELY NOTHING in the cell for a false IF result?

Thanks, John

 

[PenKa]

Well, it does kind of stink that there is no "If then DO NOTHING" option. However, the easiest way to just work right past this is to feed any unwanted else value to an irrelevant cell/worksheet.

It's not as sleek as a "Do Nothing", but it will get the job done. For example, =IF(A1=B1, A1, UselessSheet!$A$1="")

 

[RoryA]

It's not as sleek as a "Do Nothing", but it will get the job done.

Actually, no it won't. A formula can only return a value to the cell it's in, not populate another cell.

 

[PenKa]

Actually, no it won't. A formula can only return a value to the cell it's in, not populate another cell.

Hrrmm. After many years of using excel, I don't know how I could have missed that; did I always just work-around it without committing to memory? Either way, I just checked, and you are correct; it returns a false value to the home-cell.

Sounds like OP may just need to load a different column with the default (results of Do Nothing values) and have the formula reference those values to satisfy the else condition.

 

[adambomb]

I run into this occasionally when I need to transpose certain values. For example, I get a list of bank transactions both credits and debits in the same column. Due to the nature of the information, I can't sort, I have to use it as-is. For me a three step method works and is just as fast as anything I have ever tried.

Step 1. If my +/- values are in column A, and my "greater than zero" values need to be in column B, then this formula is pasted into column b: =if(a1>0,a1+50,"JunkTextString")

Step 2. Copy and paste the resulting values in column B as "Values." This should eliminate the formula while keeping the result of the formula.

Step3. Use find and replace to find "JunkTextString" and replace it with nothing. Just make sure the "replace" field is blank when you do you replacements. This way, you can "Replace All" and clean out the column of the data you don't want.

Then you can rinse and repeat. It is specific in nature and may not work if your requirement is to keep the formula in column B. If you need to formula in B, then you are in the same boat with this solution as the above solutions. Find and replace will only replace the text in the formule, and leave you with, in effect: =if(a1>0,a1+50,"")