numbers functions help

NickB2708 wrote:

Does anyone know a quick way of being able to change the value in a formula multiple times without clicking in every formula to change it?

Here is a Find Replace in Formulas Automator Service (Dropbox download) that does what you describe. I made it a few years ago.

To use it you select cells the cells containing formulas you want to change, choose Numbers > Services > Find Replace in Formulas in your menu, and follow the prompts. It does not change cells that do not contain a formula.

To install it simply double-click the downloaded package, and if necessary give permission at System Preferences > Security & Privacy. Also make sure Automator.app is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

I suggest trying on a test document first to make sure it is doing what you want.

SG

select one cell in the column, then update the formula.

select the cell again (after typing the return key to finish editing the formula).

copy

select the column of cells, paste

If it's more complicated than this, then post specifics about your formula for a more specific question.

Everyone here is very friendly and helpful

Best

If you need to do this often or if that value is a parameter/variable that is common to a bunch of formulas, perhaps that value should be a cell reference vs hard coded into the formulas. This might be a good time to make that change.

As a general statement, it would not be a simple thing for the app to automatically update all the other non-identical formulas based on the change made to one formula, nor would you want that to happen usually.

Formula A = C2*5

Formula B = (C2-5)*5

If you change the "5" to a "6" in formula A, do both 5's get changed in formula B or just one (and which one) or do you not want it to change formula B at all?

But if cell D2=5 and

Formula A =C2*D2

Formula B =(C2-D2)*5

then it would "update all the formulas" whenever you change the value in cell D2.

To your question ,though, I cannot think of an easy way to update a number in a bunch of dissimilar formulas, except one at a time. I thought about using the FORMULATEXT formula in a new column, then copy / paste formula results to turn them into actual text, then find/replace to change the value in each, but then you have to copy/paste each "formula" one-by-one back to its matching cell to overwrite the old formula. Unless there are a lot of them, this entire procedure is just as much work.

It might be possible to do something with Applescript but I feel the time it would take to write and test the script would be longer than the time it takes to do it manually.

SGIII wrote:

Here is a Find Replace in Formulas Automator Service (Dropbox download) that does what you describe. I made it a few years ago.

Great script!

I have been searching exactly for this.

Thank you!

how many formulas are you talking about?

I think that, like, Badunit, I suggest making the common value be some cell, then you can change that cell value