edit a formula

[donais <do...@telupton.com>]

I have a huge spreadsheet and have to change a value in all formulas

formula are like this:
each columns are this style 'a' identify a column    '2-4-5-6' etc are
the lines

=sum(a3*e3)+(b4*e4)+(a5*e5)+(a6*e6)+(a7*e7)+................. and so on
untill (zza256)

I have to change 'e' for 'b' in about a hundred colo]umns and 600
hundred lines.
next row is to change 'f' for 'b' ond so on.

Is there an automated edit solution that can avoid me to do it by hand?

Thank's to care.

  --
Michel Donais

-- 
Michel Donais


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org

Re: edit a formula

[Brian Barker <b....@btinternet.com.INVALID>]

At 19:21 01/07/2018 -0400, Michel Donais wrote:
>I have a huge spreadsheet and have to change a value in all 
>formulas. formula are like this: each columns are this style 'a' 
>identify a column '2-4-5-6' etc are the lines

Er, that's how spreadsheets are!

>=sum(a3*e3)+(b4*e4)+(a5*e5)+(a6*e6)+(a7*e7)+................. and so 
>on until (zza256)

Let's start there: that's not a sensible formula. I'm guessing that 
you think the SUM() function is necessary because of all those 
additions you need. But no: you can *either* use plus signs *or* else 
use the SUM() function: it makes no sense to try to use both. In any 
case, as it is, your SUM() function encloses only the first 
parenthesised expression, so you are asking SUM() to add just A3*E3. 
But you cannot add a single item: you need at least two for that! In 
addition, all those parentheses are unnecessary (and therefore 
confusing), since multiplication precedes addition in mathematical 
priority. You can sensibly express this formula in one of two ways, either:
=A3*E3+B4*E4+A5*E5+A6*E6+A7*E7+...
or:
=SUM(A3*E3;B4*E4;A5*E5;A6*E6;A7*E7;...)

>I have to change 'e' for 'b' in about a hundred columns and 600 hundred lines.

Er, sixty thousand rows?

>... next row is to change 'f' for 'b' and so on. Is there an 
>automated edit solution that can avoid me to do it by hand?

You can use Edit | Find & Replace... (or Ctrl+F) to edit formulae. 
Click More Options and ensure Formulae is selected for "Search in". 
It may help to tick Tools | Options... | OpenOffice Calc | View | 
Display | Formulae - very probably temporarily - so that you can see 
the formulae that you are editing.

But it is a Very Bad Idea to edit large numbers of presumably related 
formulae manually and individually in this way. It is impossible to 
enter large numbers of formulae without introducing errors - and it 
is generally not possible to spot such errors. Even if you made no 
mistakes, you could not be sure that this was so. Instead, you should 
create an appropriate formula once and use it to fill cell ranges, 
either by pasting or by dragging the fill handle or by using Edit | 
Fill. You may need to enhance any formula slightly to ensure that it 
becomes modified appropriately (or not) as it is used to fill ranges.

If it doesn't appear possible to do this, then you almost certainly 
have not designed your spreadsheet sensibly. A little time spent on 
design may well render the sort of editing you describe unnecessary.

In any case, why do you need to make such changes? If you need to 
insert additional rows or columns, your formulae should adjust 
themselves automatically to keep their functionality. Again, if not, 
you may need to insert your new rows or columns differently.

It is very likely that your real solution will be redesigning how 
your spreadsheet works, not tinkering with countless individual formulae.

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org