In need of some excel help.

If I have one column with 10,000 rows and each row in that same column
have a different value. How can I affect the entire column contain
those value by a percentage, whether it is decreasing or increasing.


You see, I have a list of prices in my excel spreadsheet that I need
to change constantly. However instead of sitting down and change the
price of each individual one, I want to change the entire thing by a
percentage. Whether it is a positive percentage or negative
percentage.

I know there is a code to do this, I have tries to look it up in the
excel help and the only thing they gave me for an answer was how to
effect the change in one cell not the entire cell. ( =F5*(1+5%) ) This
was the code that they gave me and it only work with one cell not the
entire column.

---

Request for Question Clarification by maniac-ga on 06 Oct 2005 17:05 PDT

Hello Cultural,

Hmm. Are you trying to manipulate the values in a set of cells by a
percentage (permanently changing their values) or trying to get the
result of that manipulation in separate cells (as suggested by
paul_and_emma)?

If you are doing the latter and am satisfied with the comment by
paul_and_emma, I suggest you close the question (so you are not
charged for an answer). If the former, please make a clarification so
the proper answer can be prepared.

  --Maniac

---

Clarification of Question by cultural-ga on 06 Oct 2005 19:09 PDT

Hello,

Thank you for those who responded to my question. However it really
doesn?t help me much when I have to change every single cell one by
one. What I wanted is to change the entire column of cell by a
percentage. What I mean is this.

Let say in column A row 1 to 10,000 each cell contain a different value.


example.     column 
               A  
row 1         $95
row 2         $68
row 3         $78
row 4         $63 
row 5         $56
ect..  


What I want is a formula where i can change or affect the entire
column A by 5 percents. Whether it is negative 5 percent or positive
five percent.

So the column should look like this at the end.


example.     column 
              A  
row 1       $90.25
row 2       $64.60
row 3       $74.10
row 4       $59.85 
row 5       $53.20
ect..  


I know how to adjust every single cell by a percentage one by one.
However I do not know how to adjust the entire cell in one shot, sort
of speak.

P.s. please be as detailed as possible...

---

Request for Question Clarification by maniac-ga on 07 Oct 2005 06:30 PDT

Hello Cultural,

Comments, like the one by Manuka are freely offered. See
  http://answers.google.com/answers/faq.html#aboutcomments
for more details. So if you are satisified, I suggest you close the
question. If not, explain what is lacking in a clarification so it can
be answered.

  --Maniac

---

Clarification of Question by cultural-ga on 10 Oct 2005 20:50 PDT

hello,

I am trying to figure out how to close this question that I posted
here on google answer. I feel that manuka-ga have given me the correct
and best answer for my question and would like to close this question
and pay him. If anyone knows how to close this question, please let me
know. It is amazingly difficult to close this google question page.

thank you
cultural-ga

Just paste your equation into every cell in the column. One way to do
this is to put the equation in one cell, copy it, click the column
heading, and then paste. If you want it in a range of cells, select
that range and then paste.

lets assume that your coulmn of figures is in column F rows 1 to 10,000.

find an empty cell in you speadsheet, say column B, row 1 (from know
on let's call this cell B1.) type into this cell your percentage
adjustment e.g. 5%
make sure that you use the % sign as 5% in a ratio is 0.05, and is not 5!


insert a new column for your pecentage adjusted figures, by clicking
anywhere in column G and selecting Insert / Column from the menu.
Everything shuffles to the right, leaving you room.

then type in to cell g1 the following:   =f1(1+$b$1)

NB if your percentage adjuster wasn't in cell B1 but in say Q34 then use $Q$34.

the $ signs just tell excel that the cell is 'Fixed' when we come to
copy the formula down your column.

So, now lets copy the formula down the couln! Do this by selecting the
cell (g1 in this case) and moving the cursor over the botom right hand
corner of the cell so that it changes to a cross symbol.  Click, Hold
and drag the box down your column, and when you release the mouse
button your coumn G will be filled with...

=f1(1+$b$1)
=f2(1+$b$1)
=f3(1+$b$1)
=f4(1+$b$1)

.....

=f10000(1+$b$1)

& when you change the value in cell B1 from 5% to say -10% all the
column changes magically!

good luck


Paul

PS if you hadn't used the $ tags infront of the B & the 1 in $b$1 then 

you would have got column G saying...

=f1(1+b1)
=f2(1+b2)
=f3(1+b3)
=f4(1+b4)

.....

=f10000(1+b10000)

There is a MUCH, MUCH easier way to do this. In particular, you DON'T
need an extra column for the new figuers.

Convert your percentage change into a ratio (divide by 100 and add 1).
5% more: 1.05
5% less: 0.95 

Enter this into any blank cell, and copy the cell.
Select the whole column whose values you want to change.
Go to the Edit menu and select Paste Special...
Click on "Values" and "Multiply" and then "OK".

Done!

manuka,

Outstanding! Thank you so much.

cultural

P.s. how do I close this question and pay manuka for his work?

Unfortunately (well, *I* think it's unfortunate!) you can't pay me
since I'm not an official Researcher - I can comment but not post
official Answers. So just close/expire the question and save yourself
a few dollars. 8-)