I have a spreadsheet where B9 is Funds Appropriated, C9 is Funds Used, D9 is
Balance, and E9 is % Remaining. The formula I have used in E9 is =SUM(D9/B9).
When I copy that formula to the next cell down, E10, I get =SUM(D10/B10) .
What I want is to have the B9 stay constant, =SUM (D10/B9). How can I do
this? I have been manually changing the divisor to B9 all the way down, but I
know there has to be an easier way. Is there a better formula that will do
this?

Thanks from a non-technical user.

The Sum function is superfluous.

=D9/B$9

Look up about cell and range references in the Help files.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"clpayne" wrote in message
...
I have a spreadsheet where B9 is Funds Appropriated, C9 is Funds Used, D9
is
Balance, and E9 is % Remaining. The formula I have used in E9 is
=SUM(D9/B9).
When I copy that formula to the next cell down, E10, I get =SUM(D10/B10) .
What I want is to have the B9 stay constant, =SUM (D10/B9). How can I do
this? I have been manually changing the divisor to B9 all the way down,
but I
know there has to be an easier way. Is there a better formula that will do
this?

Thanks from a non-technical user.

Change B9 to B$9. B9 is a relative reference which will automatically
increment or decrement as you copy the formula containing it to other rows.
The dollar sign makes the row reference absolute, so that when you copy the
formula to other rows it will still refer to the same row. To make the column
reference absolute, put a $ in front of the column letter ($B9). To make the
row and the column references absolute, put a $ in front of each ($B$9).

Hope this helps,

Hutch

"clpayne" wrote:

I have a spreadsheet where B9 is Funds Appropriated, C9 is Funds Used, D9 is
Balance, and E9 is % Remaining. The formula I have used in E9 is =SUM(D9/B9).
When I copy that formula to the next cell down, E10, I get =SUM(D10/B10) .
What I want is to have the B9 stay constant, =SUM (D10/B9). How can I do
this? I have been manually changing the divisor to B9 all the way down, but I
know there has to be an easier way. Is there a better formula that will do
this?

Thanks from a non-technical user.

You don't need the =sum() function.

Try:

=d9/b$9
or
=d9/$b$9

The $ sign tells excel not to adjust that part of the address when the formula
is copied.



clpayne wrote:

I have a spreadsheet where B9 is Funds Appropriated, C9 is Funds Used, D9 is
Balance, and E9 is % Remaining. The formula I have used in E9 is =SUM(D9/B9).
When I copy that formula to the next cell down, E10, I get =SUM(D10/B10) .
What I want is to have the B9 stay constant, =SUM (D10/B9). How can I do
this? I have been manually changing the divisor to B9 all the way down, but I
know there has to be an easier way. Is there a better formula that will do
this?

Thanks from a non-technical user.

--

Dave Peterson