CODEWITHSUNDEEP
excelexcel-formula
PeanutsMonkey
PeanutsMonkey

Reputation: 7095

Is there a way to skip a formula if a condition is not met in Excel?

I have a list of values that i am comparing and if it results in a "Yes", the function returns a value.

I would however like to skip the function if the value is "No" e.g. the function doesn't execute and it simply leaves the cell blank as this requires manual input.

Is there a way to achieve this in Excel?

Upvotes: 0

Views: 13633

Answers (2)

Roberto
Roberto

Reputation: 2786

You could calculate the result in another cell. Make the formula with an IF statement just like Alexandre Lavasseur wrote,
=IF(condition, your_calculation, "")
Make the cell hidden, or just use the color white for the text.
Now add a button with a very simple macro (i.e. call the button "Calculate!") that would just copy the result from that cell (as value!) and paste it to the "official" result cell. This way when the button is activated, if there is any value to store in the cell, it will be pasted. If there isn't, the cell will still be blank and ready to accept a "manual" value.

Edit: like in this example:

http://www.sendspace.com/file/eq3vuy

(the button will "write" a value in B9 only if the number of libraries is above 0).

Assuming that you have a value for "Books" in B4, "Libraries" in B5, and that you hide this formula in H5: =IF(B5>0;B4/B5;"")

Then the macro: Sub Calculate() ' ' Calculate Macro '

'
    Range("H5").Select
    Selection.Copy
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Will take any value from H5 and paste special (values) to B9, where you'd have the "books per library". Assign this macro to a button.
If the value for libraries in B5 is not positive, then the macro will paste the empty value "", i.e. it will clear the cell. You can also have it so that the macro leaves the cell as it is, but I wouldn't recommend it (the user might think the macro "agrees" with the value in B9 while it's just leaving it there).

Upvotes: 2

ApplePie
ApplePie

Reputation: 8942

You can use an IF() and return an empty string but it will not be exactly the same as leaving the cell blank since it will actually contain a formula. You will however be able to overwrite that with your manual input if it's ok with you.

=IF(somecondition = "Yes", somevalue, "")

Upvotes: 1

Related Questions