Formula to add two cells

[Pnutncrew]

Trying to add two cells together, but I need to copy it down the spreadsheet. How do I keep the one cell constant, but let the other change as I copy it down the spreadsheet? I think there is a character or something that needs to be in the formula...

[vlady]

the Character is Dollar sign

When you put a dollar sign.
For the Column the column doesn't change $A1 but the row will change 1,2,3,4......
For the Row A$1 the column changes A,B,C,D,.... but the row doesn't
Row and column don't change $A$1

[Pnutncrew]

I knew it was something simple...could not remember! Thanks for the help! Now I'll be going to bed on time, not after midnight!!!

[Pnutncrew]

I have not written formula's in a while more extreme than a1+b1. Having said that, what is wrong with my formula?

=$b6*g3

the $b6 is changing to the next cell address as I copy it down the spreadsheet.

[quekbc]

As you are copying it down, you want to lock down the 6 of B6. So, rather than $B6, you use B$6

Please Login or Register  to view this content.

[:) Sixthsense :)]

Relative And Absolute References In Formulas

Excel accepts cell references in what are called absolute and relative ranges. Absolute ranges have a $ character before the column portion of the reference and/or the row portion of the reference. Relative ranges do not use the $ character. The $ character indicates to Excel that it should not increment the column and/or row reference as you fill a range with a formula or as you copy a range. For example A1 is a relative range, while $A$1 is an absolute range. If you enter =A1 in a cell and then fill that cell down a column, the '1' in the reference will increment in each row. Thus, the formula in row 50 would be =A50. However, if you enter =$A$1 in a cell and fill down, the range reference will remain $A$1 -- it will not increment as you fill or copy down a column.

There are three absolute styles:

Reference Style Meaning
$A$1 Both the column and row reference are fixed. Neither will be incremented or changed during a copy or fill operation.
$A1 Only the column reference is fixed. It will not change during a fill or copy, but the row will change.
A$1 Only the row reference is fixed. It will not change during a fill or copy, but the column will change.

If you select all or part of a formula in the formula, you can press F4 to cycle range reference between the 4 styles (1 relative and 3 absolute).

Even with an absolute referencing style, Excel will still change row and column references when you insert a row or column. To have a truly absolute cell reference that will not change under any circumstances, use the INDIRECT function. For example =INDIRECT("A1") will always refer to cell A1, regardless of any changes made to the worksheet. This works because Excel does not interpret the string "A1" as an address. Instead, it treats it as plain text and therefore does not change it.

A common use of mixing absolute and relative range specifications is to create a running total of a column of number. For example, if you have data in cells A1:A10, the formula =SUM(A$1:A1) in cell B1 and filled down to cell B10 will return the running total for the numbers in column A.

Source:-

http://www.cpearson.com/excel/relative.aspx