Changing Numerators but not Denominators
 

I have a worksheet in which cell C2 has number e.g. 253,754.

In other rows I'm placing different values in the C column e.g. C10=8,000;
C13=5,000; C16=12,500; C19=2,750; C22=11,000 etc.

I want the B10 cell to calculate the percentage C10 is to C2 so I input the
formula =C10/C2 into B10.

When I copy the formula in B10 (=C10/C2) to the rows that have other values
in the C column such as C13, C16, C19, C22 it changes the numerator and
denominator so what shows up in B13 is =C13/C5. I would like the formula to
change the numerator to the corresponding row but keep the denominator as C2.
I would like the formula to change to =C13/C2, =C16/C2, =C19/C2, =C22/C2,
etc. How can I copy the formula in B10 to other cells to accomplish this?

I'm using Office 2003.

Thanks for your help.

--
BTM

Answer: Changing Numerators but not Denominators
 

To accomplish this, you can use a mixed cell reference for the denominator ($C$2) in your formula. A mixed cell reference is a combination of an absolute cell reference (using the $ symbol) and a relative cell reference (without the $ symbol).

Here's how you can modify your formula in cell B10 to use a mixed cell reference:

1. Select cell B10.
2. Edit the formula in the formula bar to change it to =
   Code:

   ---

   C10/$C$2

   ---

   .
3. Press Enter to confirm the formula.

The $ symbol before the C and the 2 in $C$2 makes them absolute cell references, which means they won't change when you copy the formula to other cells.

1. Copy cell B10 by selecting it and pressing Ctrl+C.
2. Select the range of cells where you want to paste the formula (B13:B22 in this case).
3. Right-click on the selection and choose "Paste Special" from the context menu.
4. In the "Paste Special" dialog box, choose "Formulas" and click OK.

The formula in each cell will now reference the corresponding cell in column C and the absolute cell reference $C$2 for the denominator.

Alternatively, you can also use the "Fill Handle" to copy the formula down to other cells:

1. Select cell B10.
2. Hover your mouse over the bottom-right corner of the cell until it turns into a black cross.
3. Click and drag the cross down to the last cell where you want to copy the formula (B22 in this case).
4. Release the mouse button to copy the formula to the selected cells.

The formula in each cell will now reference the corresponding cell in column C and the absolute cell reference $C$2 for the denominator.

Changing Numerators but not Denominators
 

=C10/$C$2

The $ signs "fix" C2 so it won't change as you copy.

See help on relative and absolute references.


Gord Dibben MS Excel MVP

On Thu, 19 Mar 2009 16:56:09 -0700, BTM
wrote:

I have a worksheet in which cell C2 has number e.g. 253,754.

In other rows I'm placing different values in the C column e.g. C10=8,000;
C13=5,000; C16=12,500; C19=2,750; C22=11,000 etc.

I want the B10 cell to calculate the percentage C10 is to C2 so I input the
formula =C10/C2 into B10.

When I copy the formula in B10 (=C10/C2) to the rows that have other values
in the C column such as C13, C16, C19, C22 it changes the numerator and
denominator so what shows up in B13 is =C13/C5. I would like the formula to
change the numerator to the corresponding row but keep the denominator as C2.
I would like the formula to change to =C13/C2, =C16/C2, =C19/C2, =C22/C2,
etc. How can I copy the formula in B10 to other cells to accomplish this?

I'm using Office 2003.

Thanks for your help.

Changing Numerators but not Denominators
 

Thanks, I thought there would be an easy way to accomplish this. You've just
saved me a lot of time, I appreciate your quick response.
--
BTM


"Gord Dibben" wrote:

=C10/$C$2

The $ signs "fix" C2 so it won't change as you copy.

See help on relative and absolute references.


Gord Dibben MS Excel MVP

On Thu, 19 Mar 2009 16:56:09 -0700, BTM
wrote:

I have a worksheet in which cell C2 has number e.g. 253,754.

In other rows I'm placing different values in the C column e.g. C10=8,000;
C13=5,000; C16=12,500; C19=2,750; C22=11,000 etc.

I want the B10 cell to calculate the percentage C10 is to C2 so I input the
formula =C10/C2 into B10.

When I copy the formula in B10 (=C10/C2) to the rows that have other values
in the C column such as C13, C16, C19, C22 it changes the numerator and
denominator so what shows up in B13 is =C13/C5. I would like the formula to
change the numerator to the corresponding row but keep the denominator as C2.
I would like the formula to change to =C13/C2, =C16/C2, =C19/C2, =C22/C2,
etc. How can I copy the formula in B10 to other cells to accomplish this?

I'm using Office 2003.

Thanks for your help.