edit a formula

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

edit a formula

donais
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: edit a formula

Brian Barker-2
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: [hidden email]
For additional commands, e-mail: [hidden email]