CODEWITHSUNDEEP
excelexcel-formula
athos
athos

Reputation: 6425

excel formula to read from a volatile function?

I have a volatile function say =getprice() in cell A1, that may succeed, returning a price, or fail, returning #REF!. Is there a way to write a formula in B1, that read from A1, if succeed, get the value, and if failed, keep last round value?

Upvotes: 4

Views: 212

Answers (2)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5205

I realize this is a late response but here is another alternative that doesn't precisely answer your question, but still, I think it is useful information:

I recently discovered that data-validation drop-down lists are always non-volatile, even if you select a volatile value from the drop-down. The value chosen from the drop-down list will remain in the cell indefinitely until you re-choose from the drop-down again.

In your case, in cell B1, you can select data validation and set it up with these settings:

enter image description here

This way, cell A1 will constantly be updating, and whenever you want to "capture" this value in A1, you just manually select the only available option from the drop-down list in cell B1, which will store that value in B1 indefinitely until you manually select from the drop-down list again.

Again I realize this doesn't precisely answer your question because cell B1 doesn't automatically update, but this method has two major advantages:

  1. It allows Excel to essentially have memory and look at past events (something that could otherwise not be accomplished).

  2. It essentially converts a volatile cell to a non-volatile cell.

Converting to a non-volatile cell is in general particularly useful for making your spreadsheet more efficient. For example in cell A1 you could have the volatile function = NOW() and then use this method to snapshot copy this value into cell B1, and all cells in your workbook that need NOW() in the formula can be based off of cell B1 instead of A1, thus having potential to drastically improve calculation times since B1 is non-volatile.

Upvotes: 3

pnuts
pnuts

Reputation: 59495

If you turn on Enable iterative calculation with Maximum Iterations set to 1 then:

=IF(ISERROR(A1),B1,A1)

Upvotes: 3

Related Questions