PDA

View Full Version : Excel Problem



heaton
18-09-2004, 12:36 PM
If I have a column of money amounts in say a depreciation schedule and I want to reduce them all by say 2.5% printing out the answers in the next column and the reduced total in the third column, how do I do this for the whole column automatically ?

wotz
18-09-2004, 12:48 PM
Assuming original amounts are in column A, formula for cell B1 is =A1*.025
formula for C1 is =A1-B1 copy cells B1 & C1 down the rest of the column

heaton
18-09-2004, 02:12 PM
copy cells B1 & C1 down
> the rest of the column

Ok Thanks, but when I get to this part of the process all I get is the answer to =a1*.025 copied all down. Could you give me an exact step by step or mouse click by mouse click detail of the whole procedure please. Thanks again.

wintertide
18-09-2004, 02:17 PM
Try typing the formula in two cells, just changing the cell reference, then select the cells and drag down - it should work.

Hope this helps :)

heaton
18-09-2004, 04:50 PM
, just changing
> the cell reference, then select the cells and drag
> down - it should work.
Sorry but how do I change the cell reference without having to go down and change the cell reference for each line. I know there is a way to just somehow make it work automatically all the way down but I need someone to give me a mouse click by mouse click description of how it is done.

wintertide
18-09-2004, 05:02 PM
Instead of typing the formula in the first cell of each column, type it in the first cell then again in the cell directly beneath but changing it (eg. if it's A1+B1, change it to A2+B2) then select those two cells and hover the mouse cursor over the bottom right corner of the selected cells. It'll change to a little black cross. Click and drag down the column. It should change the numbers for you automatically.

Hope this helps.

godfather
18-09-2004, 05:15 PM
When any formula is copied down or across using the drag method above as detailed by wintertide, the formulas are automatically adjusted without having to type again in the second cell.

Also the formulas are automatically adjusted by a straight copy and paste.

Try it and see.

heaton
18-09-2004, 08:19 PM
By George I've got it, I've got it. Ever so thankful and my club depreciation schedule will be piece of cake. Thanks chaps, jolly good show what !!!

parry
18-09-2004, 10:31 PM
Hi Heath, another tip. If you have a column to the left of your formula, you can double-click the Fill Handle (the bottom right corner that has the +) and the formula will be copied down to the last row (or until the first blank) of the column to the left.

eg A1:A10 are filled in with data. If your formula is in B1 and you double-click the fill handle, the formula will be copied down to B10. This is a bit quicker than dragging the formula down if you have a large # rows.

hth

parry
18-09-2004, 10:32 PM
err Heaton, not Heath. Sorry.

leshibbard
19-09-2004, 04:32 PM
Hi Heaton, another way is to drop your figures down a row and have your depn. in B1, and make it perm. if rates change you only have to change cell B1.

Copy B2 down.

2.50%
=+A2*B$1
100 2.50 97.50
110 2.75 107.25
120 3.00 117.00
130 3.25 126.75
140 3.50 136.50
150 3.75 146.25
160 4.00 156.00
170 4.25 165.75
180 4.50 175.50
190 4.75 185.25
200 5.00 195.00
210 5.25 204.75
220 5.50 214.50
230 5.75 224.25
240 6.00 234.00
250 6.25 243.75
260 6.50 253.50
270 6.75 263.25
280 7.00 273.00

Les 3.

zqwerty
19-09-2004, 05:15 PM
Heaton I don't know if you are aware of this page, I can recommend the XLFDIC01 file. Download as a zip and don't worry about the macro warnings when you run it in Excel.

http://homepage.ntlworld.com/noneley/

heaton
20-09-2004, 12:45 PM
Thanks again chaps, I downloaded that file querty and it appears to be a help file. One of the problems with help files is knowing what to look for. I tried all sorts of terminology but could not find how to do this simple thing. Why don't they have a help item saying something like " how to enter formulae in selected cells." Guess I am asking too much. Anyway I know how to do it now thanks to Press F1 which is a really
helpful website. Cheers.