Conditional formula calculation of a column

Is it possible to have a formula in the column that only calculates the value of the column in case a certain criteria is met, otherwise it lets you add the value by yourself?

I have often desired that exact feature. Currently, as far as I understand, the simplest solution is to use the Value for new rows formula.

Obviously that only works for new rows, so if you need it on existing rows there are a few work-arounds, all with caveats:

  • Automation: create a rule that modifies the row(s) in question whenever the criteria are met. Only problem is that this option is slow and users may be surprised when their values are over-written suddenly.
  • Use two columns: one column will be user-editable, and the other will be a formula that looks something like this:
    If([criteria], [calculated value], [user-inputted value])
    Then you have to figure out how to display it nicely to the user.
    (You may consider adding conditional formatting to, for example, color the text of the user-editable field white (ie invisible) when criteria are met.)