Percentage calculation from cell to row

[Jameslee77]

I want to be able to type a certain percent in cell I4 and have it
re-calculate what is already there in column F. So right now it say's 90%. In
the first item in column F, which is F10, the formula bar reads
=+SUM(E10*I4). So, if I type 95% and press enter it raises the dollar amount
to 95% of the retail price. Basically I need to type a percent and press
enter in a cell that calculates an entire column that's based another column
that is the retail price!

 

[AltaEgo]

If your formula in a cell is =Sum(E10*I4) then your spreadsheet will
automatically update any time you change either (or both) E10 or I4. If you
need all cells to update based on a single percentage, your formula in every
cell that depends on the percentage needs to reference the percentage cell.

OK, theory done, let's do something with it:

In the cell that now contains the formula =+SUM(E10*I4), replace it with .
=SUM(E10*$I$4).
{NB the '+' symbol in your formula before is unnecessary.}
You can now copy this straight in the cell below and it will change to
=SUM(E11*$I$4).

Copy the formula down as far as you need.

The other way would be to name the percentage cell

Click on your cell that contains the percentage.
Click on the name box - see diagram - and type a meaningful name (example
pctReduction)
http://spreadsheets.about.com/od/glossary/g/name_box_def.htm
change your formula to reference your named cell:

=SUM(E10*pctReduction).


Back to theory:

Some reading that may help you understand the difference between relative
and absolute references:

http://office.microsoft.com/en-us/excel/HP051983231033.aspx?pid=CH010036991033

see the reference "The difference between relative and absolute references"

 

[Jameslee77]

Thanks, that was very helpful!
Here's my next question.
Rather than copy and paste into every cell descending.
Is there a way I can assign this function to an array of cells in the column?
If so, does it involve the VBA editor like an "if" statement or something?

 

[AltaEgo]

There are shortcuts to copy down to every cell adjacent filled cells.

Click on the cell containing the formula.
In the lower right-hand corner you will see a small square - the fill
handle.

You can grab the fill handle with your mouse and drag it down to copy
formula
OR
(My favourite shortcut) you can double-click the fill handle and Excel will
do the work for you.

Fill handle basics:

http://spreadsheets.about.com/od/f/g/fill_handle_def.htm

Flash Video
http://communityclips.officelabs.com/Video.aspx?videoId=ea0d6d9a-ae96-47f0-afe9-fc9bb2b57366