copying fomula

  • Thread starter Thread starter daz
  • Start date Start date
D

daz

Hi Does anyone know how i could copy or fill down this formulae =A2*B1
written in B2 to the cells B3, B4 and so on? The two variables are only A1
and B2. I can do this the normal copy and paste way but wondered if there
was a much quicker way to show children in my class at school.

Many thanks

Daz
 
Yeah tried that. thanks though.

If i do it that way the formula in B2 which is =B1*A2 is copied to B3 as
=B2*A2 and to B4 as =B3*A4 but I need B2, B3 etc always to be B1. I don't
know if I've explained that very well. Does that make sense?

Cheers

Daz
 
THat's great it worked thanks very much!

Is there also a way of doing the same thing across a row?
The same command didn't seem to work for some reason?
Maybe it's just me

Cheers

Daz
 
Tip:
If you are changing your formula then you can use the F4 key
Excel add the $ for you then ( i am lazy)
 
No luck there Ron. thanks though.
Your first formula is very useful i will be able to teach that.
I'm trying to get the children to design a multiplication grid where the
column and row headers multiply. But want them to find the easiest way
rather than entering 100 like formulae.

Thanks again

Daz
 
No luck there Ron. thanks though.

select a formula cell
Press F2 (you are in Edit mode)
Use the F4 key with your cursur in a address
 
FWIW,

If you select the *entire* formula in the formula bar, then a *single* hit
of F4 will change *all* the cell references at the same time.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Brilliant! Thanks Ron that'll make life much easier.
Wish I knew Excel that well.

Daz
 
Thanks very much guys it works perfectly it'll be much quicker to format
too.
Just hope none of the kids ask me to explain it! :-)

Daz
 
Explanations are in the references, but to start with:.

ROW() is the current row number
COLUMN() is the current column number

ROW()-1 is the current row number less one
COLUMN()-1 is the current column number less one
=(ROW()-1) + (COLUMN()-1) is the number in each cell for the addition table
=ROW() + COLUMN() -2 is equivalent to the above

$A2 as you fill across the $A reference to column A remains the same
as you fill down the reference to column A remains to same as would anyway.
2 as you fill across the 2 reference to row 2 remains the same
as you fill down the row adjusts accordingly, each row down is one higher

Similar for B$1 except that is the row which is being maintained as absolute.

Still not clear read the references to Absolute and Relative referencing.

the topic in HELP is:
The difference between relative and absolute references
Chip Pearson's page is
Relative And Absolute References
http://www.cpearson.com/excel/relative.htm

References to the Fill Handle and to the formulas used above were on my pages
Populating a Table based on number of rows and columns (#MultTab)
http://www.mvps.org/dmcritchie/excel/join.htm#MultTab

Fill-Handle, Replication and use of the Mouse
http://www.mvps.org/dmcritchie/excel/fillhand.htm

If you switch to the formula view you will see the formulas used in each cell
the shortcut to formula view is Ctrl+` (hold ctrl while accent grave)
Also available through Tools, Options, View (tab), [x] Formulas
Remove the checkmark to toggle back to the normal view.
--
 
Yes - that makes sense! Excel uses the $ sign to "fix"
references. So if you want to "fix" a cell reference you
can either enter it $A$1, or when entering the formula use
the F4 key to cycle through the flavours of
obsolute/relative options. This is very powerful since
you can fix either the row or column or both, and if your
talking ranges, you can fix the "start" but not the "end"
eg sum($a$1:b5) - in this case A1 will always be A1, but
B5 will be adjusted. ALso my preferred method for filling
a range is to use CTRL+D to copy down or CTRL+R to copy
right - just habit...
 
Back
Top