Percentage cut-n-paste problem

  • Thread starter Thread starter Mike Z. Helm
  • Start date Start date
M

Mike Z. Helm

I made a simple spreadsheet with a "number of shares" column and a
"share price" column.

I created another column to calculate "total value". I then total the
value of all shares in a cell below the "total value" for each stock.

I then enter a formula to calculate the percentage value of the first
stock.

So far, no problems.

I copy the cell with the percent formula and paste it to the remaining
cells in that column.

Problem: Excel increments not only the row for each individual stock,
it does so for the total cell.

So how do I do this without having to manually enter each percentage
cell.

Thanks in advance,
 
mike

If to arrive at the percentage you have say

=C1/C6 where C6 is the total cell, you need to make that cell absolute.

=C1/$C$6

When copied down to say D2 you will then have =C2/$C$6

Look up in help for relative and absolute references.

Tip: No need to copy and paste all these formulae. Just do the first one and
grab the fill handle (little square in bottom right corner of cell) and drag
down or, if the cells to the left are contiguous, double-click to avoid
dragging. It will fill for you.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
On Wed, 14 Jul 2004 07:25:20 +0100, "Nick Hodge"
mike

If to arrive at the percentage you have say

=C1/C6 where C6 is the total cell, you need to make that cell absolute.

=C1/$C$6

When copied down to say D2 you will then have =C2/$C$6

Look up in help for relative and absolute references.
Click to expand...

I will, but to clarify f I prefix either row or cell with a $ sign, it
will treat that as absolute instead of relative?

Cool - I knew there had to be a way of doing this easily.

Anticipatory question:

What if I move the Totals cell down a few rows? Can I easily adjust all
the totals? I guess it's easy enough to just do one cell and drag as
described below if I can't do something else.
Tip: No need to copy and paste all these formulae. Just do the first one and
grab the fill handle (little square in bottom right corner of cell) and drag
down or,
Click to expand...

That worked great.

if the cells to the left are contiguous, double-click to avoid
dragging. It will fill for you.
Click to expand...

I didn't quite get that part. My cells to the left are contiguous -
double click where?

Ah well, you gave me what I needed anyway. And as I anticipated, the
answer to that question would solve how it sorted. When I sorted by
value, my original column went fubar, but the one I just made works as
desired.

Thanks,
 
Mike

You could name the totals cell Total via Insert>Name>Define... (Or select
the cell and enter Total in the name box. (The box just above the column A
header that usually has the cell reference in it).

This way the formula would be

=C1/Total

This can be copied down in the same way and if you move it the range travels
with it

To copy by double clicking with contiguous cells, I meant double-click the
fill handle, (square at bottom right of selected cell).

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
D-click on the fill-handle. which is the square black lump at bottom right of
the cell.

When you hover over the cell, a black cross should appear at the bottom-right.

D-click on that.

Gord Dibben Excel MVP
 
On Wed, 14 Jul 2004 20:52:38 +0100, "Nick Hodge"
Mike

You could name the totals cell Total via Insert>Name>Define... (Or select
the cell and enter Total in the name box. (The box just above the column A
header that usually has the cell reference in it).

This way the formula would be

=C1/Total

This can be copied down in the same way and if you move it the range travels
with it

To copy by double clicking with contiguous cells, I meant double-click the
fill handle, (square at bottom right of selected cell).
Click to expand...

Got it - I presume by naming the field, if I move it later, I won't have
to mess with any other cells that reference it.
 
Back
Top