Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jen
 
Posts: n/a
Default how to change a formula for a large group of cells

I have created formulas for a very large matrix of cells represented below
such that (sheet1):
B10 = A1-A1 C10 = B1-A1 D10 = C1-A1 .......
B11 = A1-B1 C11 = B1-B1 D11 = C1-B1 .......
many more rows and columns:)

Although these formulas are correct - they each needed to be typed in by
hand. To do the analysis of error propagation on the data the formulas will
use the same cell numbers in question, however, the formula will change such
that as per given example (sheet 2)

C10 = ((B1^2)+(A1^2)) ^(1/2)

Is there a method for editing the formula only for a mass group of cells
while keeping the cells they refer to the same?

It is not possible with the matrix layout to autofill - and editing each
individual formula is very tedious with large amounts of data.

Also for future reference: is there an autofill for the single cell
increase while the other remains constant as in row 10 in the example above?

Thanks,
Jen
  #2   Report Post  
Ron Moore
 
Posts: n/a
Default

I don't know of any way to do a "mass edit" as you're suggesting. However,
you can rewrite your formula so that autofill can be used. For the first
case, In B10, enter

=OFFSET($A$1,0,COLUMN()-2)-OFFSET($A$1,0,ROW()-10)

and fill this down and to the right to cover your range.

As you can probably see, in the second case, a possible formula for B10
would be

=SQRT(OFFSET($A$1,0,COLUMN()-2)^2+OFFSET($A$1,0,ROW()-10)^2)

If for reasons of comprehensibility you want to keep the formulas "looking
like" you have them now, a macro could be used to set the formulas in your
range of interest. If you want to pursue this and need help, reply back.

As for your second question, that's easily done using a combination of
relative and fixed column addressing. In B10:

=A1-$A1

Then fill to the right.

"Jen" wrote:

I have created formulas for a very large matrix of cells represented below
such that (sheet1):
B10 = A1-A1 C10 = B1-A1 D10 = C1-A1 .......
B11 = A1-B1 C11 = B1-B1 D11 = C1-B1 .......
many more rows and columns:)

Although these formulas are correct - they each needed to be typed in by
hand. To do the analysis of error propagation on the data the formulas will
use the same cell numbers in question, however, the formula will change such
that as per given example (sheet 2)

C10 = ((B1^2)+(A1^2)) ^(1/2)

Is there a method for editing the formula only for a mass group of cells
while keeping the cells they refer to the same?

It is not possible with the matrix layout to autofill - and editing each
individual formula is very tedious with large amounts of data.

Also for future reference: is there an autofill for the single cell
increase while the other remains constant as in row 10 in the example above?

Thanks,
Jen

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What formula is used for subtracting a range of different cells f. tim Excel Worksheet Functions 3 April 21st 23 10:07 PM
vlookup on large text in cells Gus Excel Worksheet Functions 2 February 23rd 06 07:55 PM
quick way to copy-paste a formula linked to cells in another file iniakupake Excel Worksheet Functions 2 September 26th 05 03:56 AM
How to change a formula in one cell and have these changes appear Sam Excel Worksheet Functions 3 May 18th 05 10:57 PM
How to change a formula in one cell and have these changes appear Duke Carey Excel Worksheet Functions 1 May 17th 05 06:15 PM


All times are GMT +1. The time now is 04:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"