[Solved] Reverse "FORMULA" function

[Arthur Lindley]

Is there a function which acts to reverse the action of "FORMULA"? I want to automate a process of editing specific individual formulas in a large and fairly complex spreadsheet. To do this, I envisaged using "FORMULA" to convert to text, "REPLACE" and other text formulae to edit, and then "****" (which is what I can't find) to convert the new text string back to a formula to put into the relevant cell. Have I just missed it, or is there no such function?

Thanks

Arthur

[Zizi64]

Hi,

If I understood xour problem exactly:

No way to change a function name in a formula by an another formula, but
you can use the INDIRECT function for editing/changing a reference in an existing formula.

(workaround tips:

You can use all of desired functions in some cells, and you can choose one of the calculated results, by an another formula.
or you can create an user defined Macro/Basic function using Case directive for select the desired calculation methode.
or You can change a function name in formula by a Basic macro.
or you can "Copy-Paste as unformatted text" for convert an edited text (by formulas). Result: the edited text becomes a working formula.)

[acknak]

I want to automate a process of editing specific individual formulas...

Just to be clear: the answer is going to depend on exactly what changes you need to make. You'll have to provide a concrete example of what you need to do.

Basically, you can't change the operation to be performed (the function name, as already mentioned), but you can change what cells the operation applies to, or the inputs to the operation. The best approach is not to try and "edit" the formula as you normally would, but to modularize the formula to combine different expressions under different situations.

[Arthur Lindley]

Thanks for information so far, but the problem remains. I'll try to explain in more detail:

I start with a very large raw data array (output from solar panels, recorded every 5 mins of every day, for 3 months so far). To try to make sense of all this, I have created a sheet which tabulates the average output for each 5-min time point (=AVERAGE(....)) for each weekly period: that's quite a lot of "=AVERAGE" formulae to type in. What I would like to do, without incurring permanent wrist damage from endless typing, is to create what would be more or less replica sheets, but using "=MAX" and "=MIN" for each of these.

So I do want to change the function - for which clearly "-INDIRECT" doesn't work. It may be that in the end I just have to go back to typing in - but if I can use some form of automatic replacement it would sure be a lot better.

Cheers

Arthur

[Villeroy]

Most fundamental spreadsheet basics since 30 years: [Tutorial] Absolute, relative and mixed references

[acknak]

What I would like to do, without incurring permanent wrist damage from endless typing...

If this is the road block you're worried about, you're on the wrong road. There's no reason you have to type such formulas more than once, then copy that to all the other cells. If you set it up properly in the first cell, the copies will all work correctly as well--this is what the tutorial on references will help you with.

If that doesn't work for your situation, then I think we'll need to understand why before we talk about more complicated approaches.

Providing a small sample sheet might enable a more helpful discussion. You can use the "Upload Attachment" link (below the message entry area after you click "POST REPLY"). For tips on posting large or confidential documents, see: [Forum] How to attach a document here

[keme]

Depending on the nature of your data, spreadsheet tools such as scenarios, multiple operations or data pilot might help (perhaps in some combination). You may need to restructure your data to use those tools.

As acknak also indicated, it's nearly impossible to provide more specific advice without some knowledge of what we are advising about.

[MrProgrammer]

Is there a function "****" which acts to reverse the action of "FORMULA"?

Let's consider this a bit. =FORMULA(A1) is used to get the formula for cell A1, so presumably **** would somehow set the formula for that cell. A fundamental principle is that each non-empty cell contains either data or a formula. The formula calculates a result which becomes the value of the cell containing the formula. The formula affects only the value of the cell which contains it, and never changes the content of any other cells.

So if there's some formula in A1 that we want to manipulate we can use =FORMULA(A1) in B1 to get the formula's text and =REPLACE(…) or =SUBSTITUTE(…) or whetever in C1 to make a new formula. But where could we use ****? Not in C1 because C1's formula can never affect the content in another cell and specifically cannot affect the formula in A1. And not in A1 because that **** function would replace the very thing we want to manipulate. So **** is not possible (without programming a macro using the API).

Although a function can't set a cell's formula, we could have a function (presumably called EVAL) which evaluates a text string the same way that the formula processor would. If so, =EVAL("1+2") would be 3; or with A1 containing "SUM", =EVAL(A1&"(C4:C6)") would sum the numbers in C4 through C6. However neither Calc nor any spreadsheet I'm familiar with has implemented an EVAL function.

What I would like to do … is to create … replica sheets, but using "=MAX" and "=MIN" for each of these.

In a typical spreadsheet where we put labels in row 1, write formulas in row 2, and then fill the formulas to the remaining rows, it's just a matter of updating the formulas in row 2, highlighting those formulas, and double clicking the fill handle to propagate them to the remaining rows. As others have mentioned, for this to be successful the formulas in row 2 have to be written with the correct relative, absolute, or mixed cell references. I also have a dicsussion of that important subject in section 8. Using formulas and cell references of Ten concepts that every Calc user should know.

In more complicated situations, it can be helpful to modify formulas with Edit > Find & Replace > More options > Search in > Formulas. This allows you to replace AVERAGE with MAX or MIN and quickly convert =AVERAGE(A2:A9) to =MAX(A2:A9). This might be what you're looking for.

For even more complicated situations, you can temporarily convert your formulas to text: Select the cells, Edit > Find & Replace > More options, select Current selection only and Regular expressions, Search for ^=(.+), Replace with #$1, Replace All. I recommend unchecking Current selection only and Regular expressions, before clicking Close. This converts =AVERAGE(A2:A9) to #AVERAGE(A2:A9), which can now be manipulated as text. So if the formula to mainpulate is in A2, we convert it to text in A2 and perhaps use =REPLACE(…) or =SUBSTITUTE(…) to build a modified formula in B2. Then copy B2 and use Edit > Paste Special > Text (unselect Formulas) to update A2 by converting B2's formula value to text. Then Edit > Find & Replace > More options, select Regular expressions, Search for ^#(.+), Replace with =$1, Replace All to convert back from text to formulas.

I have created a sheet which tabulates the average output for each 5-min time point … for each weekly period

I would agree that this seems like something that Data Pilot could help with. Beginners often struggle for hours writing formulas to accomplish tasks which could be done in minutes, without formulas, using DataPilot. Data Pilot could present all three statistics (average, minimum, and maximum) in the same report. There are many posts about DataPilot in this forum and if you're having trouble with it a search will probably find the answer for you.

[JohnSUN-Pensioner]

Caution! This message may confuse Arthur Lindley and other newcomers. I am writing for a guru. This text is not directly related to the topic!
Dear colleague MrProgrammer! I have called into question your words

The formula calculates a result which becomes the value of the cell containing the formula. The formula affects only the value of the cell which contains it, and never changes the content of any other cells.

Because I know that it is not. I mean, that the calculation of the value of the formula can not change the value of any cell on the same sheet, on current sheet. With this clarification of your definition of a completely accurate.

Edit: PS. Or "A formula consisting only native Calc functions affects the value of the cell..." Oh! My English...

[Arthur Lindley]

Thanks to everyone, and apologies for my less than extensive knowledge. Now it's clear that what I had in mind wasn't possible - but there are various alternative routes to achieve the same, which also make more sense now I see rather more.

From my point of view, it's been very instructive & helpful, & I the thread can be closed